日期:2014-05-17 浏览次数:20632 次
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[yusp_tongbu]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[yusp_tongbu]
GO
create procedure yusp_tongbu
as
begin
declare @from_server varchar(256)--来源服务器
declare @from_dbname varchar(256)--来源数据库
declare @from_user varchar(256)--来源用户名
declare @from_pwd varchar(256)--来源密码
declare @sql varchar(max)--SQL语句
set @from_server = '[huang-pc]'
set @from_dbname = 'tempdb'
set @from_user = 'sa'
set @from_pwd = 'xxxx'
set @sql = 'select top 5 * from [10.20.30.202\prodsqlserver].tempdb.dbo.csdn'
EXEC sp_addlinkedserver @server='10.20.30.202\prodsqlserver',@srvproduct='',@provider='SQLOLEDB',@datasrc=@from_server
EXEC sp_addlinkedsrvlogin '10.20.30.202\prodsqlserver','false',NULL, @from_user,@from_pwd
exec (@SQL)
Exec sp_droplinkedsrvlogin [10.20.30.202\prodsqlserver],Null
Exec sp_dropserver [10.20.30.202\prodsqlserver]
end
go
exec yusp_tongbu