SQL Server 2012與 SQL Server 2000的連結伺服器設定

by adonis 29. 十一月 2013 13:56

作    者:楊先民
審    稿:張智凱

前言

最近被學員遇到一個問題,就是他試圖用 SQL Server 2012設定連結伺服器(Linked Server)到 SQL Server 2000,卻沒有辦法連線成功,會出現錯誤訊息,所以本期就針對這方面的主題進行了解。

連結伺服器

有使用過 SQL Server的人應該都了解,當你的資料放置在多台 SQL Server中,需要將這些資料串連起來查詢,就必需設定所謂的連結伺服器。連結伺服器說穿了,只不過是在SQL Server上「註冊」一台非本機的 SQL Server罷了,但要知道,連結伺服器並不是只能連到 SQL Server,它在設定的時後就可以是要連到 SQL Server,或是非 SQL Server,如圖:

image

如果你不是使用管理工具,而是使用 T-SQL指令的話,則是使用 sp_addlinkedserver的指令,甚至連 Access資料庫都可以連結,如下:
EXEC sp_addlinkedserver
     @server = N'自訂名稱',
     @provider = N'Microsoft.Jet.OLEDB.4.0',
     @srvproduct = N'OLE DB Provider for Jet',
     @datasrc = N'C:\MSOffice\Access\Samples\Northwind.mdb';
  GO

不過,如果是比較新版的 Access,則需要使用 Ace.OLEDB來連線了,如下:
EXEC sp_addlinkedserver
     @server = N'自訂名稱',
     @provider = N'Microsoft.ACE.OLEDB.12.0',
     @srvproduct = N'OLE DB Provider for ACE',
     @datasrc = N'C:\MSOffice\Access\Samples\Northwind.accdb';
  GO

那麼,如果設定完連結伺服器之後,該如何連線呢?我們假設你已經把安全性的設定搞定了(因為如果你連線到 Access,預設是不用設定安全性的,因為 Access沒有安全性的設定),那只需要使用:
select * from 自訂名稱…資料表名稱
即可連接到 Access資料庫中的資料表存取資料,因為 Access資料庫是沒有資料庫名稱,也沒有結構描述名稱的,但是點還是要提供,所以就變成:
自訂名稱…資料表名稱了。

回歸正題

接下來我們回歸正題,如果是 SQL Server 2012要連到 SQL Server 2000的話,該如何設定連接伺服器呢?如果你就直接設定「伺服器類型」為 SQL Server,那麼當你試圖利用
select * from 2000Servername.dbname.dbo.object去存取 SQL Server 2000資料庫中的資料表時,這時就會很殘忍的出現一個錯誤訊息:

OLE DB provider “SQLNCLI11″ for linked server “NorthWind2000″ returned message “Client unable to establish connection”.

這時你會不會感覺自己好像設定錯了?

通常的建議是選伺服器類型為「其他資料來源」,然後利用 OLE DB的方式連接到 SQL Server 2000,不過最後的結果依然是出現相同的錯誤訊息,甚至有時你還會加碼看到下面這個錯誤訊息:

Msg 22, Level 16, State 1, Line 0
SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions.

也就是說,沒有錯, SQL Server 2012之後,不再支援 SQL Server 2000的直接連結伺服器的連結。

那麼,該怎麼辦呢?

由於 SQL Server 2012只支援伺服器類型為 SQL Server 2008R2、SQL Server 2008以及 SQL Server 2005的向前相容兩個版本的 SQL Server連結,所以要嘛你就必需把 SQL Server 2000升級到 SQ Server 2005之後的版本,要嘛就是使用另外一招了,也就是使用 ODBC的方式來連結,不過如果使用 ODBC連結的話通常需要考慮到效能的問題,以及你要設定 system dsn。

由於要使用 ODBC 的連結來連到 SQL Server,所以建議各位使用 T-SQL指令來建立連結伺服器的設定,也就是我們假設你的 SQL Server 2000的伺服器名稱為 VS100,則需要利用sp_addlinkedserver設定的步驟如下:

A的方式:
1.    開啟控制台,搜尋 ODBC,選擇「設定資料來源」(ODBC)。
2.    選擇「系統資料來源名稱」,建立一個系統的 data source name,假設名稱為 VS100DSN。
3.    請務必設定系統資料來源名稱,因為是認這台電腦的,如果是選擇「使用者資料來源名稱」,則只有該使用者才看的到。
4.    設定 data source name時,選擇 SQL Server,並且連向到 SQL Server 2000的電腦名稱,無論使用 Windows驗證或是 SQL驗證都可以,預設資料庫可以不需要設定。
5.    設定完成後使用下面的指令:

