PRB: OpenQuery Function Fails with Queries That Exceed 8000 Characters (286370)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 6.5
  • Microsoft SQL Server 7.0
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
This article was previously published under Q286370

SYMPTOMS

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:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near <...SQL...>

-or-

Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with <...SQL...>
In the preceding error messages, <...SQL...> vary based on the query text that you pass to the OPENQUERY function.

CAUSE

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

RESOLUTION

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.

STATUS

This behavior is by design.

MORE INFORMATION

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.
Modification Type:MajorLast Reviewed:12/5/2003
Keywords:kbDatabase kbprb KB286370
©2003 Microsoft Corporation. All rights reserved.