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

sql-server-2005 varchar sp-executesql large-data
angel picture angel · Oct 2, 2013 · Viewed 10.1k times · Source

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?

Answer

My Stack Overfloweth picture My Stack Overfloweth · Oct 2, 2013

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?

Related questions

What are the main performance differences between varchar and nvarchar SQL Server data types?

I'm working on a database for a small web app at my school using SQL Server 2005. I see a couple of schools of thought on the issue of varchar vs nvarchar: Use varchar unless you deal with a lot of …

Read More
Is the LIKE operator case-sensitive with MSSQL Server?

In the documentation about the LIKE operator, nothing is told about the case-sensitivity of it. Is it? How to enable/disable it? I am querying varchar(n) columns, on an Microsoft SQL Server 2005 installation, if that matters.

Read More
How to store a string var greater than varchar(max)?

I'm trying to do this: DECLARE @myVar VARCHAR(MAX) Loop with cursor select @myVar = @myVar + bla bla bla end loop When the loop ends, @myVar is incomplete, containing only 8000 characters. I have tryed to use text, but is not allowed …

Read More