exec sp_addlinkedserver '别名','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=远程名;UID=用户;PWD=密码;'exec sp_addlinkedsrvlogin @rmtsrvname='别名',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密码'注册完后,再在AAA上:
select * from 别名.库名.dbo.表名
select * from 别名.库名.dbo.表名
'SQLOLEDB',
'Data Source=209.131.196.56;User ID=sa;Password=222222'
).tt.dbo.test
from openrowset('sqloledb','209.131.196.56,12356';'sa';'222222'.DB.dbo.[TABLE])
Function f_GetSQL$( _
ByVal b_srvIP$, _
ByVal b_Port&, _
ByVal b_User$, _
ByVal b_Passowrd$, _
ByVal b_OBject)
f_GetSQL = "select * from openrowset('sqloledb','" _
& b_srvIP & "," & b_Port & "';'" & b_User & "';'" & b_password & "'." _
& b_OBject & ")"
End Function'调用示例
Sub test()
Dim iDb As ADODB.Connection
Dim iRs As New ADODB.Recordset
Set iDb = New ADODB.Connection
iDb.Open "....连接到A服务器的连接字符串"
'查询B服务器的指定数据
Set iRs = iDb.Execute(f_GetSQL("209.131.196.56","12356","sa","222222","DB.dbo.[TABLE]"))
iDb.Close
Set iDb = Nothing
End Sub
create proc p_qry
@b_serverIP varchar(15), --B服务器的IP地址
@b_serverPrt varchar(10), --B服务器的端口
@b_user sysname, --B服务器的登录用户名
@b_password sysname, --B服务器的登录密码
@sql varchar(4000) --要执行的SQL语句或查询的对象,对于sql语句,要用''括起来
as
set nocount on
declare @s varchar(8000)
set @s=replace(@sql,'''','''''')
print('
select *
from openrowset(''sqloledb'','''+@b_serverIP+','+@b_serverPrt
+''';'''+@b_user+''';'''+@b_password+''','+@sql+')')
go--调用
exec p_qry '209.131.196.56','12356','SA','222222','DB.dbo.[TABLE]'
go
select *
from openrowset('sqloledb','209.131.196.56,12356';'sa';'222222',DB.dbo.[TABLE])