Get Data Moving 1
Results 1 to 1 of 1

Thread: Linked Servers: The WHERE condition was filtered by DQP and not sent to Connect

  1. #1
    Adeeb Mass'ad is offline Support Manager
    Join Date
    Aug 2006
    Location
    Jaffa of Nazareth
    Posts
    169
    Rep Power
    14

    Linked Servers: The WHERE condition was filtered by SQLServer's DQP

    DESCRIPTION

    In some cases executing a Query under SQLServer/Linked Servers, the WHERE condition may be processed locally by SQLServer, and not sent to the Attunity Server.

    In this case SQLServer is sending the Query without a WHERE condition, where it retrieves the whole result-set, and then applies a FILTER on it with the WHERE condition the user specified.

    Further analyzing the case, it seems that the problem occurs when using string comparison.

    IMPORTANT NOTES
    1. This article is correct when using the Linked Server "4 part" query syntax (Non-Passthru).

    2. When using the OpenQuery method, SQLServer sends the query directly to the Attunity Server, so the artcile is not relevant in this case.

    3. There is a major performance hit, if SQLServer decided to process the WHERE condition locally.
    TROUBLESHOOTING

    To verify that the Query is being delegated to the Attunity Server without the WHERE condition, perform the followings:
    1. Run the “Query Analyser“, located at “Microsoft SQL Server“ group.
    2. Write the Query.
    3. Execute the Query.
    There are two methods where you can check how the Query was passed to Connect:
    • From the “Query“ menu, click on “Display Estimated Query Plan“. You will get the query plan, if you see a “FILTER“ object, then SQLServer is applying it's own FILTER.
    To verify that, click on the plan objects, and see how the query is being executed.
    • Open the nav.log from the Windows machine, located at:
    NAVROOT/TMP

    Go to the bottom of the log file, and search for your Query, check if the WHERE condition was sent.
    SOLUTION

    There are two possible solutions:
    1. Using the OpenQuery method, which can not be the optimal solution in all cases.
    2. Enabling the “Collation Compatible” flag of the Linked Server as follows:
    • Start SQL Server's Enterprise Manager.
    • Expand the Security/Linked Servers sub-tree.
    • Right-Click on the Linked Server, click Properties.
    • Go to the “Server Options“ tab, and enable the “Collation Compatible“ checkbox.
    LOGGING

    In case, the information in this article did not help you, provide the following logging information:
    • Attunity Server Log file from the Windows machine.
    • Enable both generalTrace and oledbTrace.
    Last edited by Adeeb Mass'ad; 01-25-2007 at 11:14 AM.
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •