SQL Server Stored Procedure (預存程序) linked server bug

°★Shadow御､

當使用SQL Server 2000建立stored procedure來跨server執行時，會發生許多讓人莫名其妙的問題，也許SQL 2003/2005有修正吧，但畢竟尚有許多人使用SQL 2000，因此把這些心得提供大家參考。


關於stored procedure語法就不再多廢話，因此只說明如何透過stored procedure來跨server。


首先需建立linked server(連結伺服器)，方式有三種:

(1)打開Enterprise manager，在左邊Tree選單找到連結伺服器去新增。
(2)使用Query Analyzer，執行sp_addlinkedserver。
(3)使用程式(如VC/VB/ASP等)，去執行sp_addlinkedserver，當不需要時再做sp_dropserver。

第三種方式當然最麻煩，因為還要寫一堆code，但是如果在run-time才能決定去連結哪個server的話，就得用第三種方式，不過這種情況應該不多見。

第二種方式就是在Query Analyzer的SQL查詢視窗輸入:
EXEC sp_addlinkedserver 'serverName'

如果你的狀況還需要其他設定，sp_addlinkedserver詳細語法如: http://technet.microsoft.com/zh-tw/library/ms190479.aspx

執行了sp_addlinkedserver後，重新整理後會在"連結伺服器"出現外部的SQL server(沒有重新整理的話看不到喔)，這個方式的效果跟第一種方式其實一模一樣，只是第一種方式使用視窗式比較方便輸入設定。

OK，建立完成連結伺服器後，再來就看看怎麼去做連線了。

連線的方式也有三種:

(1)在Enterprise Manager使用查詢方式
如:
SELECT * FROM linkedServer...tableName (不指定db)
SELECT * FROM linkedServer.master.dbo.tableName (指定db)

(2)在Query Analyzer使用查詢方式
方式同上

(3)建立stored procedure去做
要建立stored procedure有兩個方式:在Enterprise manager用新增stored procedure方式、用Query Analyzer方式去建立。

如果你在Enterprise manager用新增stored procedure方式去使用linked server，永遠都會出現: 錯誤 7405 異質資料查詢需要設定ANSI_NULLS與ANSI_WARNINGS，但是當你在stored procedure設定

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

錯誤一樣出現 ... 真是被搞得無所適從

這個時候Query Analyzer就很重要啦，打開Query Analyzer後，在SQL window下輸入如:

CREATE PROCEDURE sp_yourSpName AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
select * from linkedserverName.master.dbo.tableName
GO

(當然以上select語句只是例子，請依需求修改)
神奇的是以上一模一樣的stored procedure內容卻無法在Enterprise manager去新增，錯誤7405永遠過不了 ...

使用Query Analyzer建好stored procedure後，就可以在Query Analyzer的SQL window去執行測試看看:

EXEC sp_yourSpName

(當然如果你的sp_yourSpName如果需要輸入引數，依你的需求去下EXEC指令)

就如此這般，使用了Query Analyzer可以pass掉ANSI_NULLS, ANSI_WARNINGS的錯誤啦，真是被Microsoft搞慘了，把這些資料公諸於世，讓大家不必再花冤枉時間，也算功德一件吧!