Monday, March 12, 2012

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set

Hi,

I have a problem with linked servers.

I have an application running against a SQLServer 2005 Express. For some limitations, I had to access from the same application to another database, but I cannot change to another server.

So I have 2 created a second instances, where the first one refers the second one and I created synonyms in the first one to access to all the objects in the second one, to emulate a database in the first instances, but running on the second one. The final idea is to move to another server, but for the testing I use another instance.

But when I try to access to the aplication database, I hav the following error: 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 searched solutions for this issue, but I only found to add SET ANSI_NULLS ON and SET ANSI_WARNINGS ON to my connection, before the queries, but I can't, because I cannot change the application.

If anyone can help me, I'd be veri greatfull

Best regards, ArielAriel,
I had a similar problem with SQL 2000 when I added a stored procedure to access the data on a linked server. I had created the stored proc through Query Analyzer and the work around was to create the stored proc through Enterprise Manager. Once I created within Enterprise Manager it worked.
A google search of this problem will provide results.|||The problem is the application have a lot of SPs, tables and views I have created the synonims.

The problem is only with the SPs?

If that, I can remove the synonims and create SPs that access the original ones.

Thanks, Ariel

No comments:

Post a Comment