question

beach1447 avatar image
beach1447 asked

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.

queryselect
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Don't put it in the comments. Edit your question and include the query and table def. there.
1 Like 1 ·
Export into where?
0 Likes 0 ·
Can you post your query?
0 Likes 0 ·
And the table definition
0 Likes 0 ·
query is too large to add to comments.
0 Likes 0 ·
Show more comments
Kev Riley avatar image
Kev Riley answered

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

9 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
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.
0 Likes 0 ·
see my edit above.....
0 Likes 0 ·
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.
0 Likes 0 ·
`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?
0 Likes 0 ·
Show more comments
bonskijr avatar image
bonskijr answered

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

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.