dynamic sql - string being truncated issue

Question

dynamic sql - string being truncated issue

TT-131210

SSCommitted

Points: 1603
More actions
June 30, 2009 at 9:54 am

#155891

I have a string that is declared nvarchar(max). Once the string has been built the length is 4178.
DECLARE @MyString nvarchar(max)
SET @MyString = N'Insert Into ...'
EXECUTE sp_executesql @MyString
Error Message: String or binary data would be truncated.
I am stumped as to why the above would give me this error.
Thanks Tim

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 1

The issue is likely with your insert statement. A piece of data in the statement exceeds the column size. So you might be trying to put 40 characters of data in a 30 character column. The issue does not appear to be with the dynamic sql, but what is actually being executed. Look at your data and column sizes for the issue.

Lowell SSC Guru Points: 323518 More actions · Answer 2

you error message is not related to your (max) string, it's related to a specific varchar/char column in the sql statement you built...

if a column has a definition of a varchar(8), for example, and your INSERT statemnt tries to stick 12 characters in it, you will get that error: String or binary data would be truncated.

take a look at the specifics for you statement you built, and see if you can find the data that is larger than one of the columns.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TT-131210 SSCommitted Points: 1603 More actions · Answer 3

That was the probelm. I did not even think of the query I was trying to execute was the problem. The piece of code has always worked. I had some tunnel vision going on.

Thanks Tim