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?

kigelThese 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.|||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