SQL Server的鏈接服務器技術小結

2024-08-31 00:50:46

字體：大中小

來源：轉載

供稿：網友

一、使用 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...表名