-
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.
-
-
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....
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|