dynamic sql more then nvarchar(max) more then the max length

Question

dynamic sql more then nvarchar(max) more then the max length

edl0022

SSC Veteran

Points: 261
More actions
July 5, 2009 at 10:15 pm

#384916

I have a stored procedure with a variable with type nvarchar(max) to store the sql for executei(exec sp_executesql).... but i found my sql statement is too long(over the size of nvarchar(max)) . is there anyway i can solve this problem.
is anyone can give me a hand?

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

edl0022 (7/5/2009)
I have a stored procedure with a variable with type nvarchar(max) to store the sql for executei(exec sp_executesql).... but i found my sql statement is too long(over the size of nvarchar(max)) . is there anyway i can solve this problem.
is anyone can give me a hand?

Are you sure it's the size of the string that's an issue? This has a capacity of 2GB... if your string is bigger than this, you need to look seriously at trimming this down.

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

t.hitendra SSCommitted Points: 1620 More actions · Answer 2

Hi there,

We'd have to take a look at your SP. In case you're not passing more than 2 GB size parameter you might have used another varchar variable with lesser size and concatenated in the varchar(max) variable , but this is just a guess.

edl0022 SSC Veteran Points: 261 More actions · Answer 3

thks guy for giving help, i found the problem is not the size limit to the nvarchar(max). it is missing part of the sql during execute....

i am still checking where is the problem....

Florent Rousseau Old Hand Points: 311 More actions · Answer 4

Hi, there is a post on the same topic on the SQL2005 part of the forum, with the solution:

http://www.sqlservercentral.com/Forums/Topic747429-145-1.aspx