Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
thussain
Starting Member
7 Posts |
 Posted - 2011-04-22 : 12:30:05
|
| I have an application that is creating a dynamic query greater than 8000 character and my SQL 2000 is chopping off extra character as SQL limit varchar a length of 8000 character Maximum.The good thing is I also have soem SQL server 2005 machine whcih support varchar(max) and almost all the time my dynamic SQL will be greater than 8000 character when used on a machine of SQL 2005.Is there any way I can check if my dyanmci query is extending beyond 8000 character I can use a method liek this,.IF LEN(@sql) < 5000 DECLARE @sql1 varcahr(8000)ELSE DECLARE @sql1 varcahr(max)However whn I do that I am getting an error saying variable can be defined only once. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-22 : 12:37:14
|
| You can't use an IF to declare 2 different sizes, and there's no point if you can use varchar(max), just go with that.If you have to work on SQL 2000 you can declare multiple varchar(8000) variables (@sql1, @sql2, @sql3, etc.) and do this:EXEC(@sql1 + @sql2 + @sql3) |
 |
|
|
ganeshkumar08
Posting Yak Master
187 Posts |
Posted - 2011-04-25 : 06:23:41
|
| Otherwise you directly use as belowEXEC('Your Dynamic Query');Dont declare any variable and assign the value into it.Solutions are easy. Understanding the problem, now, that's the hard part |
|
|
|
|
|
|
Dynamic Query > 8000 Character