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.


more ▼

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

avatar image

1 1 1 2

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
show all comments (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

avatar image

Kev Riley ♦♦
66.8k 48 65 81

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.


more ▼

answered Nov 23, 2009 at 01:46 PM

avatar image

204 5 5 10

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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Nov 23, 2009 at 01:06 PM

Seen: 3498 times

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

Copyright 2018 Redgate Software. Privacy Policy