6

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?

Community
  • 1
  • 1
angel
  • 3,986
  • 10
  • 52
  • 82
  • Check this http://stackoverflow.com/questions/4833549/nvarcharmax-still-being-truncated – Anjali Oct 02 '13 at 14:34
  • problem is not with @var or type nvarchar(max) problem is with sp_executesql, which only support 8000 characters – angel Oct 02 '13 at 14:52
  • You may need to create views to move some of the statement outside the query if the restriction is in the stored procedure parameter. – Pekka Dec 14 '13 at 23:37

2 Answers2

3

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?

KyleMit
  • 45,382
  • 53
  • 367
  • 544
My Stack Overfloweth
  • 3,994
  • 3
  • 22
  • 34
  • problem is not with @var or type nvarchar(max) problem is with sp_executesql, which only support 8000 characters – angel Oct 02 '13 at 14:51
  • Hmm... does the following code work in your SQL? Edit: Having some errors pasting the code here. I'll message it to you. – My Stack Overfloweth Oct 02 '13 at 15:20
  • 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" – angel Oct 02 '13 at 15:21
  • What version of SQL Server are you using? – My Stack Overfloweth Oct 02 '13 at 15:28
  • is it diferent at other versions? i am using 2005 – angel Oct 02 '13 at 15:31
  • I think 2005 works a little differently from what I've read. One person suggests using "exec" instead on this link: http://sqlmag.com/stored-procedures/character-limitation-spexecutesql – My Stack Overfloweth Oct 02 '13 at 15:36
  • then is it imposible execute more then 8000 characters? is there not a solution? – angel Oct 02 '13 at 15:51
1

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

Jesus
  • 633
  • 3
  • 13
  • 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? – angel Dec 18 '13 at 14:41
  • This is really old I don't know if is still relevant but have a look http://sqlmag.com/stored-procedures/character-limitation-spexecutesql – Jesus Dec 18 '13 at 14:53