Linked Server及Stored Procedure裡的Ansi_Nulls設定

  • 5225
  • 0
  • 2012-05-30

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

clip_image002

 

--將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

clip_image004

 

若將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

clip_image006

 

另外,若在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

clip_image008

 

需要特別注意的是,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'))

clip_image010

 

 

在Stored Procedure裡設定的Ansi_Nulls off 無效:

exec SP_2

GO

clip_image012

 

在執行Stored Procedure,當下Session 的Ansi_Nulls off 無效:

Set Ansi_Nulls off

GO

exec SP_2

GO

clip_image012[1]