create proc sp_InnerImport
(
@serverIP nvarchar(32)
)
as
begin
--一行一行执行下面的存储过程语句就是对的,可一起就是错的
set nocount on
if exists(select 1
from master.dbo.sysservers
where srvname='RemoteServer'
)
begin
exec sp_dropserver @server='RemoteServer',@droplogins='droplogins'
end
exec sp_addlinkedserver @server='RemoteServer',
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='127.0.0.1' exec sp_addlinkedsrvlogin @rmtsrvname='RemoteServer',
@useself='false',
@locallogin=null,
@rmtuser='sa',
@rmtpassword='zrx'
--总是在下面报错
--select * from RemoteServer.Test.dbo.abc
select *
FROM openquery(RemoteServer, 'SELECT * FROM Test.dbo.User ') exec sp_dropserver @server='RemoteServer',@droplogins='droplogins'
end
go
(
@serverIP nvarchar(32)
)
as
begin
--一行一行执行下面的存储过程语句就是对的,可一起就是错的
set nocount on
if exists(select 1
from master.dbo.sysservers
where srvname='RemoteServer'
)
begin
exec sp_dropserver @server='RemoteServer',@droplogins='droplogins'
end
exec sp_addlinkedserver @server='RemoteServer',
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='127.0.0.1' exec sp_addlinkedsrvlogin @rmtsrvname='RemoteServer',
@useself='false',
@locallogin=null,
@rmtuser='sa',
@rmtpassword='zrx'
--总是在下面报错
--select * from RemoteServer.Test.dbo.abc
select *
FROM openquery(RemoteServer, 'SELECT * FROM Test.dbo.User ') exec sp_dropserver @server='RemoteServer',@droplogins='droplogins'
end
go
---try:
create proc sp_InnerImport
(
@serverIP nvarchar(32)
)
as
begin
--一行一行执行下面的存储过程语句就是对的,可一起就是错的
set nocount on
if exists(select 1
from master.dbo.sysservers
where srvname='RemoteServer'
)
begin
exec sp_dropserver @server='RemoteServer',@droplogins='droplogins'
end
exec sp_addlinkedserver @server='RemoteServer',
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='127.0.0.1' exec sp_addlinkedsrvlogin @rmtsrvname='RemoteServer',
@useself='false',
@locallogin=null,
@rmtuser='sa',
@rmtpassword='zrx'
--总是在下面报错
--select * from RemoteServer.Test.dbo.abc
waitfor delay '00:00:05'
select *
FROM openquery(RemoteServer, 'SELECT * FROM Test.dbo.User ') exec sp_dropserver @server='RemoteServer',@droplogins='droplogins'
end
go
(
@serverIP nvarchar(32)
)
as
begin
--一行一行执行下面的存储过程语句就是对的,可一起就是错的
set nocount on
if exists(select 1
from master.dbo.sysservers
where srvname='RemoteServer'
)
begin
exec sp_dropserver @server='RemoteServer',@droplogins='droplogins'
end
exec sp_addlinkedserver @server='RemoteServer',
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='127.0.0.1' exec sp_addlinkedsrvlogin @rmtsrvname='RemoteServer',
@useself='false',
@locallogin=null,
@rmtuser='sa',
@rmtpassword='zrx'
--总是在下面报错 ,
--+go,批处理结束
go --select * from RemoteServer.Test.dbo.abc
select *
FROM openquery(RemoteServer, 'SELECT * FROM Test.dbo.User ') exec sp_dropserver @server='RemoteServer',@droplogins='droplogins'
end
go
在 sysservers 中未能找到服务器 'RemoteServer'。请执行 sp_addlinkedserver 以将服务器添加到 sysservers。
(
@serverIP nvarchar(32)
)
as
begin
--一行一行执行下面的存储过程语句就是对的,可一起就是错的
set nocount on
if exists(select 1
from master.dbo.sysservers
where srvname='.'
)
begin
exec sp_dropserver @server='.',@droplogins='droplogins'
end
exec sp_addlinkedserver @server='RemoteServer',
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='127.0.0.1' exec sp_addlinkedsrvlogin @rmtsrvname='.',
@useself='false',
@locallogin=null,
@rmtuser='sa',
@rmtpassword='zrx'
--总是在下面报错
--select * from RemoteServer.Test.dbo.abc
waitfor delay '00:00:05'
exec('select *
FROM openquery(RemoteServer,''SELECT * FROM Test.dbo.User '')') exec sp_dropserver @server='.',@droplogins='droplogins'
end
go
drop proc sp_InnerImport
exec('select *
FROM openquery(RemoteServer,''SELECT * FROM Test.dbo.User '')')
---这样也不对呀?
那是你的链接服务器没有先存在,你就select当然会报错的呀
EXEC sp_addlinkedserver 'mbo-srv-05', N'SQL Server'
exec sp_addlinkedsrvlogin 'mbo-srv-05','false',null,'sa','Password01!';
exec ( 'use dmsdb select * from menu' )
at
[mbo-srv-05]
(
@serverIP nvarchar(32)
)
as
begin
--一行一行执行下面的存储过程语句就是对的,可一起就是错的
set nocount on
if exists(select 1
from master.dbo.sysservers
where srvname='.'
)
begin
exec sp_dropserver @server='.',@droplogins='droplogins'
end
exec sp_addlinkedserver @server='RemoteServer',
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='127.0.0.1' exec sp_addlinkedsrvlogin @rmtsrvname='.',
@useself='false',
@locallogin=null,
@rmtuser='sa',
@rmtpassword='zrx'
--总是在下面报错
--select * from RemoteServer.Test.dbo.abc
waitfor delay '00:00:05'
exec('select *
FROM openquery(RemoteServer,''SELECT * FROM Test.dbo.User '')') exec sp_dropserver @server='.',@droplogins='droplogins'
end
go
这个可以反复创建了, 但是,我在使用的时候总是显示正在执行批查询, 比刚才多花很长时间才能够出结果. 这又是为什么呢?刚才只要能够创建成功就能够很快出结果,因为waitfor delay '00:00:05' 吗? 为什么要有这个语句?
waitfor delay '00:00:02'
如果还慢但是结果 正常时改
waitfor delay '00:00:01'
drop proc sp_InnerImport
gocreate proc sp_InnerImport
(
@serverIP nvarchar(32)
)
as
begin
set nocount on
if exists(select 1
from master.dbo.sysservers
where srvname='RemoteServer'
)
begin
exec sp_dropserver @server='RemoteServer',@droplogins='droplogins'
end
exec sp_addlinkedserver @server='RemoteServer',
@srvproduct='',
@provider='SQLOLEDB',
@datasrc=@serverIP exec sp_addlinkedsrvlogin @rmtsrvname='RemoteServer',
@useself='false',
@locallogin=null,
@rmtuser='sa',
@rmtpassword='zrx' --select * from RemoteServer.Test.dbo.abc
--select * FROM openquery(RemoteServer, 'SELECT * FROM Test.dbo.abc ') --waitfor delay '00:00:05'
exec('select * FROM openquery(RemoteServer,''SELECT * FROM Test.dbo.abc '')') exec sp_dropserver @server='RemoteServer',@droplogins='droplogins'
end
go
exec('select * FROM openquery(RemoteServer,''SELECT * FROM Test.dbo.abc '')')
select * from master.dbo.sysservers