原帖:http://bbs.csdn.net/topics/390304883IF 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.INVMBExec sp_droplinkedsrvlogin DBVIP,Null
Exec sp_dropserver DBVIP
end
go
exec yusp_tongbu
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.INVMBExec sp_droplinkedsrvlogin DBVIP,Null
Exec sp_dropserver DBVIP
end
go
exec yusp_tongbu
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
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 = '[huang-pc]'
set @from_dbname = 'tempdb'
set @from_user = 'sa'
set @from_pwd = 'xxxx'
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
--go--如果这里加上GO,在非存储过程下是可以执行的
select top 5 * from [10.20.30.202\prodsqlserver].tempdb.dbo.csdn
Exec sp_droplinkedsrvlogin [10.20.30.202\prodsqlserver],Null
Exec sp_dropserver [10.20.30.202\prodsqlserver]
end
go
exec yusp_tongbu
据我所知好像存储过程中的语句是从as开发到go结束,也就是说第一个go之后的内容不属于存储过程了.临时性查询建议使用 即席分布式查询使用 OPENROWSET 和 OPENDATASOURCE 函数连接到使用 OLE DB 的远程数据源。
示例如下:EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=数据库名称或IP;User ID=用户名;Password=密码'
).Northwind.dbo.Categories
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如果非要这种形式,建议按以上执行,应该不会出现找不到服务器的错了.