Select Query issue

Question

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.

Answer 1

0

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

answered Nov 23 '09 at 02:02 PM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

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 '09 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 '09 at 02:25 PM beach1447

see my edit above.....

Nov 23 '09 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 '09 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 '09 at 02:46 PM Kev Riley ♦♦

(comments are locked)

Answer 2

0

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

answered Nov 23 '09 at 01:46 PM

bonskijr
174 ● 4 ● 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 '09 at 01:51 PM beach1447

(comments are locked)

Select Query issue

Follow this question

Related Questions