SQL Server的連結的伺服器技術小結 選擇自 netcoder 的 Blog

 

一、使用 Microsoft OLE DB Provider For ODBC 連結MySQL
安裝MySQL的ODBC驅動MyODBC
1、為MySQL建立一個ODBC系統資料來源，例如：選擇資料庫為test ,資料來源名稱為

myDSN

2、建立連結資料庫
EXEC sp_addlinkedserver  @server = 'MySQLTest', @srvproduct='MySQL',

@provider = 'MSDASQL', @datasrc = 'myDSN'
GO
EXEC sp_addlinkedsrvlogin 

@rmtsrvname='MySqlTest',@useself='false',@locallogin='sa',@rmtuser='mys

ql的使用者名稱',@rmtpassword='mysql的密碼'

3、查詢資料

SELECT * FROM OPENQUERY (MySQLTest ,'select * from 表' )

下面這個不行：
SELECT * FROM OPENQUERY (MySQLTest ,'表' )

注意：不能直接用select * from 連結的伺服器名.資料庫名.使用者名稱.表(或視圖)

四部分名稱查詢資料，可能是個Bug.

二、使用 Microsoft OLE DB Provider For ORACLE 連結ORACLE

1、建立連結資料庫
sp_addlinkedserver '別名', 'Oracle', 'MSDAORA', '服務名'
GO
EXEC sp_addlinkedsrvlogin  @rmtsrvname='別名

',@useself='false',@locallogin='sa',@rmtuser='oracle使用者名稱

',@rmtpassword='密碼'

2、查詢資料

SELECT * FROM 別名..使用者名稱.表(視圖)

注意：四部分名稱全部用大寫

3、執行預存程序

使用OPENQUERY:
SELECT *
FROM OPENQUERY(別名, 'exec 使用者名稱.預存程序名')

三、設定連結的伺服器以訪問格式化文字檔

用於 Jet 的 Microsoft OLE DB 提供者可用於訪問並查詢文字檔。

若要直接建立訪問文字檔的連結的伺服器而不將檔案連結為 Access .mdb 檔案中

的表，請執行 sp_addlinkedserver，如下例所示。
提供者是 Microsoft.Jet.OLEDB.4.0，提供者字串為"Text"。資料來源是包

含文字檔的目錄的完整路徑名稱。schema.ini 檔案（描述文字檔的結構）必

須與此文字檔存在於相同的目錄中。有關建立 schema.ini 檔案的更多資訊，

請參見 Jet 資料庫引擎文檔。

--Create a linked server.
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
    'Microsoft.Jet.OLEDB.4.0',
    'c:/data/distqry',
    NULL,
    'Text'
GO

--Set up login mappings.
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
GO

--List the tables in the linked server.
EXEC sp_tables_ex txtsrv
GO

--Query one of the tables: file1#txt
--using a 4-part name.
SELECT *
FROM txtsrv...[file1#txt]

四、連結SQL Server伺服器:

1、使用 ODBC 的 Microsoft OLE DB 提供者

EXEC sp_addlinkedserver '別名','','MSDASQL',NULL,NULL,'DRIVER={SQL

Server};SERVER=遠程名;UID=使用者;PWD=密碼;'
如果加上參數@catalog，可以指定資料庫
exec sp_addlinkedsrvlogin  @rmtsrvname='別名

',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密碼'

2、使用SQL Server 的 Microsoft OLE DB 提供者

exec sp_addlinkedserver @server='別名

',@provider='sqloledb',@srvproduct='',@datasrc='遠程伺服器名'
exec sp_addlinkedsrvlogin 

@rmtsrvname='wzb',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtp

assword='密碼'

然後你就可以如下：
select * from 別名.庫名.dbo.表名
insert 庫名.dbo.表名 select * from 別名.庫名.dbo.表名
select * into 庫名.dbo.新表名 from 別名.庫名.dbo.表名
go

例1、

此樣本在 SQL Server 的執行個體上建立一台名為 S1_instance1 的連結的伺服器，
該伺服器使用 SQL Server 的 Microsoft OLE DB 提供者。

EXEC    sp_addlinkedserver    @server='S1_instance1', @srvproduct='',
                                @provider='SQLOLEDB',

@datasrc='S1/instance1'

例2、

--建立連結的伺服器
EXEC sp_addlinkedserver 'xiaoming','','MSDASQL',NULL,NULL,'DRIVER={SQL

Server};SERVER=192.168.0.1;UID=sa;PWD=123;'
--建立連結的伺服器登入映射
exec sp_addlinkedsrvlogin 

@rmtsrvname='xiaoming',@useself='false',@locallogin='sa',@rmtuser='sa',

@rmtpassword='123'
go
--查詢資料
select * from xiaoming.schooladmin.dbo.agent 

--刪除連結的伺服器登入映射和連結的伺服器：
exec sp_droplinkedsrvlogin 'xiaoming' ,'sa'
exec sp_dropserver  'xiaoming'

注意事項：

SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF }
所以不能通過串連伺服器設定此屬性
into 也存在這樣的問題
select  * into   xiaoming.northwind.dbo.tt from

xiaoming.northwind.dbo.tt

五、設定連結的伺服器以訪問Access資料庫

使用用於 Jet 的 Microsoft OLE DB 提供者
此樣本建立一台名為 test的連結的伺服器。

說明  本樣本假設已經安裝 Microsoft Access 和樣本 Northwind 資料庫，且

Northwind 資料庫駐留在 C:/。

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
   @server = 'test',
   @provider = 'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'C:/Northwind.mdb'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
   'test',
   'OLE DB Provider for Jet',
   'Microsoft.Jet.OLEDB.4.0',
   'C:/Northwind.mdb'
GO
使用
select * from test...表名

六、串連SYBASE
--首先,你要在SQL伺服器上裝上訪問sybase的用戶端

--建立連結的伺服器
exec sp_addlinkedserver 'Sybase1', ' ', 'MSDASQL', NULL, NULL
 ,'Driver={Sybase System

11};Database=hisdb;Srvr=10.211.135.12;UID=sa;PWD=1111;'
使用：
select * from Sybase1.hisdb.dbo.table1

方法二
使用ODBC
SQL Server到SYBASE串連伺服器的實現
 
作者:  CCBZZP

 
本文的測試環境為:
作業系統:  WINDOWS2000 SERVER (繁體系統)
安裝資料庫: SQLSERVER2000(英文版)和SYBASE8.0用戶端(英文版)
 

具體實現步驟:
1.要求pc機上安裝SYBASE8.0用戶端軟體和sqlserver2000軟體。
2.配置windows的ODBC資料來源：
開始菜單—》程式集—》系統管理工具—》資料資料來源（ODBC）—》進入配置用

戶DSN或者系統DSN均可以：添加—》選擇ADAPTIVE SERVER ANYWHERE8.0—》自定

義資料來源名稱（隨意如: SYBASETEST）—》資料庫名稱（必選！）—》OK完成。

3. 選擇剛才配置的資料來源名稱, 再選擇 配置, 跳出SYBASETEST MESSAGES:

The data source is not connected.  Connecting to the data source will

provide useful information during configuration.  Would you like to

connect to the data source?

選擇YES(OK或確認)即可

進入CONNECT TO SYBASE  DATABASE畫面:

USER ID: 輸入SYBASE DATABASE的使用者

PASSWORD: 輸入SYBASE DATABASE的使用者的密碼

CONNECTION  MODE: 可以選擇預設的SHARE模式

選擇OK(確認)即可!

配置和測試ODBC完成!

4.配置sqlserver2000中的串連伺服器：
企業管理器—》安全性—》串連伺服器—》右鍵建立串連伺服器—》定義串連名

稱； 選其他資料來源； 指定程式名稱為：SYBASE ADAPTIVE SERVER ANYWHERE

PROVIDER8.0; 產品名稱可不填； 資料來源指定剛才ODBC中定義好的資料來源名稱；

提供者字串按以下格式填寫：User ID=username;Password=userpasswd(或者

按如下格式：UID=username;PWD=userpasswd)，這裡的使用者名稱和密碼對應所要連

接的SYBASE資料庫中的使用者名稱和密碼 —》 安全性標籤頁裡：設定用此安全上下

文進行，並錄入SYBASE的資料庫使用者名稱和密碼—》伺服器選項標籤頁可預設—》

確定。
5.準備工作全部完成！在sqlserver企業管理器—》安全性—》串連伺服器開啟剛

建好的串連伺服器—》點擊表，即可在右邊視窗看到該SYBASE資料庫使用者擁有的

的所有表名，但在這裡還並不能查看錶的記錄，這個需要在sqserver的查詢分析

器中用具體sql實現！訪問表時，使用格式為： [串連伺服器名]..[SYBASE使用者

].[表名]。更詳細具體的使用這裡不再贅述。