PRB: OpenQuery Function Fails with Queries That Exceed 8000 Characters

When you try to execute an extremely long query text (greater than 8000 characters) inside of an OPENQUERY function, with a linked server, you may see error messages similar to: In the preceding error messages, <...SQL...> vary based on the query text that you pass to the OPENQUERY function.

You are passing more than 8000 characters for the second parameter of the OPENQUERY function.

The second parameter (which is the query you want to execute on the remote linked server) of the OPENQUERY function can only accept 8000 characters.

To resolve this problem you must modify your query to fit into the 8000 character limit.

This behavior is by design.

The OPENQUERY function executes a pass-through query on the given linked server, which is an OLE DB data source. This function can be referenced in the FROM clause of a query as though it is a table name. The OPENQUERY function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, the OPENQUERY functions returns only the first one.

The limitation here is that you can only pass less than 8000 characters as your pass-through query with the OPENQUERY function.

Steps to Reproduce Behavior

1. Create a linked server through the SQL Enterprise Manager or by using the sp_addlinkedserver and sp_addlinkedsrvlogin stored procedures.
2. Try to run a long SQL statement (more than 8000 characters) from the SQL Server Query Analyzer. You can use a query similar to:

   SELECT * from OPENQUERY(YourLinkedServer, 'Select au_lname, au_lname, au_lname, ..... au_lname, au_lname from dbo.Authors')
   						

   In the preceding code example you can repeat the column name (au_lname) enough times to create a query that is greater than 8000 characters.

You can reproduce this problem by using any client that can execute SQL statements against SQL Server, including the SQL Server Query Analyzer as well as ODBC, OLE DB, and ActiveX Data Objects (ADO) applications.