how to execute a long (11000 characters) dynamic query using sp_executesql

Question

Hello,
I'm  trying to execute a dynamic SQL query(=11000 characters) using sp_executesql but it fails with message "String or binary data would be truncated."

I'm executing the following query

DECLARE @qry nvarchar(max)
SET @qry =N'-----query of 10000 characters------
exec sq_executesql @qry

Since I'm using nvarchar(max) I was under the impression that it will hold query that long. I know that  exe() can be used but why is that I'm unable to execute the query using sp_executesql despite using nvarchar(max)? Can anybody help me understand?

Thanks in advance.

 

Answer 1

It should be some error within your qeury, such as update a column of varchar(10) to a string with a length greater than 10.
If I was wrong, please correct it.

Answer 2

AFAIK the max number was only 4000 long.

from msdn library http://msdn.microsoft.com/en-us/library/ms186939.aspx

nvarchar [ ( n | max ) ]

Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for nvarchar are national char varying and national character varying .

---

Best Regards,
Melissa Suciadi

If you have found this post helpful, please click the 'Vote as Helpful ' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered ' link below. It helps others who experience the same issue in future to find the solution.

Answer 3

Hello,
I'm  trying to execute a dynamic SQL query(=11000 characters) using sp_executesql but it fails with message "String or binary data would be truncated."

I'm executing the following query

DECLARE @qry nvarchar(max)
SET @qry =N'-----query of 10000 characters------
exec sq_executesql @qry

Since I'm using nvarchar(max) I was under the impression that it will hold query that long. I know that  exe() can be used but why is that I'm unable to execute the query using sp_executesql despite using nvarchar(max)? Can anybody help me understand?

Thanks in advance.

 

your variable can contain upto 2GB data but in one go you can not assign more than 8K bytes to the variable you have to split your query

check this

declare @sql nvarchar(max)


--set @sql = N'select'+ REPLICATE(' ', 4000)+' ''ok'''
--exec sp_executesql @sql -- This will not work

set @sql = N'select'+ REPLICATE(' ', 2000)
set @sql = @sql + REPLICATE(' ', 2000)+' ''ok'''

exec sp_executesql @sql

select LEN (@sql)

Answer 4

Hi,

It shouldn't be the case. You should be able to pass strings that can be accommodated in NVARCHAR(MAX).

MSDN - "The size of the string is limited only by available database server memory. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max)."

If you are appending any string variables (say NVARCHAR(xx)) you might see that the string is terminated to 4000 characters. Just check the size of the string before execution (PRINT LEN(@strVariable)). Let us know if you still have any issues.

Arif,

Just looked at your post, it's not possible to set more than 8K of data because of the expression that you have. Introduce an explicit cast and there you go.

The expression below would work always

declare @sql nvarchar(max)

set @sql = N'select'+ CONVERT(NVARCHAR(MAX),REPLICATE(' ', 4000)) +' ''8K+ Concatenation'''
print @sql
exec sp_executesql @sql

Shoot if any questions!!

Regards

Padmanabhan

Answer 5

Hi,

It shouldn't be the case. You should be able to pass strings that can be accommodated in NVARCHAR(MAX).

MSDN - "The size of the string is limited only by available database server memory. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max)."

If you are appending any string variables (say NVARCHAR(xx)) you might see that the string is terminated to 4000 characters. Just check the size of the string before execution (PRINT LEN(@strVariable)). Let us know if you still have any issues.

Arif,

Just looked at your post, it's not possible to set more than 8K of data because of the expression that you have. Introduce an explicit cast and there you go.

The expression below would work always

declare @sql nvarchar(max)



set @sql = N'select'+ CONVERT(NVARCHAR(MAX),REPLICATE(' ', 4000)) +' ''8K+ Concatenation'''

print @sql

exec sp_executesql @sql

Shoot if any questions!!

Regards

Padmanabhan

yes that is what i was saying 8KB restriction and my solution is to "split the query" which works that is why i think should be an answwer

and your solution is to convert before assigning to the variable which is a another solution and that is why I have proposed your post as answer