Microsoft KB Archive/286370

= PRB: OpenQuery Function Fails with Queries That Exceed 8000 Characters =

Article ID: 286370

Article Last Modified on 12/5/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * 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
 Create a linked server through the SQL Enterprise Manager or by using the sp_addlinkedserver and sp_addlinkedsrvlogin stored procedures.  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.

Keywords: kbdatabase kbprb KB286370

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.