Problem concatenating varchars over 4000 characters

[shmiller]

I am using several varchar variables in a proc to dynamically build a long string then execute the string with exec.
EXEC (@string1 + @string2 + @string3)
When I limit the size of each string to 4000 it works fine. If I let it go to 8000 it fails. The errors indicate the strings are being truncated.
Does anyone know why?

Thanks

 

[MAK9974]

All empty spaces filled up.
use this

set @query1=ltrim(rtrim(@query1))
set @query2=ltrim(rtrim(@query2))
set @query3=ltrim(rtrim(@query3))

execute (@query1+@query2+@query3)

 

[shmiller]

I found the problem. In the strings that I was building, even though the string variable was a varchar, I was using nvarchar variables to build the string and I think this was taking up extra space and getting truncated. I changed everything to varchar and it works now.

 

[bperry]

Glad to hear you have it fixed.

I think the problem may have been that the nvarchar datatype only holds up to 4000 characters (not 8000 like varchar).

The reason for this is that nvarchar supports international characters, but to do this it takes two storage bytes for each character instead of just one. Hence, 8000/2 = 4000.

Anyway, just background info for the future.

 

[tlbroadbent]

shmiller,

Thanks for sharing the solution. It is good to note that SQL will return an nchar or nvarchar string if one of the strings being concatenated is nchar or nvarchar. This is true even if all the other strings are non-Unicode. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[shmiller]

Tlbroadbent, that's exactly what the problem was. My 8000 character strings were correctly defined as varchar not nvarchar. But I was using an nvarchar as one of the variables that went into building the 8000 character string. Declaring everything as varchar solved the problem.
It seems to work well, but out of curiosity, do you have a better idea for building a dynamic query that is 25000 characters long?

Thanks,
Shanti

 

[tlbroadbent]

There is no other option as far as I know. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.