We help IT Professionals succeed at work.

SQL error when accessing linked server

chasmx1
chasmx1 asked
on
I'm writing an INSERT TRIGGER that is giving me the error:
Msg 7405, Level 16, State 1, Procedure RI_ReportInfo_ITrig, Line 40
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.

The trigger looks like this:
DECLARE @RefNumFound INT;
SET @RefNumFound = (SELECT COUNT(*) FROM CorsairFM.FM.dbo.WORKOBJ wo      -- linked table
      WHERE wo.RefNum = CAST(dbo.RemoveChars(inserted.RIMediaNumber) AS BIGINT))   -- clean all non-numeric character from RIMediaNumber
IF(@RefNumFound != 1)  -- if we find more than 1 match in the linked table something is wrong with users inputted data
   BEGIN
        RAISERROR(778429, 16, 1)
        ROLLBACK TRANSACTION
    END      

I can't seem to find the problem. Can you help?

Thanks
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019

Commented:
Just prefix this code at the beginning of your trigger to get it fixed.

SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO

Author

Commented:
This part of my INSERT TRIGGER.  Based on an INSERT and its values I need to find and update a row in a linked server on another server and database.  
Here is the INSERT TRIGGER:

PREVENT INSERT IF MEDIA NUMBER IS BLANK OR DOESN'T MATCH RECORD IN FusionFM LINKED TABLE (WORKOBJ.RefNum)
*/
DECLARE @RefNumFound BIGINT
DECLARE @RIMediaNumber BIGINT
SET @RIMediaNumber = (SELECT dbo.FixRIMediaNumber(RIMediaNumber) FROM inserted)
SET @RefNumFound = -1
SET @RefNumFound = (SELECT wo.RefNum FROM CorsairFM.FM.dbo.WORKOBJ wo
WHERE wo.RefNum = @RIMediaNumber) --CAST(inserted.RIMediaNumber AS BIGINT) ) -- clean all non-numeric character from RIMediaNumber

IF(@RefNumFound = -1) -- if we find more than 1 match in the linked table something is wrong with users inputted data
BEGIN
RAISERROR (N'<<%7.3s>>', -- Message text.
10, -- Severity,
1, -- State,
N'abcde'); -- First argument supplies the string.
END
/*
ELSE
BEGIN
UPDATE CorsairFM.FM.dbo.WBR_WebRequests
SET WBRMRTSReportCompleted = GETDATE()
WHERE WBRRefNum = @RefNumFound
END

*/  
What am I doing wrong?
Thanks
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019

Commented:
No issues with Trigger code..
Just drop your existing trigger and recreate it by prefixing script given below before CREATE TRIGGER and that should work..

SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO

Author

Commented:
When I try to insert a record in the base table I'm getting the following error (see attached).  It won't access the linked table CorsairFM.FM.dbo____
What am I doing wrong?

SQL-Insert-Error.bmp
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019
Commented:
Seems like your MSDTC is not configured properly.
Make sure it is configured correctly as per:

http://support.microsoft.com/kb/839279

both in SQL Server machine and linked SQL Server machine
And make sure you restart whole machine once which should work..

Author

Commented:
I check.  Thanks