Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    11

    Red face Unanswered: Linked Server / OLE DB problem

    Hi,

    I've connected 2 servers using the linked server option.

    Tables in Server A contains some triggers that enforces changes in tables in Server B.

    When making changes in server A from the enterprise manager (and invoking the triggers) everything works fine. But when an outside application is responsible for the changes the following message appears:

    "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query."

    I've tried setting the ANSI_NULLS ON 1.inside the triggers, 2. in the script that creates the triggers and 3.in the servers' properties tab but it doesn't help.

    Any ideas?

    kigel
    kigel

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    These set statments need to be made by the application doing the modifications. You can try to force this by Using EM, right click on the server name, select properties and go to the Connections tab.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Mar 2003
    Posts
    11

    Wink Re: Linked Server / OLE DB problem

    It worked! thanks!

    I tried your first suggestion - enabling the "enforce distibuted transactions" under server properties --> connections tab. In addition i enabled the ANSI_NULLS and ANSI_WARNINGS again under server properties --> connections tab. I repeated the operation on both servers, stopped them and started them again and it worked.

    One thing: on one of the sessions (in the interface application) i got the message: "OLE DB provider 'SQLOLEDB' reported an error". The message appeared only once, in one of the sessions and i believe/hope it is not related.

    thanks again,

    kigel
    kigel

Posting Permissions

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