# Database Discussions > Microsoft SQL Server 2005 >  Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for

## arilani

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

----------


## rmiao

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

----------


## arilani

Which options do I have to change?

----------


## rmiao

ANSI_NULLS and ANSI_WARNINGS as you listed.

----------


## arilani

Thanks, but the problem persists.

----------


## rmiao

Did you restart sql?

----------


## arilani

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?

----------


## rmiao

On server where you run the query from.

----------


## arilani

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

----------


## rmiao

Then you need to work with your app vendor.

----------


## arilani

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

----------


## rmiao

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

----------


## arilani

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
GO5. 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?

----------


## rmiao

Try that to see if works.

----------


## arilani

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

----------


## rmiao

Tried put following in beginning of your script?

SET ANSI_NULLS ON; 

SET ANSI_WARNINGS ON;

----------


## arilani

I'll explain you the problem.

I have a 3rd party application runing against a SQL Server 2005 database.
I have to move the database to another server, but the application must not notice the server translation, so I moved the database to the second server and in the original one linked the other server and created synonyms to all the moved objects.

The problem is because the application explicity sets *ANSI_WARNINGS OFF* and I cannot get the source code or ask the vendor to change the code.

So I cannot *SET ANSI_WARNINGS ON*

Thanks, Ariel

----------


## rmiao

Only app vendor can help you, but you said vendor doesn't exist anymore. Whatelse we can do? Only thing I can think of is renaming your server.

----------


## Kosnick

In your connection string, change "AnsiNPW=NO" to "AnsiNPW=Yes"

----------


## luiz_ab

The solution was:

CREATE THE PROCEDURE LIKE THIS...

SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO

CREATE PROCEDURE dbo.MyProcWhatever AS BEGIN
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
...

COOL..
 NOW CHECK IF THE CREATION WENT OK WITH THIS
SELECT OBJECTPROPERTY(OBJECT_ID('MyProcWhatever '), 'ExecIsAnsiNullsOn')
SELECT OBJECTPROPERTY(OBJECT_ID('MyProcWhatever'), 'IsAnsiNullsOn')

TO BE OK BOTH MUST RETURN 1!

COOL..

NOW EXECUTE THE PROCEDURE LIKE THIS...

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

EXEC dbo.MyProcWhatever 

THAT SOLVED THE PROBLEM FOR ME..

THE MAIN THING WAS BEFORE CALLING THE PROCEDURE SETTING THE NULLS AND WARNINGS ON AGAIN.

----------


## gillianreynolds

The reason for turning the warnings off immediately is because the stored proc. is executed in VB6 to populate a record set object. If after the creation of sproc, NSI_WARNINGS, NOCOUNT, ANSI_NULLS are on, then the recordset object crashes. The article you sent me, talks about how to fix a problem creating a stored sproc. After the stored proc is created, the warnings, nocount and ANSI_NULLS can be turned off. I have other sprcos that work this way with different linked servers. For some reason this one does not.

----------

