Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for

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, Ariel

You can change server connection options in server properties in ssms.

Which options do I have to change?

ANSI_NULLS and ANSI_WARNINGS as you listed.

Thanks, but the problem persists.

Did you restart sql?

Yes, but it didn't work. One question, in wich server I must change the options in the Linked server or in the one is referencing the linked server?

On server where you run the query from.

Sorry, but it doesn't solve the problem...

Then you need to work with your app vendor.

The problem is that is impossible, because the company does not exists any more...

Try drop existing synonym, recreate it in query windows. Ensure you set those ansi options first before create synonym statement in same query window.

Can you help me with all the steps:
1. The original database server is configured without the options (name: ORIGINAL)
2. The original server is configured without the options (name: DB)
3. The referring server is configured with the options
4. Create a new databse and assign the options (name: DB)

CREATE DATABASE DB COLLATE SQL_Latin1_General_CP1_CI_AS
GO
ALTER DATABASE DB SET ANSI_NULLS ON WITH NO_WAIT
GO
ALTER DATABASE DB SET ANSI_WARNINGS ON WITH NO_WAIT
GO

5. Add the linked server (name: REF)

sp_addlinkedserver @server='REF', @srvproduct='', @provider='SQLNCLI', @datasrc='ORIGINAL'

6. Create the synonims (using a cursor)

EXECUTE('USE DB; SET ANSI_NULLS ON; SET ANSI_WARNINGS ON; CREATE SYNONYM ' + @name + ' FOR REF.DB.dbo.' + @name)

This is the way you recommend me?

Try that to see if works.

Sorry, I still have the same problem....