我想把A服务器里的数据直接上传到B服务器,两台服务器都是安装sql2005,我第一步在B创建表,如下语句:
declare @linkip varchar(800),@dbcode int----连接远端数据库
set @linkip='192.168.18.143'
Exec sp_droplinkedsrvlogin linkremote,Null
Exec sp_dropserver linkremote
EXEC sp_addlinkedserver
@server='linkremote',--被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc=@linkip --要访问的服务器
EXEC sp_addlinkedsrvlogin
'linkremote', --被访问的服务器别名
'false',
NULL,
'sa', --帐号
'123' --密码
EXEC master.dbo.sp_serveroption @server=N'linkremote', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'linkremote', @optname=N'rpc out', @optvalue=N'true'
declare @sqltext varchar(8000),@dblocal varchar(8000)
set @dblocal='[cy302ncs]'
set @sqltext=' declare @ErrorMessage varchar(8000),@ErrorSeverity int, @ErrorState int,@ErrorNumber int,@OperType int
BEGIN TRANSACTION database_upload
begin try
create table [cy302ncs].dbo.t1(id int,code varchar(15),listdate datetime,sign int)
end try
begin catch
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),@ErrorNumber=ERROR_NUMBER()
if @ErrorSeverity>18 or @ErrorSeverity<0
set @ErrorSeverity=16
if @ErrorState>127 or @ErrorState<1
set @ErrorState=1
begin try
ROLLBACK TRANSACTION database_upload
end try
begin catch
end catch
raiserror (@ErrorMessage, @ErrorSeverity,@ErrorState)
--return @ErrorNumber
end catch
begin try
COMMIT TRANSACTION database_upload
end try
begin catch
end catch 'execute(@sqltext) at linkremote这样做没问题,一去到B服务器查看,有表T1存在,接着我要把A服务器本地表的数据上传到B服务器t1时,我这样写:
declare @linkip varchar(800),@dbcode int----连接远端数据库
set @linkip='192.168.18.143'
Exec sp_droplinkedsrvlogin linkremote,Null
Exec sp_dropserver linkremote
EXEC sp_addlinkedserver
@server='linkremote',--被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc=@linkip --要访问的服务器
EXEC sp_addlinkedsrvlogin
'linkremote', --被访问的服务器别名
'false',
NULL,
'system', --帐号
'cysystem830' --密码
EXEC master.dbo.sp_serveroption @server=N'linkremote', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'linkremote', @optname=N'rpc out', @optvalue=N'true'
--drop table '+@dblocal+'.dbo.t1
declare @sqltext varchar(8000),@dblocal varchar(8000)
set @dblocal='[cy302ncs]'
set @sqltext=' declare @ErrorMessage varchar(8000),@ErrorSeverity int, @ErrorState int,@ErrorNumber int,@OperType int
BEGIN TRANSACTION database_upload
begin try
create table [cy302ncs].dbo.t1(id int,code varchar(15),listdate datetime,sign int)
end try
begin catch
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),@ErrorNumber=ERROR_NUMBER()
if @ErrorSeverity>18 or @ErrorSeverity<0
set @ErrorSeverity=16
if @ErrorState>127 or @ErrorState<1
set @ErrorState=1
begin try
ROLLBACK TRANSACTION database_upload
end try
begin catch
end catch
raiserror (@ErrorMessage, @ErrorSeverity,@ErrorState)
--return @ErrorNumber
end catch
begin try
COMMIT TRANSACTION database_upload
end try
begin catch
end catch 'execute(@sqltext) at linkremotedeclare @ErrorMessage varchar(8000),@ErrorSeverity int, @ErrorState int,@ErrorNumber int,@OperType int
set xact_abort on
BEGIN TRANSACTION abc
begin try
insert openquery(linkremote, 'SELECT id ,code,listdate,sign FROM cy302ncs.dbo.t1')
select id ,code,listdate,sign from t1 end try
begin catch
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),@ErrorNumber=ERROR_NUMBER()
if @ErrorSeverity>18 or @ErrorSeverity<0
set @ErrorSeverity=16
if @ErrorState>127 or @ErrorState<1
set @ErrorState=1
begin try
ROLLBACK TRANSACTION abc
end try
begin catch
end catch
raiserror (@ErrorMessage, @ErrorSeverity,@ErrorState)
--return @ErrorNumber
end catch
begin try
COMMIT TRANSACTION abc
end try
begin catch
end catch运行出错:
链接服务器"linkremote"的 OLE DB 访问接口 "SQLNCLI" 返回了消息 "无法完成延迟准备。"。
消息 208,级别 16,状态 1,第 1 行
对象名 'cy302ncs.dbo.t1' 无效。其实我已经写了创建表t1了,为什么从本地表的数据写入B服务器的t1时说 'cy302ncs.dbo.t1' 无效
declare @linkip varchar(800),@dbcode int----连接远端数据库
set @linkip='192.168.18.143'
Exec sp_droplinkedsrvlogin linkremote,Null
Exec sp_dropserver linkremote
EXEC sp_addlinkedserver
@server='linkremote',--被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc=@linkip --要访问的服务器
EXEC sp_addlinkedsrvlogin
'linkremote', --被访问的服务器别名
'false',
NULL,
'sa', --帐号
'123' --密码
EXEC master.dbo.sp_serveroption @server=N'linkremote', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'linkremote', @optname=N'rpc out', @optvalue=N'true'
declare @sqltext varchar(8000),@dblocal varchar(8000)
set @dblocal='[cy302ncs]'
set @sqltext=' declare @ErrorMessage varchar(8000),@ErrorSeverity int, @ErrorState int,@ErrorNumber int,@OperType int
BEGIN TRANSACTION database_upload
begin try
create table [cy302ncs].dbo.t1(id int,code varchar(15),listdate datetime,sign int)
end try
begin catch
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),@ErrorNumber=ERROR_NUMBER()
if @ErrorSeverity>18 or @ErrorSeverity<0
set @ErrorSeverity=16
if @ErrorState>127 or @ErrorState<1
set @ErrorState=1
begin try
ROLLBACK TRANSACTION database_upload
end try
begin catch
end catch
raiserror (@ErrorMessage, @ErrorSeverity,@ErrorState)
--return @ErrorNumber
end catch
begin try
COMMIT TRANSACTION database_upload
end try
begin catch
end catch 'execute(@sqltext) at linkremote这样做没问题,一去到B服务器查看,有表T1存在,接着我要把A服务器本地表的数据上传到B服务器t1时,我这样写:
declare @linkip varchar(800),@dbcode int----连接远端数据库
set @linkip='192.168.18.143'
Exec sp_droplinkedsrvlogin linkremote,Null
Exec sp_dropserver linkremote
EXEC sp_addlinkedserver
@server='linkremote',--被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc=@linkip --要访问的服务器
EXEC sp_addlinkedsrvlogin
'linkremote', --被访问的服务器别名
'false',
NULL,
'system', --帐号
'cysystem830' --密码
EXEC master.dbo.sp_serveroption @server=N'linkremote', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'linkremote', @optname=N'rpc out', @optvalue=N'true'
--drop table '+@dblocal+'.dbo.t1
declare @sqltext varchar(8000),@dblocal varchar(8000)
set @dblocal='[cy302ncs]'
set @sqltext=' declare @ErrorMessage varchar(8000),@ErrorSeverity int, @ErrorState int,@ErrorNumber int,@OperType int
BEGIN TRANSACTION database_upload
begin try
create table [cy302ncs].dbo.t1(id int,code varchar(15),listdate datetime,sign int)
end try
begin catch
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),@ErrorNumber=ERROR_NUMBER()
if @ErrorSeverity>18 or @ErrorSeverity<0
set @ErrorSeverity=16
if @ErrorState>127 or @ErrorState<1
set @ErrorState=1
begin try
ROLLBACK TRANSACTION database_upload
end try
begin catch
end catch
raiserror (@ErrorMessage, @ErrorSeverity,@ErrorState)
--return @ErrorNumber
end catch
begin try
COMMIT TRANSACTION database_upload
end try
begin catch
end catch 'execute(@sqltext) at linkremotedeclare @ErrorMessage varchar(8000),@ErrorSeverity int, @ErrorState int,@ErrorNumber int,@OperType int
set xact_abort on
BEGIN TRANSACTION abc
begin try
insert openquery(linkremote, 'SELECT id ,code,listdate,sign FROM cy302ncs.dbo.t1')
select id ,code,listdate,sign from t1 end try
begin catch
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),@ErrorNumber=ERROR_NUMBER()
if @ErrorSeverity>18 or @ErrorSeverity<0
set @ErrorSeverity=16
if @ErrorState>127 or @ErrorState<1
set @ErrorState=1
begin try
ROLLBACK TRANSACTION abc
end try
begin catch
end catch
raiserror (@ErrorMessage, @ErrorSeverity,@ErrorState)
--return @ErrorNumber
end catch
begin try
COMMIT TRANSACTION abc
end try
begin catch
end catch运行出错:
链接服务器"linkremote"的 OLE DB 访问接口 "SQLNCLI" 返回了消息 "无法完成延迟准备。"。
消息 208,级别 16,状态 1,第 1 行
对象名 'cy302ncs.dbo.t1' 无效。其实我已经写了创建表t1了,为什么从本地表的数据写入B服务器的t1时说 'cy302ncs.dbo.t1' 无效
exec p_t1
exec p_t2是不是这样来做?
直接用链接服务器,然后 insert ... select ...
不就行了.
不同服务器数据库之间的数据操作--创建链接服务器
exec sp_addlinkedserver 'ITSV' , '' , 'SQLOLEDB' , '远程服务器名或ip地址'
exec sp_addlinkedsrvlogin 'ITSV' , 'false' , null , '用户名' , '密码' --查询示例
select * from ITSV.数据库名.dbo.表名 --导入示例
select * into 表 from ITSV.数据库名.dbo.表名 --以后不再使用时删除链接服务器
exec sp_dropserver 'ITSV' , 'droplogins' --连接远程/局域网数据(openrowset/openquery/opendatasource)
--1、openrowset --查询示例
select * from openrowset('SQLOLEDB' , 'sql服务器名' ; '用户名' ; '密码' , 数据库名.dbo.表名) --生成本地表
select * into 表 from openrowset('SQLOLEDB' , 'sql服务器名' ; '用户名' ; '密码' , 数据库名.dbo.表名) --把本地表导入远程表
insert openrowset( 'SQLOLEDB' , 'sql服务器名' ; '用户名' ; '密码' , 数据库名.dbo.表名)
select *from 本地表 --更新本地表
update b
set b.列A=a.列A
from openrowset('SQLOLEDB' , 'sql服务器名' ; '用户名' ; '密码' , 数据库名.dbo.表名) as a inner join 本地表 b
on a.column1 = b.column1 --openquery用法需要创建一个连接 --首先创建一个连接创建链接服务器
exec sp_addlinkedserver 'ITSV' , '' , 'SQLOLEDB' , '远程服务器名或ip地址'
--查询
select *
FROM openquery(ITSV , 'SELECT * FROM 数据库.dbo.表名')
--把本地表导入远程表
insert openquery(ITSV , 'SELECT * FROM 数据库.dbo.表名')
select * from 本地表
--更新本地表
update b
set b.列B=a.列B
FROM openquery(ITSV , 'SELECT * FROM 数据库.dbo.表名') as a
inner join 本地表 b on a.列A=b.列A --3、opendatasource/openrowset
SELECT *
FROM opendatasource('SQLOLEDB' , 'Data Source=ip/ServerName ; User ID=登陆名 ; Password=密码').test.dbo.roy_ta
--把本地表导入远程表
insert opendatasource('SQLOLEDB' , 'Data Source=ip/ServerName ; User ID=登陆名 ; Password=密码').数据库.dbo.表名
select * from 本地表
declare @linkip varchar(800),@dbcode int----连接远端数据库
set @linkip='192.168.18.143'
Exec sp_droplinkedsrvlogin linkremote,Null
Exec sp_dropserver linkremote
EXEC sp_addlinkedserver
@server='linkremote',--被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc=@linkip --要访问的服务器
EXEC sp_addlinkedsrvlogin
'linkremote', --被访问的服务器别名
'false',
NULL,
'sa', --帐号
'123' --密码
EXEC master.dbo.sp_serveroption @server=N'linkremote', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'linkremote', @optname=N'rpc out', @optvalue=N'true'
declare @sqltext varchar(8000),@dblocal varchar(8000)
set @dblocal='[cy302ncs]'
set @sqltext=' declare @ErrorMessage varchar(8000),@ErrorSeverity int, @ErrorState int,@ErrorNumber int,@OperType int
BEGIN TRANSACTION database_upload
begin try
create table [cy302ncs].dbo.t1(id int,code varchar(15),listdate datetime,sign int)
end try
begin catch
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),@ErrorNumber=ERROR_NUMBER()
if @ErrorSeverity>18 or @ErrorSeverity<0
set @ErrorSeverity=16
if @ErrorState>127 or @ErrorState<1
set @ErrorState=1
begin try
ROLLBACK TRANSACTION database_upload
end try
begin catch
end catch
raiserror (@ErrorMessage, @ErrorSeverity,@ErrorState)
--return @ErrorNumber
end catch
begin try
COMMIT TRANSACTION database_upload
end try
begin catch
end catch 'execute(@sqltext) at linkremote
goinsert into linkremote.[cy302ncs].dbo.t1 select * from t1