sp_executesql with large query Str
-
We have a query string that is too large for sp_executesql
We are using nvarchar(4000), but the actual string is about 5000-6000, has anyone ever found a way to get around this limitation with sp_executesql?
Seems like sp_executesql can only use nvarchar, nvarchar maxes @ 4000 chars.
Many Thanks in advance
-
You could use 'EXECUTE', which doesn't have to be nvarchar.
Steve
-
DECLARE @sqlprt1 VARCHAR(8000),@sqlprt2 VARCHAR(8000)
SET @sqlprt1 = 'SELECT * FROM /*....and long and long...*/'
SET @sqlprt2 = ' INFORMATION_SCHEMA.COLUMNS /*...and long and long...*/'
EXECUTE(@sqlprt1+@sqlprt2)
/rockmoose
You must unlearn what You have learnt -
Ohh, I hate when its that easy!!
Thanks, tried it and works great.
Is there any drawback to using straight execute() to sp_executesql?
My Thanks
-
Hi,
When using sp_executesql it is more likely that SQL Server will be able to reuse the execution plan of the sql batch (BOL). This I think is only true when there are parameters involved when executing sp_executesql. If there are no parameters involved I don't think there is a difference between using sp_executesql and EXECUTE().
Also I think that EXECUTE converts the sql batch into Unicode characters under the hood. So probably it is best to pass NCHAR or NVARCHAR to EXECUTE().
Someone out there might be able to give a more precise answer ?!
/rockmoose
You must unlearn what You have learnt -
If EXECUTE converts to unicode under the hood, it apparently doesn't have the maximum characters restriction that sp_executesql does. If it did, sqlSushi's query wouldn't have worked!
Steve
-
Actually the 8000 bytes length restiction on character datatypes is a restriction on the datatype and not on the representation of the data itself, ( 1 byte per character for nonunicode data and 2 bytes per character for unicode data ).
To me it makes perfectly good sense that SQL Server can allocate arbitrary large sizes of nonunicode and unicode character arrays, and is not restricted by the 8000 byte limit that restricts the datatypes.
So under the hood SQL Server could convert char representation to nchar representation when using EXECUTE().
BOL on EXECUTE(): "For optimum performance, do not use more than 4,000 characters".
This makes me think that passing a 4000 long char is less trouble because it can be converted to a nchar with not so much trouble, passing a >4000 char will result in SQL Server having to manipulate the string further.
Any enlightments welcome
,/rockmoose
You must unlearn what You have learnt -
instread of Using Nvarchar U can Use Varchar itself this can hold any query string Upto 8000 characters
if u thing sp_executesql can use only nvarchar then instread of sp_executesql
u can directly use Exec <sp_Name> using above advice this will solve your problems
if succeeds pls mail to me at
regards
vishwanath
-
Thank you for the insight.
Yes we did get it working again by creating a dynamic query string and capturing in a local var @t.
Firing the script with execute(@t) was just as easy as using sp_executesql @t, and we wre able to go over the 4000 char limit of sp_executesql (must use nvarchar, max size of nvarchar is 4000).
@t is a varchar(8000) but I believe you can have execute fire even larger dynamic statements by concating execute(@t+@s+@x)
Again thanks!
-
*********
DECLARE @sqlprt1 VARCHAR(8000),@sqlprt2 VARCHAR(8000)
SET @sqlprt1 = 'SELECT * FROM /*....and long and long...*/'
SET @sqlprt2 = ' INFORMATION_SCHEMA.COLUMNS /*...and long and long...*/'
EXECUTE(@sqlprt1+@sqlprt2)
*********
Hi we are trying something almost exact to this suggestion above, but are having an issue: When we EXECUTE(@sqlprt1+@sqlprt2), the string query is stopping @ 8000 characters.
It seems that string varchar(8000) + string varchar(8000) is still equal to 8000.
I am not sure if this is an issue with EXECUTE only handling string of 8000 or if this is an issue with strings having a ceiling of 8000 chars.
Long an hort of it is we need to EXECUTE() strings larger than 8000 chars
Thanks
-
Hi,
It seems that string varchar(8000) + string varchar(8000) is still equal to 8000.
Yes, that is true, adding two varchar(8000) will result in varchar(8000).
This shouldn't affect the beavior of execute( @str1 + @str2 ) though, just look in BOL.
Hi we are trying something almost exact to this suggestion above, but are having an issue: When we EXECUTE(@sqlprt1+@sqlprt2), the string query is stopping @ 8000 characters.
Ok, check this out:
here is an example of what you are trying to do
declare @a varchar(8000),@b varchar(8000)
-- make 8000 long strings for testing...
select @a = '/' + replicate('*',8000-16) + '/' + 'SELECT * FROM '
select @b-2 = '/' + replicate('*',8000-27) + '/' + 'INFORMATION_SCHEMA.TABLES'
-- execute the strings
--select len(@a), len(@b), len(@a+@b), len(@b+@a)
However if you are trying some dynamic stuff like:
exec( 'execute(' + @a + @b-2 + ')' )
then it will not work due to the fact that @a + @b-2 will return a varchar(8000).
In fact the whole dynamic expression 'execute(' + @a + @b-2 + ')' will be varchar(8000).
This means - strangely enough - that execute(@a + @b-2) does not evaluate the expression (@a + @b-2)
prior to execution!
/rockmoose
You must unlearn what You have learnt -
print len(@a) {=7999}
print len(@b) {=8000}
print len(@a+@b) {=7999}
execute( @a + @b-2 ) {fires just fine!!}
Rockmoose, so I think I am starting to understand.
Though len(@a+@b) {=7999},which has hit the varchar(8000) limit, the actual execution of execute(@a+@b) does not evaluate the length, just fires the script.
So, I should be able to execute strings larger than 8000 (@a+@b).
Therefore it must be an issue with my string or query.
Thank you.
-
Yup,
Good luck with your query string..
A common error (for me anyway) with long generated sql strings/scripts is to forget a space or to have a comma too much/little somewhere.
You are welcome.
You must unlearn what You have learnt -
Being able to substitute parameters in sp_executesql offers these advantages to using the EXECUTE statement to execute a string:
- Because the actual text of the Transact-SQL statement in the sp_executesql string does not change between executions, the query optimizer will probably match the Transact-SQL statement in the second execution with the execution plan generated for the first execution. Therefore, SQL Server does not have to compile the second statement.
- The Transact-SQL string is built only once.
- The integer parameter is specified in its native format. Casting to Unicode is not required.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply