Linked Server及Stored Procedure裡的Ansi_Nulls設定
-- 在SQLServer_A 建立測試Table
Use tempdb
GO
Create table tb_Test (C1 char(1))
Insert into tb_Test values('A'),('B'),(null)
GO
-- Ansi_Nulls預設為ON,無法使用 = 或 <> 比對null
Select * from tempdb.dbo.tb_Test where C1 is null
Select * from tempdb.dbo.tb_Test where C1 = null
--將Session的Ansi_Nulls 設為off後,可以使用 = 或 <>比對null
Set Ansi_Nulls off
GO
Select * from tempdb.dbo.tb_Test where C1 is null
Select * from tempdb.dbo.tb_Test where C1 = null
若將Ansi_Nulls設為off,在本機上面 = null語法可正常執行,但無法找出值為null的資料列。
但透過 Linked Server時,Ansi_Nulls或Ansi_Warnings若設為off,執行 = null語法則會遇到7405錯誤。
Msg 7405, Level 16, State 1, Line 1
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.
-- 1)在SQLServer_B 建立Linked Server 連到SQLServer_A
-- 2)將Session的Ansi_Nulls或Ansi_Warning 設為off
-- 3)再執行SQL Statement去 Query SQLServer_A
另外,若在Stored Procedure裡將 = null語法組成字串動態執行,該Stored Procedue雖可以建立,但會在執行時出錯。
-- 建立Stored Procedure : SP_test
Use tempdb
Go
Set Ansi_Nulls off
GO
Create proc SP_test
as
begin
Exec('Select * from SQLServer_A.tempdb.dbo.tb_Test where C1 is null')
Exec('Select * from SQLServer_A.tempdb.dbo.tb_Test where C1 = null ')
End
需要特別注意的是,Stored Procedure會以建立時當下Session的Ansi_Nulls設定值為準!
即使在Stored Procedure裡更改設定或是在執行Stored Procedure時變更設定,都無法對Stored Procedue的執行結果造成影響。
Use tempdb
Go
-- Ansi_Nulls 預設值為on (1:on / 0:off),建立SP_2
Select SESSIONPROPERTY ('Ansi_Nulls')
GO
CREATE Proc SP_2
as
begin
Set Ansi_Nulls off -- 在Stored Procedure裡設定Ansi_Nulls off
Select SESSIONPROPERTY ('Ansi_Nulls') as [Ansi_Nulls setting]
Select * from tempdb.dbo.tb_Test where C1 <> null
End
查詢SP_2建立時,當下Session的Asi_Nulls設定值:
Select * from sys.sql_modules where object_id = (object_id('SP_2'))
在Stored Procedure裡設定的Ansi_Nulls off 無效:
exec SP_2
GO
在執行Stored Procedure,當下Session 的Ansi_Nulls off 無效:
Set Ansi_Nulls off
GO
exec SP_2
GO