sp_executesql or exec(@var) is too long. Maximum length is 8000

Question

I have large queries so i cant use linked server in production by rules. i pass a varchar(max) which this has more than 8000 characters.

but sp_executesql does not support more than 8000 characters then how can i execute my string?

Wyrick

Check this #4834049 – Preconcert

problem is not with @var or type nvarchar(max) problem is with sp_executesql, which only support 8000 characters – Wyrick

You may need to create views to move some of the statement outside the query if the restriction is in the stored procedure parameter. – Heretical

Answer 1

nvarchar(max) should work on SQL Server 2008 or later.

Does this work?:

declare @sql nvarchar(max)
set @sql = N'select' + CONVERT(NVARCHAR(MAX),REPLICATE(' ', 8000)) + ' ''Above 8000 character limit test'''
exec sp_executesql @sql

If you're using a version before that, you may need to split the query into multiple variables:

How to use SQL string variable larger than 4000 character in SQL server 2005 Stored Procedure?

Taber

problem is not with @var or type nvarchar(max) problem is with sp_executesql, which only support 8000 characters – Wyrick

Hmm... does the following code work in your SQL? Edit: Having some errors pasting the code here. I'll message it to you. – Taber

which code? have something as it declare @x nvarchar(max) set @x='select 1 number union all select 2 as number union all select 3 as number '(etc until this get more than 8000 caracters), this work if the length of @x is less to 8000 but when this is more then 8000 this does not work, and i got "is too long. Maximum length is 8000" – Wyrick

What version of SQL Server are you using? – Taber

is it diferent at other versions? i am using 2005 – Wyrick

I think 2005 works a little differently from what I've read. One person suggests using "exec" instead on this link: sqlmag.com/stored-procedures/character-limitation-spexecutesql – Taber

then is it imposible execute more then 8000 characters? is there not a solution? – Wyrick

Answer 2

Have you try to declare something like

declare  @var_1 nvarchar(4000);
declare  @var_2 nvarchar(4000);
         .
         .
         .
declare @var_n nvarchar(4000);

so you do something like this

exec(@var_1 + @var_2 + ....+ @var_n) 

Hope that this will help you

Chaoan

problem is not with variable, problem is with Exec apparently could receive only 8000 characters, then if i need execute a larger string, how can i execute? – Wyrick

This is really old I don't know if is still relevant but have a look sqlmag.com/stored-procedures/character-limitation-spexecutesql – Chaoan

Sorry this solution does not work – Tricorn