sp_addlinkedserver使用方法--实现数据间的链接

推荐Exec sp_droplinkedsrvlogin DBVIP,Null
Exec sp_dropserver DBVIP

EXEC sp_addlinkedserver
@server='DBVIP',--被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc="/Server2" --要访问的服务器


EXEC sp_addlinkedsrvlogin
'DBVIP', --被访问的服务器别名
'false',
NULL,
'sa', --帐号
'thankyoubobby' --密码


Select * from DBVIP.pubs.dbo.orders


/ORACLE
Exec sp_droplinkedsrvlogin demo,Null
Exec sp_dropserver demo
go

EXEC sp_addlinkedserver
@server ='demo',
@srvproduct='Oracle',
@provider='MSDAORA',
@datasrc="/ServiceName"


EXEC sp_addlinkedsrvlogin
'demo',
'false',
NULL,
'userid',
'password'
go

SELECT * FROM OPENQUERY(demo ,'select * from tbdemo' )

UPDATE OPENQUERY (demo, 'SELECT id FROM tbdemo WHERE id = 101')
SET name = 'hello';

INSERT OPENQUERY (demo, 'SELECT id FROM tbdemo')
S ('hello');

DELETE OPENQUERY (demo, 'SELECT id FROM tbdemo WHERE name = ''hello''');