October 18, 2006 at 9:48 am
Thanks in advance.
I have a trigger that uses subqueries to update some fields. The queries check working table and historic tables. We moved to a new SQL 2005 server x62 and setup linked servers. I added ansi_nulls and ansi_warnings as below. I still get the error below, which must be from subqueries. I tried adding settings on each subquery, right before each linked server select "(SET ANSI_NULLS ON SET ANSI_WARNINGS ON select ..." . But i get a syntax error there?
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.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tri_InsertUPSShipmentTrigger] ON [dbo].[tblUPSShipments]
FOR INSERT
AS
SEt ANSI_WARNINGS ON
--...........
-- get order cost from Dynamics
if ( (@oenum LIKE 'O%') AND (LEN(@oenum) >= 8) )
Begin
-- lookup Dynamics order cost
SET ANSI_NULLS ON SET ANSI_WARNINGS ON
update dbo.tblShipReport
set ordercost = (select SUBTOTAL from NACR10.nacr.dbo.SOP30200 where SOPNUMBE = @oenum UNION select SUBTOTAL from NACR10.nacr.dbo.SOP10100 where SOPNUMBE = @oenum)
where dbo.tblShipREport.OrderNumber = @oenum
-- Update Lines and Pieces from Dynamics
SET ANSI_NULLS ON SET ANSI_WARNINGS ON
update dbo.tblShipReport
set lines = (select sum(q) from (select isnull(count(LNITMSEQ),0) as q from NACR10.nacr.dbo.SOP30300 where SOPNUMBE = @oenum UNION all select isnull(count(LNITMSEQ),0) as q from NACR10.nacr.dbo.SOP10200 where SOPNUMBE = @oenum) as derived ),
pieces = (select sum(q) from (select isnull(sum(quantity),0) as Q from NACr10.nacr.dbo.SOP30300 where SOPNUMBE = @oenum UNION ALL select isnull(sum(quantity),0) as Q from NACR10.nacr.dbo.SOP10200 where SOPNUMBE = @oenum ) as derived)
where dbo.tblShipReport.OrderNumber = @oenum
end
END
October 23, 2006 at 8:00 am
This was removed by the editor as SPAM
October 23, 2006 at 11:13 am
I'm having a similar issue. Let me know what you find and I'll do the same.
There are several posts that instruct you to be sure that you create the sproc using SET ANSI_NULLS ON and SET ANSI_WARNINGS ON. As well, several posts point out that these original settings are kept and any set done within the sproc are ignored.
I dropped my sproc and recreated as instructed and I still get the error.
Good Luck!
October 23, 2006 at 1:47 pm
What is the value for this (1=ON):
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.tri_InsertUPSShipmentTrigger'), 'ExecIsAnsiNullsOn')
AS 'AnsiNulls'
October 23, 2006 at 1:51 pm
The result is 1
October 23, 2006 at 1:58 pm
Hum, obviously you have indeed created this the way all the posts suggest (ANSI_NULLS ON). Just wanted to verify.
Wish I had a solution for you. I have a very similar situation. I've seen many other posts with exactly your question, but never a solution.
Hard to believe one of those really smart gurus hasn't posted back
Good Luck.
October 23, 2006 at 4:14 pm
I did figure out the issue I had. My sproc was created with SET ANSI_NULLS ON. Within my sproc I was calling master.dbo.execresultset. THAT sproc was not created with SET ANSI_NULLS. I recreated with SET ANSI_NULLS ON and everything works just fine.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply