Execute very long statements in TSQL using sp_executesql

Question

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.

Answer 1

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