EXEC master.dbo.sp_addlinkedserver @server = N'VS100', @srvproduct=N'MSDASQL', @provider=N'MSDASQL', @datasrc = N'VS100DSN', @location=N'System'; 

這個指令應該就直接把關鍵字改一改就可以直接用了(笑)
比較重要的是 srvproduct 以及 provider,在這裡都是 MSDASQL,所以並不是

EXEC master.dbo.sp_addlinkedserver @server = N'VS100', @srvproduct=N'MSSQL', @provider=N'SQLNCLI', @provstr=N'PROVIDER=SQLOLEDB;SERVER=VS100'

這樣的設定,因為這裡的 provider是 SQLMCLI,已經無法連線到 SQL Server 2000的環境了,以圖型的界面設定如下:

image

接下來,還得設定安全性,這裡你可以直接把管理工具打開來設定,或是使用下面的 T-SQL指令:

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'VS100',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL GO

以圖型的界面設定如下:

image

主要還是利用本機 SQL Server 2012的帳號,對映到 SQL Server 2000的帳號方式進行連線。

B的方式:
如果你不想設定 system DSN的話,也可以直接把設定 system DSN的語法寫在 T-SQL中,也就是如下的設定:

EXEC master.dbo.sp_addlinkedserver @server = N'VS100', @srvproduct=N'', @provider=N'MSDASQL', @provstr=N'DRIVER={SQL Server};SERVER=VS100;Trusted_Connection=yes;' 
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'VS100',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL GO
如果就管理畫面的角度,就是長這個樣子:

image

這樣就可以解決 SQL Server 2012連結到 SQL Server 2000的連結伺服器的問題囉,大家可以試試看,不過還是希望大家能儘早升級到 SQL Server 2012啦(笑)。

 

image

Tags:

評論 (53) -

cours de theatre
cours de theatre United States
2017/9/30 上午 09:59:05 #

A big thank you for your post.Really looking forward to read more. Want more.

回覆

dic phoenix
dic phoenix United States
2017/10/6 下午 11:09:45 #

Thank you for your blog post.Thanks Again. Really Cool.

回覆

Buy illegal backlinks
Buy illegal backlinks United States
2017/10/9 下午 01:04:56 #

A big thank you for your blog post. Really Cool.

回覆

sklep z kamagra
sklep z kamagra United States
2017/10/9 下午 02:42:46 #

Say, you got a nice article.Really looking forward to read more. Really Great.

回覆

can ho bien vung tau
can ho bien vung tau United States
2017/10/9 下午 04:59:49 #

I value the article.Much thanks again. Really Great.

回覆

solarmovie
solarmovie United States
2017/10/10 下午 06:47:48 #

Very neat post. Will read on...

回覆

Sterling Businesses Ltd
Sterling Businesses Ltd United States
2017/10/10 下午 08:54:28 #

Wow, great article.

回覆

pirater un compte facebook
pirater un compte facebook United States
2017/10/10 下午 11:13:16 #

Thank you ever so for you blog article.Really thank you! Really Cool.

回覆

Buy illegal backlinks
Buy illegal backlinks United States
2017/10/12 下午 07:37:52 #

Thank you for your blog article.Really looking forward to read more. Awesome.

回覆

investigate this site
investigate this site United States
2017/10/14 下午 02:17:55 #

Thanks so much for the post.Much thanks again. Want more.

回覆

dragon city hack game
dragon city hack game United States
2017/10/15 下午 02:19:29 #

I really enjoy the post.Really thank you! Really Cool.

回覆

omega xl review
omega xl review United States
2017/10/15 下午 07:02:15 #

Im thankful for the blog article.Really looking forward to read more. Cool.

回覆

read what he said
read what he said United States
2017/10/17 下午 01:37:08 #

I cannot thank you enough for the blog article.Thanks Again. Really Cool.

回覆

sletrokor
sletrokor United States
2017/10/17 下午 07:10:02 #

Thanks so much for the blog.Much thanks again. Much obliged.

回覆

The western capital
The western capital United States
2017/10/19 上午 04:32:36 #

Really appreciate you sharing this blog.Really looking forward to read more. Really Great.

回覆

porn
porn United States
2017/10/19 上午 06:12:47 #

Major thankies for the blog.Thanks Again. Much obliged.

回覆

this page
this page United States
2017/10/19 下午 04:59:57 #

I appreciate you sharing this blog.Really looking forward to read more. Much obliged.

回覆

Osimi Sea View
Osimi Sea View United States
2017/10/21 上午 02:09:26 #

I am so grateful for your article post.Really looking forward to read more. Really Cool.

回覆

carte grise en ligne
carte grise en ligne United States
2017/10/21 上午 05:49:41 #

Im grateful for the blog post.Much thanks again. Will read on...

回覆

elakekassa
elakekassa United States
2017/10/21 下午 04:14:24 #

Appreciate you sharing, great post.Thanks Again. Awesome.

回覆

Turbotax phone number
Turbotax phone number United States
2017/10/24 上午 08:08:15 #

Really appreciate you sharing this article post.Thanks Again.

回覆

vung tau melody
vung tau melody United States
2017/10/28 上午 08:35:31 #

I think this is a real great blog post.Much thanks again. Cool.

回覆

EZ Battery Reconditioning
EZ Battery Reconditioning United States
2017/10/30 上午 07:29:40 #

I cannot thank you enough for the post.Thanks Again. Will read on...

回覆

pogoda plock
pogoda plock United States
2017/10/30 下午 03:14:57 #

I cannot thank you enough for the article post. Really Cool.

回覆

mamaweeb
mamaweeb United States
2017/11/1 上午 07:43:26 #

Very neat blog post.Really looking forward to read more. Awesome.

回覆

phenocal review
phenocal review United States
2017/11/1 下午 03:05:50 #

I really enjoy the article post.Much thanks again. Awesome.

回覆

phentaslim
phentaslim United States
2017/11/3 下午 02:29:40 #

Hey, thanks for the post.Much thanks again. Keep writing.

回覆

natural remedy spinal stenosis
natural remedy spinal stenosis United States
2017/11/15 下午 12:03:40 #

Thank you ever so for you article post.Really looking forward to read more. Fantastic.

回覆

fashion
fashion United States
2017/11/24 上午 02:13:31 #

Thanks a lot for the blog post.Thanks Again. Want more.

回覆

porno
porno United States
2017/12/1 下午 08:59:58 #

Looking forward to reading more. Great post.Really thank you! Awesome.

回覆

primary trade lines
primary trade lines United States
2017/12/3 上午 09:06:52 #

Thanks for sharing, this is a fantastic article post.Really looking forward to read more. Really Great.

回覆

mobile porno
mobile porno United States
2017/12/5 下午 01:57:13 #

Great post.Thanks Again. Awesome.

回覆

wow, awesome blog post.Much thanks again. Want more.

回覆

tips weight loss
tips weight loss United States
2017/12/17 上午 04:37:03 #

I value the blog article.Much thanks again.

回覆

Say, you got a nice article.Much thanks again. Cool.

回覆

driver canon
driver canon United States
2017/12/23 上午 06:35:37 #

Looking forward to reading more. Great blog post. Keep writing.

回覆

Darwin Horan
Darwin Horan United States
2017/12/23 下午 05:03:53 #

Wow, great blog.Much thanks again. Cool.

回覆

driver hp
driver hp United States
2017/12/25 下午 06:36:44 #

Im obliged for the blog article.Really thank you! Want more.

回覆

رگ
رگ United States
2017/12/26 上午 01:22:30 #

Im grateful for the post.Thanks Again. Awesome.

回覆

Thank you ever so for you post.Much thanks again. Really Great.

回覆

SOCCER HIGHLIGHTS
SOCCER HIGHLIGHTS United States
2017/12/26 下午 06:22:33 #

wow, awesome article.Really thank you! Fantastic.

回覆

canon printer series
canon printer series United States
2017/12/27 下午 06:52:49 #

Major thanks for the blog article. Want more.

回覆

hp drivers
hp drivers United States
2018/1/2 上午 09:32:53 #

Really enjoyed this article.Really looking forward to read more. Keep writing.

回覆

see it here
see it here United States
2018/1/2 下午 05:36:11 #

I value the blog article.Thanks Again. Really Great.

回覆

Im thankful for the article post. Want more.

回覆

printer driver
printer driver United States
2018/1/3 上午 08:55:40 #

A big thank you for your article post. Awesome.

回覆

real money online pokies
real money online pokies United States
2018/1/4 下午 07:34:40 #

wow, awesome blog post. Really Great.

回覆

hp printer driver
hp printer driver United States
2018/1/5 下午 08:16:15 #

Im obliged for the blog post.Really thank you! Will read on...

回覆

FBA
FBA United States
2018/1/6 下午 12:20:37 #

I appreciate you sharing this post. Keep writing.

回覆

web hosting
web hosting United States
2018/1/10 上午 11:47:34 #

Thanks-a-mundo for the article post.Much thanks again. Really Great.

回覆

mx6mKOuN
mx6mKOuN United States
2020/9/17 上午 01:09:22 #

828511 981349Maintain up the  great  piece of function, I read couple of posts  on this  internet web site  and I believe  that your weblog  is  actually  fascinating and  holds   bands of  wonderful   details. 636178

回覆

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List