Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21

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

  1. #16
    Join Date
    Sep 2002
    Posts
    5,938
    Tried put following in beginning of your script?

    SET ANSI_NULLS ON;

    SET ANSI_WARNINGS ON;

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

  3. #18
    Join Date
    Sep 2002
    Posts
    5,938
    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.

  4. #19
    Join Date
    Sep 2009
    Posts
    1
    In your connection string, change "AnsiNPW=NO" to "AnsiNPW=Yes"

  5. #20
    Join Date
    Jan 2010
    Posts
    1

    Solution for me

    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.

  6. #21
    Join Date
    May 2011
    Posts
    1
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •