none
Heterogeneous queries, ANSI_NULLS, ANSI_WARNINGS RRS feed

  • Question

  • I have stored procedure:

    EXEC sp_addlinkedsrvlogin @FailedRegionServerName, 'false', NULL, 'sa', 'pass'

    DECLARE @a varchar(100)
    SET @a = @FailedRegionServerName + '.Ithalat.dbo.Product'

    DECLARE @s varchar(100)
    SET @s = ' SELECT * FROM ' + @a
    EXEC ( @s )


    When I execute it I get the 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.

    Then I put
    SET ANSI_WARNINGS ON
    SET ANSI_NULLS ON
    lines into the procedure. Also checked "Ansi Nulls" and "Ansi Warnings" in the properties of SQL Server. It didn't work

    Then I tried:

    DECLARE @s varchar(300)
    SET @s = 'SET ANSI_WARNINGS ON; SET ANSI_NULLS ON; SELECT * FROM ' + @a
    EXEC ( @s )

    I still got the error.

    WHAT SHOULD I DO? HOW CAN I GET A TABLE CONTENT FROM A LINKED SERVER? Any will be appreciated, thanks a lot...

    Thursday, October 19, 2006 10:21 AM

Answers

  •  

    In Query Analyzer

    Set ANSI_NULLS ON;
    Set ANSI_WARNINGS ON;

    Execute and then remove these two lines of code.

    You can now write your create procedure code in Analyzer

    SQL server will remember these ansi settings evertime your procedure is subsequently called

    Tuesday, October 31, 2006 8:16 PM
  • You will have to create the stored procedures with those ANSI Settings:

    SET ANSI_WARNINGS ON
    SET ANSI_NULLS ON


    CREATE PROCEDURE Name (...)

    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Thursday, October 19, 2006 5:52 PM
    Moderator
  • Jens suggestion will work - I think you just need to use a GO between the SETs and the Create statement. Your stored procedure needs to be created with the settings on. So you just set those on in your session and then create the stored procedure.

    Set ansi_nulls on
    Set ansi_warnings on
    go
    Create Procedure YourProcedure ....

    -Sue

    Sunday, March 25, 2007 3:44 PM

All replies

  • You will have to create the stored procedures with those ANSI Settings:

    SET ANSI_WARNINGS ON
    SET ANSI_NULLS ON


    CREATE PROCEDURE Name (...)

    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Thursday, October 19, 2006 5:52 PM
    Moderator
  • Well, your suggestion had been tried and didn't work  

    All I need is to select some data from another server and insert into local server

    Thursday, October 26, 2006 10:18 AM
  •  

    In Query Analyzer

    Set ANSI_NULLS ON;
    Set ANSI_WARNINGS ON;

    Execute and then remove these two lines of code.

    You can now write your create procedure code in Analyzer

    SQL server will remember these ansi settings evertime your procedure is subsequently called

    Tuesday, October 31, 2006 8:16 PM
  • hi,

    did u get the solution for ur problem? i am facing the same error..

    Sunday, March 25, 2007 10:27 AM
  • Jens suggestion will work - I think you just need to use a GO between the SETs and the Create statement. Your stored procedure needs to be created with the settings on. So you just set those on in your session and then create the stored procedure.

    Set ansi_nulls on
    Set ansi_warnings on
    go
    Create Procedure YourProcedure ....

    -Sue

    Sunday, March 25, 2007 3:44 PM
  • I have the same problem and this resolution did not work.
    Thursday, August 20, 2009 7:34 PM
  • Gracias SUE, me funciono!! solo faltaba poner GO!
    BEsos

    Friday, September 25, 2009 4:31 PM
  • I'm getting the same error in a user-defined function



    CREATE FUNCTION [dbo].[fnComboJurisdiccion](@idestado varchar(2)) 
    RETURNS @tbl_jurisdiccion TABLE (idjurisdiccion varchar(2), Jurisdiccion varchar(20))
    AS 
    BEGIN
     -- Muestra las jurisdicciones del Estado seleccionado
     
     if @idestado='%'
      INSERT @tbl_jurisdiccion SELECT IDJurisdiccion='%', Jurisdiccion = 'TODAS'
     else
      INSERT @tbl_jurisdiccion SELECT IDJurisdiccion='%', Jurisdiccion = 'TODAS' UNION SELECT J.IDJurisdiccion, (J.IDJurisdiccion + ' - ' + J.nomJurisdiccion) as Jurisdiccion FROM SERVERAPP.redes.dbo.CTJurisdicciones J WHERE J.IDEstado = @idestado

     RETURN
    END

    already activated ANSI_NULLS and ANSI_WARNINGS on Connections from the server properties.

    any idea?

    thanks a lot

    Friday, January 22, 2010 5:03 PM
  • I found a new description for SET ANSI_WARNINGS

    https://msdn.microsoft.com/en-us/library/ms190368.aspx

    I hope it may help you


    Wednesday, August 19, 2015 8:16 AM