1

I would like to execute dynamic SQL statements which are about 10,000 characters.

When I use sp_executesql as below:

DECLARE @stmt varchar(MAX)

SET @stmt = 'xxxxxxxx.................' which is about 10,000 characters

EXEC sp_executesql @stmt

I got the following error

The character string that starts with '  select t1.e_reference xxxxxxxxxxx' is too long. Maximum length is 8000.

As far as I know, we can use sp_executesql to execute very long statements, can't we?

I am using SQL Server 2008, Enterprise Edition, 64 bit.

How can I achieve this? Thanks.

TTCG
  • 8,805
  • 31
  • 93
  • 141
  • Try it with nvarchar(max), it takes a nvarchar parameter, so your varchar is having to be converted, slim chance that is causing the problem – Andrew Nov 16 '11 at 11:50
  • I have tried it with nvarchar(MAX) as well. But it doesn't work. – TTCG Nov 16 '11 at 11:56
  • 2
    Can you show more of your actual query? I can't reproduce this. In fact I can't even see that error in `sys.messages`. Are you querying a linked server? – Martin Smith Nov 16 '11 at 13:05
  • Yes, Martin. I am querying against Oracle Linked Server. – TTCG Nov 16 '11 at 14:09
  • The linked server part is kind of important information, well done to Martin for picking up on it – Andrew Nov 17 '11 at 14:37

4 Answers4

8

Based on your responses in the post, you are using linked server. The 8000 char limit is not posed by sp_executesql, but by OPENQUERY that you are probably using in your variable @stmt .

MSDN says this of OPENQUERY's arguments:

'query' Is the query string executed in the linked server. The maximum length of the string is 8 KB.

http://msdn.microsoft.com/en-us/library/ms188427.aspx

To bypass this, you could probably use

execute (@query) at oracle_linked_server
kate1138
  • 91
  • 3
  • 10
0

MSDN says this which is a bit vague: "The size of the string is limited only by available database server memory. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max)."

On 64-bit servers the limit is 2GB. It is not clear what is the limit of 32-bit servers? Is it 4000, 8000, whatever memory is available, 2GB?

0

Break the code up into strings of less than 4k characters, then replace single quotes with double quotes, lastly execute like below.

SET @STM1 = REPLACE( @STM1, CHAR(39), CHAR(39) + CHAR(39);
SET @STM2 = REPLACE( @STM2, CHAR(39), CHAR(39) + CHAR(39); 
SET @STM3 = REPLACE( @STM3, CHAR(39), CHAR(39) + CHAR(39);
EXECUTE( N'EXECUTE sp_executesql N''' + @STM1 + N' ' + @STM2 + N' ' + @STM3 + '''' );
Tyler Clement
  • 1
-1

The @stmt parameter for sp_executesql has a data type of nvarchar(8000), so you have exceeded the limit.

Either refactor your SQL statements into smaller parts, or put the SQL into a stored procedure.

DaveRead
  • 3,371
  • 1
  • 21
  • 24
  • based on what do you say it ? – Royi Namir Nov 16 '11 at 11:53
  • http://msdn.microsoft.com/en-us/library/ms188001(v=SQL.100).aspx - for a while now the docs have shown it is limited by nvarchar(max), historically back in sql 2k you would be right – Andrew Nov 16 '11 at 11:53