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

當使用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搞慘了，把這些資料公諸於世，讓大家不必再花冤枉時間，也算功德一件吧!