0

Upon trying to insert into table via app, I receive following error message.

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection.

Neither ANSI_NULLS ON nor ANSI_WARNINGS ON did help...

SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[BenRef_UPDATE]
 ON [dbo].[ITRAEN]
  AFTER UPDATE
    AS

 BEGIN
     SET NOCOUNT ON

    IF UPDATE(poz2) 


   CREATE TABLE storeBenRef( 
      BeneficiaryRef VARCHAR(30),
      Partija VARCHAR(30) collate Serbian_Latin_100_CI_AS, 
      Datum DATETIME
     );

    INSERT INTO storeBenRef ( BeneficiaryRef, Partija, Datum ) 
      SELECT i.poz2, i.PARTIJA,i.DOTVORANJE
           FROM inserted i
             INNER JOIN deleted d 
                 ON i.PARTIJA = d.PARTIJA;


   SET ANSI_NULLS ON
   SET ANSI_WARNINGS ON


    UPDATE [EXPSRV1].[OPR].[protected].[TransferServiceArrangement] 
    SET PaymentDetailsBeneficiaryReferenceNumber = tmp.BeneficiaryRef
      FROM storeBenRef tmp
          INNER JOIN [EXPSRV1].[OPR].[protected].[TransferServiceArrangement]  
            trsa
              ON trsa.PaymentDetailsPayerAccountNumber = tmp.partija 
                   WHERE trsa.FirstPaymentDate = tmp.Datum



    DROP TABLE dbo.storeBenRef; 

        SET NOCOUNT ON;
     END;

Tried to put them before an update statement, tried to set them individually, but it did not work.

I ask for help.

unknown
  • 461
  • 9
  • 23
  • 1
    You need to set these session variables before executing the procedure. How exactly are you connecting and executing the SP from your app? – EzLo Jul 19 '18 at 11:44
  • There is no SP, insert into table is delegated directly from the source, this is just a trigger that will update another table located on the linked server, but I doubt this has anything to do with that one. – unknown Jul 19 '18 at 11:48
  • Hm maybe it has... When I comment this UPDATE section there is no error. You suggest I alter the SP responsible for transfering data from source to linked sarver table ? – unknown Jul 19 '18 at 11:51
  • If the linked server isn't SQL Server, the connection needs to set explicitly a value for those 2 behaviours. Read this https://stackoverflow.com/questions/16818120/why-does-ansi-nulls-and-ansi-warnings-need-to-be-set-to-on-for-linked-server-que – EzLo Jul 19 '18 at 11:53
  • It is sql server – unknown Jul 19 '18 at 11:53
  • I added nulls and warnings to sp's but nothing changed – unknown Jul 19 '18 at 11:54
  • What did you mean, where else should I add session variables ? – unknown Jul 19 '18 at 12:01
  • Is this not already answered either here: https://blogs.msdn.microsoft.com/sql_server_and_msbi/2015/03/18/error-heterogeneous-queries-require-the-ansi_nulls-and-ansi_warnings-options-to-be-set-for-the-connection/ or here: https://stackoverflow.com/questions/10300322/heterogeneous-queries-require-the-ansi-nulls-and-ansi-warnings-options-to-be-set – GandRalph Jul 19 '18 at 12:18
  • Tried to put it before the statement, did not help... – unknown Jul 19 '18 at 12:36

0 Answers0