SQL跨服务器操作语句

 

--简单的跨服务器查询语句
select * from opendatasource('SQLOLEDB', 'Data Source=192.168.0.1;User ID=sa;Password=123').db_bingle.dbo.users

 

--跨服务器插入数据
IF  EXISTS (select * from sys.servers WHERE name = 'ufserver')
Exec sp_droplinkedsrvlogin 'ufserver',Null 
exec sp_dropserver @server='ufserver'
go
EXEC sp_addlinkedserver 
    @server = 'ufserver',    --临时名称
    @srvproduct='',
    @provider = 'SQLOLEDB',
    @datasrc='192.168.0.1'        
EXEC sp_addlinkedsrvlogin 
    'ufserver',        
    'false',
    null,
    'sa',                    --登录名
    '123'                    --登录密码    
go
EXEC master.dbo.sp_serveroption @server=N'ufserver', @optname=N'collation compatible', @optvalue=N'true'
go
EXEC master.dbo.sp_serveroption @server=N'ufserver', @optname=N'data access', @optvalue=N'true'
go
EXEC master.dbo.sp_serveroption @server=N'ufserver', @optname=N'rpc', @optvalue=N'true'
go
EXEC master.dbo.sp_serveroption @server=N'ufserver', @optname=N'rpc out', @optvalue=N'true'
go
EXEC master.dbo.sp_serveroption @server=N'ufserver', @optname=N'use remote collation', @optvalue=N'true'
go
--插入语句
INSERT INTO ufserver.db_test.dbo.users(ID, Name, AddTime)
SELECT ID, Name, AddTime 
FROM dbo.users
go