We help IT Professionals succeed at work.
Get Started

SQL error when accessing linked server

chasmx1 asked
Last Modified: 2012-06-27
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:
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
        RAISERROR(778429, 16, 1)

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

Watch Question
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE