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

十一月 29, 2007

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

敬請留言

你的回應對我們是很重要的. 你的電子郵件將不會被公開.

請等待 ...
*
Loading Facebook Comments ...