异构查询需要ANSI_NULLS [英] Heterogeneous queries require the ANSI_NULLS
本文介绍了异构查询需要ANSI_NULLS的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我写了一个触发器。
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.
异构查询需要为连接设置ANSI_NULLS和ANSI_WARNINGS选项
。这样可以确保一致的查询语义。
启用这些选项,然后重新发出查询。
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 SET ANSI_WARNINGS为ON
,但是不起作用。 (链接服务器PostgreSql的触发器)
After that I've tried to use SET SET ANSI_WARNINGS is ON
,however it doesn't work. (a trigger for linked server PostgreSql)
推荐答案
尝试使用此功能
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
这篇关于异构查询需要ANSI_NULLS的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文