Heterogeneous queries require the ANSI_NULLS

Question

I've written a trigger.

USE [TEST]
GO
/****** Object:  Trigger [dbo].[TR_POSTGRESQL_UPDATE_YC]    Script Date: 05/26/2010 08:54:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[TR_POSTGRESQL_UPDATE_YC] ON [dbo].[BCT_CNTR_EVENTS]

FOR INSERT
AS 
BEGIN


DECLARE @MOVE_TIME varchar(14);
DECLARE @MOVE_TIME_FORMATED varchar(20);
DECLARE @RELEASE_NOTE varchar(32);
DECLARE @CMR_NUMBER varchar(15);
DECLARE @MOVE_TYPE varchar(2);

SELECT @MOVE_TIME = inserted.move_time
      ,@MOVE_TYPE = inserted.move_type
      ,@RELEASE_NOTE = inserted.release_note
      ,@CMR_NUMBER = inserted.cmr_number FROM inserted


IF(@MOVE_TYPE = 'YC')
    BEGIN

    SET @MOVE_TIME_FORMATED = SUBSTRING(@MOVE_TIME,1,4) + '-' + SUBSTRING(@MOVE_TIME,5,2) + '-' + SUBSTRING(@MOVE_TIME,7,2) + ' 00:00:00'

    --UPDATE OpenQuery(POSTGRESQL_SERV,'SELECT visit_cmr,visit_timestamp,visit_pin FROM VISIT') 
    --     SET visit_cmr = @RELEASE_NOTE 
    --     WHERE visit_timestamp = @MOVE_TIME_FORMATED
    --     AND   visit_pin = right(@CMR_NUMBER,5)
    --     AND   visit_cmr IS NULL

    END

    SET NOCOUNT ON;
END 

I've received an error, when I tried to insert a row.

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.

After that I've tried to use SET SET ANSI_WARNINGS is ON,however it doesn't work. (a trigger for linked server PostgreSql)

Burnett

Same error when set to "ON" in the trigger? – Belden

syntax Ok. I did a test 'trigger' on my local server - it's a work great!.. but on a real server .it`s give me an error (I used php application) to insert row. 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. – Burnett

Answer 1

Try to use This

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[TR_POSTGRESQL_UPDATE_YC] ON [dbo].[BCT_CNTR_EVENTS]

AFTER INSERT
AS 
BEGIN


DECLARE @MOVE_TIME varchar(14);
DECLARE @MOVE_TIME_FORMATED varchar(20);
DECLARE @RELEASE_NOTE varchar(32);
DECLARE @CMR_NUMBER varchar(15);
DECLARE @MOVE_TYPE varchar(2);

SELECT @MOVE_TIME = inserted.move_time
      ,@MOVE_TYPE = inserted.move_type
      ,@RELEASE_NOTE = inserted.release_note
      ,@CMR_NUMBER = inserted.cmr_number FROM inserted


IF(@MOVE_TYPE = 'YT')
    BEGIN

    SET @MOVE_TIME_FORMATED = SUBSTRING(@MOVE_TIME,1,4) + '-' + SUBSTRING(@MOVE_TIME,5,2) + '-' + SUBSTRING(@MOVE_TIME,7,2) + ' 00:00:00'

    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON

    UPDATE OpenQuery(POSTGRESQL_SERV,'SELECT visit_cmr,visit_timestamp,visit_pin FROM VISIT') 
           SET visit_cmr = @RELEASE_NOTE 
           WHERE visit_timestamp = @MOVE_TIME_FORMATED
           AND   visit_pin = right(@CMR_NUMBER,5)
           AND   visit_cmr IS NULL

    END

    SET NOCOUNT ON;
END 

Memorandum

Answer 2

The issue was solved when I added ANSI_NULLS and QUOTED_IDENTIFIER to the top of the query (stored procedure) and set them to ON.

Please see this example:

USE [YOUR_DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[STORE_PROC_NAME]
AS
BEGIN
    SELECT * 
    FROM Linked_Server.dbo.YOUR_TABLE
END

Ahmad