SQL Server Performance

linked servers require ANSI_NULLS and ANSI_WARNING

Discussion in 'General DBA Questions' started by Chappy, Jan 29, 2003.

  1. Chappy New Member

    When trying to access a linked server in a stored procedure, the syntax checker fails the query with the result...

    ---------------------------
    Microsoft SQL-DMO (ODBC SQLState: 42000)
    ---------------------------
    Error 7405: 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.
    ---------------------------
    OK
    ---------------------------


    I have gone onto both the source and destination linked server, and set ANSI_WARNINGS and ANSI_NULLS to ON.

    Why am I still getting this error ?
    Chappy, Jan 29, 2003
    #2

  2. satya Moderator

    Not 'in' the procedure.
    Use these settings while creating the procedure

    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON
    GO
    CREATE PROCEDURE xxx(...
    GO

    Satya SKJ
    satya, Jan 29, 2003
    #2

  3. Chappy New Member

    Many thanks satya!
    I didnt realise the SP had to be recreated after setting those ansi options.
    Chappy, Jan 29, 2003
    #2

  4. satya Moderator

    BOL referred that SET ANSI_NULLS should be set to ON for executing distributed queries.

    HTH

    Satya SKJ
    satya, Jan 29, 2003
    #2

Share This Page