January 15, 2005 at 12:01 am
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 16, 2005 at 8:59 pm
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
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
In the window you EXEC the query,
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
EXEC test 'Beta'
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply