最后60分补充,上1个100分的问题.
原帖:http://bbs.csdn.net/topics/390304883

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)--来源密码

set @from_server = 'DBSERVER'
set @from_dbname = 'MYDB'
set @from_user = 'sa'
set @from_pwd = 'pwd'
EXEC sp_addlinkedserver @server='DBVIP',@srvproduct='',@provider='SQLOLEDB',@datasrc=@from_server
EXEC sp_addlinkedsrvlogin 'DBVIP','false',NULL, @from_user,@from_pwd
--go--如果这里加上GO,在非存储过程下是可以执行的
select top 5 * from DBVIP.REANSON.dbo.INVMB

Exec sp_droplinkedsrvlogin DBVIP,Null
Exec sp_dropserver DBVIP
end
go
exec yusp_tongbu
[解决办法]

引用:

有什么问题啊

go是批处理结束标志 存储过程中不能使用
[解决办法]

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

如果非要这种形式,建议按以上执行,应该不会出现找不到服务器的错了.