Page 1 of 2 12 LastLast
Results 1 to 15 of 21

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

  1. #1
    Join Date
    Sep 2007
    Posts
    9

    Question 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

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    You can change server connection options in server properties in ssms.

  3. #3
    Join Date
    Sep 2007
    Posts
    9
    Which options do I have to change?

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    ANSI_NULLS and ANSI_WARNINGS as you listed.

  5. #5
    Join Date
    Sep 2007
    Posts
    9
    Thanks, but the problem persists.

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Did you restart sql?

  7. #7
    Join Date
    Sep 2007
    Posts
    9
    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?

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    On server where you run the query from.

  9. #9
    Join Date
    Sep 2007
    Posts
    9
    Sorry, but it doesn't solve the problem...

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    Then you need to work with your app vendor.

  11. #11
    Join Date
    Sep 2007
    Posts
    9
    The problem is that is impossible, because the company does not exists any more...

  12. #12
    Join Date
    Sep 2002
    Posts
    5,938
    Try drop existing synonym, recreate it in query windows. Ensure you set those ansi options first before create synonym statement in same query window.

  13. #13
    Join Date
    Sep 2007
    Posts
    9

    Thumbs up

    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?

  14. #14
    Join Date
    Sep 2002
    Posts
    5,938
    Try that to see if works.

  15. #15
    Join Date
    Sep 2007
    Posts
    9
    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
  •