dynamic sql in 2005

Question

dynamic sql in 2005

Ghanta

Hall of Fame

Points: 3999
More actions
July 5, 2009 at 4:37 pm

#93769

Guys I made the variable @nsql nvarchar(max) to be used for dynamic sql. When I execute using sp_executesql, it throws me error and seems like the sql that it was trying to run was incomplete (4000 chars only and it is where it throws error).... I was able to use workaround by using two variables and using
Exec(@var1 + @var2) ... But not sure why would nvarchar(max) not work?
Anyone going through this issue? I am using sql server 2005 enterprise edition... thanks!

Viewing 15 posts - 1 through 14 (of 14 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

sp_executesql, even though its an "internal" stored procedure, is still a proc, and it has a parameter that is an nvarchar(4000);

EXEC is a command, and has no parameters.. just tries to execute whatever you pass it. So when you pass it a (max) or bunch of (max)'s concatened, it works fine, where with sp_executesql, it gets truncated.

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!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Lowell (7/5/2009)
...
EXEC is a command, and has no parameters.. just tries to execute whatever you pass it. So when you pass it a (max), it gets truncated.

??? I'm sure this must be some kind of typo, Lowell, but just to be clear, EXEC(NVARCHAR(max)) will *not* truncate the string, it will execute the whole thing.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Ghanta (7/5/2009)
Guys I made the variable @nsql nvarchar(max) to be used for dynamic sql. When I execute using sp_executesql, it throws me error and seems like the sql that it was trying to run was incomplete (4000 chars only and it is where it throws error).... I was able to use workaround by using two variables and using
Exec(@var1 + @var2) ... But not sure why would nvarchar(max) not work?
Anyone going through this issue? I am using sql server 2005 enterprise edition... thanks!

Can you post the procedure? Normally this works, so there must be some additional factor.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Lowell SSC Guru Points: 323504 More actions · Answer 4

definitely fatfingered...i meant to say EXEC will use all you pass it, whether concatenated (max) variables or whatever, where sp_executesql is the one that will truncate at 4000; thanks, and i fixed my post...i know better, just a disconnect in the chair-to-keyboard-interface.

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!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Thing is, I'm not sure that its true about sp_ExecuteSql either. Here's a script that demonstrates both successfully executing a string WAY over 4000 characters:

Declare @sql as nvarchar(MAX)

set @sql = ''

select @sql = @sql + N'

IF '''+name+N'''=''not in there'' PRINT ''Found It!'';'

from master.sys.system_columns

Select @sql = @sql + N'

IF ''not in there''=''not in there'' PRINT ''Found It on the last line!'';'

Print len(@sql)

EXEC (@sql)

EXEC sp_ExecuteSql @sql

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

WayneS SSC Guru Points: 95409 More actions · Answer 6

Ghanta (7/5/2009)
Guys I made the variable @nsql nvarchar(max) to be used for dynamic sql. When I execute using sp_executesql, it throws me error and seems like the sql that it was trying to run was incomplete (4000 chars only and it is where it throws error).... I was able to use workaround by using two variables and using
Exec(@var1 + @var2) ... But not sure why would nvarchar(max) not work?
Anyone going through this issue? I am using sql server 2005 enterprise edition... thanks!

Is it possible that it's running through some old sql 2000 code first that hasn't been upgraded to use nvarchar(max)? Sql 2000 has a varchar(8000)/nvarchar(4000) limit, and this would truncate your string if it was passed to it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

WayneS (7/5/2009)
Ghanta (7/5/2009)
Guys I made the variable @nsql nvarchar(max) to be used for dynamic sql. When I execute using sp_executesql, it throws me error and seems like the sql that it was trying to run was incomplete (4000 chars only and it is where it throws error).... I was able to use workaround by using two variables and using
Exec(@var1 + @var2) ... But not sure why would nvarchar(max) not work?
Anyone going through this issue? I am using sql server 2005 enterprise edition... thanks!
Is it possible that it's running through some old sql 2000 code first that hasn't been upgraded to use nvarchar(max)? Sql 2000 has a varchar(8000)/nvarchar(4000) limit, and this would truncate your string if it was passed to it.

There are a couple of flukey ways that an NVarchar(MAX) can get cut back to 4000 characters in the middle of a procedure, but I cannot remember exactly what they are. I'm pretty sure that I can figure it out if see the code though.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Of course I'm going on Vacation in the morning... So, you want to look for things like adding string literals onto the front of your NVarchar(MAX):

SELECT @BigStr = 'Some stuff ...' + @BigStr

There's some scenario like this where the intermediate string gets cut down to 4000 characters, but I cannot remember the exact conditions.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

chinthakagunathilaka Newbie Points: 4 More actions · Answer 9

chinthakagunathilaka

Newbie

Points: 4

July 6, 2009 at 8:27 am

#1020419

.

pepepaco SSC Enthusiast Points: 103 More actions · Answer 10

what does those SQL variables content?

there are sometimes that you can split the ddl into multiple operations, maybe Adding columns one by one to a table, or converting long queries in Views, in not sure how to make EXEC to run bigger queries but there should be another way to "compress" the query.

this is just in case you want to use only one variable instead of @var1+ @var2

regards

Ghanta Hall of Fame Points: 3999 More actions · Answer 11

Guys thanks for all the info... definitely helped me to understand few things...

My issue was solved by updating the compatibility level I think..

exec sp_dbcmptlevel 'dbname', 90

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 12

That would certainly do it. Just watch for problems on that if the old database was 80 compatible...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 13

hi Rbarry

please see the below loink's problem

http://www.sqlservercentral.com/Forums/FindPost761384.aspx

thanks

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 14

This behavior really threw me out in cold for a while! After going through this thread I broke the dynamic sql in multi-part and it worked fine. Yes, my dynamic SQL was 4194 characters but the SQL will truncate it to 3986 characters (very near to 4k). And I was using EXEC to execute the SQL. Even a "print" command would not emit the entire SQL (the BOL says it can take upto 8000 chars).

Upon digging deeper into my dynamic SQL I found that one of my variables concatenated in the dynamic SQL was of type NVARCHAR. Changing it to VARCHAR made my dynamic SQL work fine.

The only thing that still bugs me is why does it work when I break it multi-part but not when its a single string!:ermm: