- 使用的存储过程简介
- 1、sp_addlinkedserver --- 为SQL SERVER 添加一个Link Server
- 2、sp_addlinkedsrvlogin --- 为Link Server 添加一个 Login
- 3、sp_droplinkedsrvlogin --- 删除Link Server的Login
- 4、sp_dropserver --- 删除Link Server
- 详细请参考 SQL SERVER Book Online
- 使用的命令
- 1、use 切换到一个指定的数据库
- 2、OPENQUERY 从一个现有的Link Server 执行一个查询
- Dim cn As ADODB.Connection
- Dim strsql As String
- Dim rs As ADODB.Recordset
- '打开连接
- Set cn = New ADODB.Connection
- cn.Open "连接"
- strsql = "use master"
- cn.Execute strsql
- '添加一个LinkServer
- strsql = "exec sp_addlinkedserver"
- strsql = strsql & " @server = 'AccessDataSrv',"
- strsql = strsql & " @provider = 'Microsoft.Jet.OLEDB.4.0',"
- strsql = strsql & " @srvproduct = 'OLE DB Provider for Jet',"
- strsql = strsql & " @datasrc = 'C:\\Test.mdb'"
- cn.Execute strsql
- '添加一个LinkServer Login 如果你的数据库有密码和用户认证的话
- 'strsql = "EXEC sp_addlinkedsrvlogin 'AccessDataSrv', 'false', '当前用户', '用户', '密码'"
- strsql = "exec sp_addlinkedsrvlogin 'AccessDataSrv', 'false'"
- cn.Execute strsql
- strsql = "use tt"
- strsql = "insert into testlinsrv(id,field2)"
- strsql = strsql & " select * from OPENQUERY(AccessDataSrv,'select a1,a2 from testlinksrv')"
- cn.Execute strsql
- strsql = "use master"
- cn.Execute strsql
- '删除linksrv的login
- strsql = "exec sp_droplinkedsrvlogin 'AccessDataSrv', null"
- cn.Execute strsql
- '删除linksrv
- strsql = "exec sp_dropserver 'AccessDataSrv'"
- cn.Execute strsql
- '返回数据
- strsql = "select * from testlinsrv"
- //该片段来自于http://www.codesnippet.cn/detail/010420132657.html
来源: http://www.codesnippet.cn/detail/010420132657.html