Executing Dynamic SQL larger than 8000 characters

Can anyone tell me if there is a way to get around the 8000 character limit for executing dynamic SQL statements? I have tried everything I can think of to get around this limitation but I can not figure out a way around this.

Here are a few of the things that I have tried that have not worked

Using VARCHAR(MAX) instead on VARCHAR(8000)

Using NVARCHAR(MAX) instead of NVARCHAR(4000)

Using nTEXT (BLOBs are not support for variables)

Executing the statement via .NET using the SqlCommand.CommandText (it accepts a data type of String which is limited to 8000 characters)

I can't believe this is sooo hard to figure out. I know somebody has run into this before. All help would be greatly appreciated.

Create multiple 8000 char strings, break your string into 8000 char blocks and run "EXEC (@.sql1+@.sql2+@.sql3+.......)"|||

Tom,

Thanks for the help! However, that did not work either. My query is 8621 chars long I broke the query into two VARCHAR(8000) variables, one was 7900 and the other was 721. Here is the error:

The character string that starts with 'SELECT ...' is too long. Maximum length is 8000.

Not sure why it is not working for me if it works for you... what is the data type fo the variables that you are using?

|||I haven't seen that error before. However, I am usually executing multiple "commands", not 1 single command greater than 8000 chars. That might be a limitation of SQL, the command buffer might only be 8000 chars.

Maybe someone from MS can answer if that is a "command buffer limit"?

You might have to break it further into multiple select statements.|||

Can you post the code. There shouldn't be a problem executing sql statement larger than 8000 via exec().

e.g.

declare @.a varchar(8000),@.b varchar(8000),@.c varchar(8000)
select @.a='select top 1 name,''',@.b=replicate('a',8000),@.c=''' from sysobjects'
exec(@.a+@.b+@.c)

|||

varchar(max) also should work just fine - could you please try something like the following?

declare @.cmd varchar(max)
set @.cmd = 'print /*' + replicate ('-', 7990);
set @.cmd = @.cmd + replicate ('-', 7990) + '*/ getdate()';
exec (@.cmd)
print datalength (@.cmd)

Feb 2 2007 2:23PM
16000

|||you have to use the new sys.sp_sqlexec stored proc that accepts a parameter of type text. have used this on a numberof occassions with sql strings in excess of 8k limit.|||Thanks for all the help. Looks like I have several options here.