x

Select Query issue

Good Morning...I realize this should be a simple correction but I am at home sick today on all kinds of meds...work called and wants me to export 60K+ rows of data.

One field in the table is 'text' datatype and everytime I execute the query I get

Msg 8152, Level 16, State 4, Line 4 String or binary data would be truncated.

I attempted to do a select into and it failed as well...can anyone help with this quickly.

Thanks.

more ▼

asked Nov 23, 2009 at 01:06 PM in Default

beach1447 gravatar image

beach1447
1 1 1 1

Export into where?
Nov 23, 2009 at 01:09 PM Raj More
Can you post your query?
Nov 23, 2009 at 01:32 PM Kev Riley ♦♦
And the table definition
Nov 23, 2009 at 02:01 PM Kev Riley ♦♦
query is too large to add to comments.
Nov 23, 2009 at 02:09 PM beach1447
Don't put it in the comments. Edit your question and include the query and table def. there.
Nov 23, 2009 at 02:24 PM NYSystemsAnalyst
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Does the query fail when you exclude the 'text' field?


Edit following on from comment:

REPLACE works with varchar and nvarchar, so yes attempting to convert your long text field to this could result in that error.

What version of SQL are you using?

can you convert the field to varchar(max) first, e.g.

REPLACE(QUOTENAME(cast(txtErrorDescription as varchar(max)), '"'), 
        CHAR(13) + CHAR(10),'') AS [Work Log]


Edit: Replacing QUOTENAME

'"' + REPLACE(txtErrorDescription, CHAR(13) + CHAR(10),'') +'"' AS [Work Log]

or if you still have issues with REPLACE

'"' + REPLACE(cast(txtErrorDescription as varchar(max)), 
              CHAR(13) + CHAR(10),'') +'"' AS [Work Log]


Just to add to this, if you were using QUOTENAME to handle quotes in the string, then you have a couple of options.

select quotename('this string has "internal" quotes','"')

"this string has ""internal"" quotes"

replace the double quotes with nothing

select '"' + replace('this string has "internal" quotes','"','') + '"'

"this string has internal quotes"

or replace them with double quotes

select '"' + replace('this string has "internal" quotes','"','""') + '"'

"this string has ""internal"" quotes"

it depends on how you need the output

more ▼

answered Nov 23, 2009 at 02:02 PM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

No, that is field in question for sure. This field contains long text of error descriptions and I am removing cr lf from the data with the replace statement and I am also enclosing the string in double quotes.

REPLACE(QUOTENAME(txtErrorDescription, '"'), CHAR(13) + CHAR(10),'') AS [Work Log]

I think the issue is that on some records that inserted max text in the field and when I attempt to add double quotes...i get the error.
Nov 23, 2009 at 02:14 PM beach1447
anyone have any idea on a way around this? The txtErrorDescription field is at MAXLENGTH so adding double quotes would cause this error...any advice would be greatly appreciated.
Nov 23, 2009 at 02:25 PM beach1447
see my edit above.....
Nov 23, 2009 at 02:31 PM Kev Riley ♦♦
SQL 2005. I executed the query over and over with sample data - 100+ records for testing...I guess never hit a record with the field fully populated. Now they want it and I am getting this error. I also got the error using you cast statement above.
Nov 23, 2009 at 02:34 PM beach1447
QUOTENAME returns a datatype of nvarchar(258) - do you need to use this or can you just add double quotes to the beginning and end?
Nov 23, 2009 at 02:46 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

That usually happens if the destination table's column is too short for the source data column (eg destcolumn char(10) <--- sourceColumn char(20)). Try to make sure source is wider or the same data length.

hth

more ▼

answered Nov 23, 2009 at 01:46 PM

bonskijr gravatar image

bonskijr
204 5 5 7

This is my issue...it's not going to a table...I am just trying to run a select query in Management Studio. I just did the select into to try to determine what the issue is...why is it doing it in management studio on a select from query.
Nov 23, 2009 at 01:51 PM beach1447
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x373
x109

asked: Nov 23, 2009 at 01:06 PM

Seen: 2799 times

Last Updated: Nov 23, 2009 at 01:06 PM