Post reply

Where and How to SET ANSI DEFAULTS for Heterogeneous distributed query in linked SQL Servers 2000

  • January 15, 2005 at 12:03 am

    #84668

     

    Error Message:

    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 tried SET ANSI_NULLS ON and SET ANSI_WARNING ON but still keep getting same error message for the following query.

    I am attempting to run the following query in 3 properly linked SQL server 2000. I am able to do distributed query and successfully retrieve data. But could not do so using following command. I have to do it this way because I do not know ahead of time which server I need to access. Server name is computed dynamically.

    Here is the query that give above error.

    CREATE PROCEDURE test

    @m1 varchar(10)

    as

    declare @sql varchar(100)

    select @sql = 'select LastName from   ' + @m1 + '.Northwind.dbo.employees'

    print @sql

    exec (@sql)

    GO

    Then I used it as follows:

    exec test 'Beta"

    [Beta is the name of one of the linked server.]

  • January 18, 2005 at 8:15 am

    #537848

    Have you tried this?

    CREATE PROCEDURE test

    @m1 varchar(10)

    as

    declare @sql varchar(100)

    select @sql = 'SET ANSI_NULLS ON GO SET ANSI_WARNING ON GO select LastName from ' + @m1 + '.Northwind.dbo.employees'

    print @sql

    exec (@sql)

    GO

  • January 18, 2005 at 2:07 pm

    #537926

    Thanks a lot.

    it works now.

    I had to create the stored procedure from query analyzer, had GO statement after ANSI_NULLS ON and ANSI_WARNINGS ON but before create statement and I did not have GO keyword at the end of the SP. I had Go at the end of ansi off.

    This might help others.

     

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply