Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

PRB: OpenQuery Function Fails with Queries That Exceed 8000 Characters


View products that this article applies to.

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.

↑ Back to the top


Cause

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

↑ Back to the top


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.

↑ Back to the top


Status

This behavior is by design.

↑ Back to the top


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.

↑ Back to the top


Keywords: KB286370, kbprb, kbdatabase

↑ Back to the top

Article Info
Article ID : 286370
Revision : 6
Created on : 12/5/2003
Published on : 12/5/2003
Exists online : False
Views : 198