SQL server :Use variable to hold string of size greater than 8000.

Question

Hello All I am using SQL Server 2008. In my Sproc i am taking big sql query in nvarchar(max) type variable and execute it with exec....but the problem is that its exceeding limits and i am not able to execute whole query.my query size is 9778. help me if you know the solution for this problem.

Pravin More · Answer

hi...for now i took that query in c# instead of sproc n it worked. ya even i think there might be some syntax error but due to lack of time i executed it from c# code. thanx suthish for your help. Pravin.

Suthish Nair · Answer

Resolved? check for spaces between statements etc..

Suthish Nair · Answer

send me on mail..

Pravin More · Answer

Thanks suthish. but i can't paste sproc code

Suthish Nair · Answer

Post/attach your SP here.

Pravin More · Answer

yes suthish i printed query but its able to print only half part because of limit and therefore i am getting error is Incorrect syntax near.

Suthish Nair · Answer

Then it might be different problem. Are you able to print the sql statements (print @sqlquery). fire/execute the query in new window.. what exact error you getting?

Pravin More · Answer

Thank you suthish for reply.i tried varchar(max) too but its not working.

Suthish Nair · Answer

change the datatype to varchar(max) or nvarchar(max). use varchar(max).