SQL Server Performance

Error while Running the Query using Linked server

Discussion in 'General DBA Questions' started by sivaraman, Dec 20, 2005.

  1. sivaraman New Member

    Hello - trying to run a query joining an existing SQL Server table to a table on a linked server (Sybase), and I get the following error:

    "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."

    I have Executd the sp_dboption stored proc..even it is thro'ing error ....

    Server: Msg 7405, Level 16, State 1, Line 1
    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.

    Regards
    Sivaraman Latchapathi
  2. dineshasanka Moderator

  3. satya Moderator

    PLease mention whether this is a problem on SQL Server 2005 instance?

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. sivaraman New Member

    No Satya...I am using SQL Server 2000...while Running the Query in Query analyser it is giving the error....

    I am just Runnig the Select Query Like :- Select * from LinkedServerCon.DBName.DBO.TableName

    Some Client of the M/c It is working, but some M/c it is giving Error..

    Regards
    Sivaraman Latcapathi


    quote:Originally posted by satya

    PLease mention whether this is a problem on SQL Server 2005 instance?

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. sivaraman New Member

  6. Ariana New Member

    Add this line before your query and it should do the trick:

    SET ANSI_NULLS ON SET ANSI_WARNINGS ON
    GO



    quote:Originally posted by sivaraman

    Hello - trying to run a query joining an existing SQL Server table to a table on a linked server (Sybase), and I get the following error:

    "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."

    I have Executd the sp_dboption stored proc..even it is thro'ing error ....

    Server: Msg 7405, Level 16, State 1, Line 1
    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.

    Regards
    Sivaraman Latchapathi
  7. satya Moderator

    Ensure both the SQL server are patched up to the similar levelsof service pack.
    Another workaround is to create the stored procedure from Query Analyzer. Note that the default ANSI setting for Query Analyzer must be set to ON in order for this to work.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page