使用的存储过程简介1、sp_addlinkedserver --- 为SQL SERVER 添加一个Link Server2、sp_addlinkedsrvlogin --- 为Link Server 添加一个 Login3、sp_droplinkedsrvlogin --- 删除Link Server的Login4、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"
