October 23, 2006 at 10:01 am
I'm getting an error message when I attempt to insert to a linked server. I have searched the sqlservercentral and viewed a lot of suggestions, but I can't seem to get this to work. I'm using both sqlserver2000 db and have all the latest updates and hotfixes.
I'd appreciate any suggestions!
exec DTSCopyInventoryLookup 'INTERFLOW02'
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.
My code sample with comments is below:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE DTSCopyInventoryLookup 'INTERFLOW02'
@LinkedServer varchar(100)
AS
declare @strQuery varchar(8000)
--Part A
set @StrQuery=@StrQuery+'delete openquery('
set @StrQuery=@StrQuery+@LinkedServer
set @StrQuery=@StrQuery+','
set @StrQuery=@StrQuery+''' select * from Inventory.dbo.Transmissions'
EXEC(@StrQuery)
drop table #Stmt
--create a temp table that will store the insert statments
CREATE TABLE #Stmt (descr varchar(1000) NOT NULL )
insert into #Stmt
select 'SET ANSI_NULLS ON SET ANSI_WARNINGS ON insert openquery('+@LinkedServer+',''select transmissionid,description,sortorder,active from Inventory.dbo.Transmissions where 1=0'') VALUES('+cast(transmissionid as varchar)+','''+description+''','+cast(sortorder as varchar)+','+cast(active as varchar)+')'
from Web_Inventory.dbo.Transmissions
--This is the statment that is stored in @Stmt.descr
--It executes just fine from QA
--SET ANSI_NULLS ON SET ANSI_WARNINGS ON insert openquery(INTERFLOW02,'select transmissionid,description,sortorder,active from Inventory.dbo.Transmissions where 1=0') VALUES(0,'None',0,1)
--My version of master.dbo.xp_execresult set
exec IFexecresultset "select descr from #Stmt","Inventory"
--exec IFexecresultset "SET ANSI_NULLS ON SET ANSI_WARNINGS ON select descr from #Stmt","Inventory"
--But I still get this 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.
--Now, use this undocumented command to execute the query result set
--master.dbo.xp_execresultset "select descr from #Stmt","Inventory"
--master.dbo.xp_execresultset "SET ANSI_NULLS ON SET ANSI_WARNINGS ON select descr from #Stmt","Inventory"
--But I get this error
--Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
--options to be set for the connection. This ensures consistent query semantics.
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--My version to insure the settings are done properly
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE procedure dbo.IFexecresultset
(
@cmd nvarchar(4000),
@dbname sysname = NULL,
@debug bit = 0
)
as
begin
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
declare @retcode bit,
@proc nvarchar(4000)
-- check to ensure that the @dbname provided is neither null or empty
if isnull(rtrim(@dbname), N'') = N''
begin
raiserror('Internal Error : @dbname cannot be null or empty.', 16, -1)
return 1
end
select @proc = quotename(@dbname) + N'.dbo.sp_execresultset'
exec @retcode = @proc @cmd, @debug
return @retcode
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
October 23, 2006 at 4:11 pm
As it turns out xp_execresultset calls master.dbo.sp_execresultset. THAT sproc was not create with set ANSI_NULLS ON. So, I recreated it that way and everything runs just fine.
October 24, 2006 at 6:42 am
This officially voids Microsoft warranty and support on that server. In cases like this you can always COPY the object under a different name and make the modifications required to fit your needs. Also you can use your own version of the objects without fear that they will be overwritten by a service pack upgrade.
October 24, 2006 at 8:12 am
Thanks for the tip -- I will certainly do as you suggested.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply