高分求教老问题：两台SQL服务器之间的数据复制

[mrzhou]

有两个SQL数据表，一个有所有的记录，叫表A，在服务器www.a.com上，现在又有另一个服务器(局域网内）上的表，叫表B，是这个表的子集，只要保存表A中的最近三个月的资料即可。怎么样让这两个表保持最新？
表A的只有一般权限，表B所在服务器有所有权限。

[zjcxc]

--参考:
--即时同步两个表的实例:

--测试环境:SQL2000,远程主机名:xz,用户名:sa,密码:无,数据库名:test

--创建测试表,不能用标识列做主键,因为不能进行正常更新
--在本机上创建测试表,远程主机上也要做同样的建表操作,只是不写触发器
if exists (select * from dbo.sysobjects where id = object_id(N'[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [test]

create table test(id int not null constraint PK_test primary key
,name varchar(10))
go

--创建同步的触发器
create trigger t_test on test
for insert,update,delete
as
set XACT_ABORT on
--启动远程服务器的MSDTC服务
exec master..xp_cmdshell 'isql /S"xz" /U"sa" /P"" /q"exec master..xp_cmdshell ''net start msdtc'',no_output"',no_output

--启动本机的MSDTC服务
exec master..xp_cmdshell 'net start msdtc',no_output

--进行分布事务处理,如果表用标识列做主键,用下面的方法
BEGIN DISTRIBUTED TRANSACTION
delete from openrowset('sqloledb','xz';'sa';'',test.dbo.test)
where id in(select id from deleted)
insert into openrowset('sqloledb','xz';'sa';'',test.dbo.test)
select * from inserted
commit tran
go

--插入数据测试
insert into test
select 1,'aa'
union all select 2,'bb'
union all select 3,'c'
union all select 4,'dd'
union all select 5,'ab'
union all select 6,'bc'
union all select 7,'ddd'

--删除数据测试
delete from test where id in(1,4,6)

--更新数据测试
update test set name=name+'_123' where id in(3,5)

--显示测试的结果
select * from test a full join
openrowset('sqloledb','xz';'sa';'',test.dbo.test) b on a.id=b.id

[zjcxc]

--定时同步服务器上的数据

--例子:
--测试环境,SQL Server2000,远程服务器名:xz,用户名为:sa,无密码,测试数据库:test
--服务器上的表(查询分析器连接到服务器上创建)
create table [user](id int primary key,number varchar(4),name varchar(10))
go
--以下在局域网(本机操作)
--本机的表,state说明:null 表示新增记录,1 表示修改过的记录,0 表示无变化的记录
if exists (select * from dbo.sysobjects where id = object_id(N'[user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [user]
GO
create table [user](id int identity(1,1),number varchar(4),name varchar(10),state bit)
go
--创建触发器,维护state字段的值
create trigger t_state on [user]
after update
as
update [user] set state=1
from [user] a join inserted b on a.id=b.id
where a.state is not null
go

--为了方便同步处理,创建链接服务器到要同步的服务器
--这里的远程服务器名为:xz,用户名为:sa,无密码
if exists(select 1 from master..sysservers where srvname='srv_lnk')
exec sp_dropserver 'srv_lnk','droplogins'
go
exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','xz'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa'
go

--创建同步处理的存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_synchro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_synchro]
GO
create proc p_synchro
as
--set XACT_ABORT on
--启动远程服务器的MSDTC服务
--exec master..xp_cmdshell 'isql /S"xz" /U"sa" /P"" /q"exec master..xp_cmdshell ''net start msdtc'',no_output"',no_output

--启动本机的MSDTC服务
--exec master..xp_cmdshell 'net start msdtc',no_output

--进行分布事务处理,如果表用标识列做主键,用下面的方法
--BEGIN DISTRIBUTED TRANSACTION
--同步删除的数据
delete from srv_lnk.test.dbo.[user]
where id not in(select id from [user])

--同步新增的数据
insert into srv_lnk.test.dbo.[user]
select id,number,name from [user] where state is null

--同步修改的数据
update srv_lnk.test.dbo.[user] set
number=b.number,name=b.name
from srv_lnk.test.dbo.[user] a
join [user] b on a.id=b.id
where b.state=1

--同步后更新本机的标志
update [user] set state=0 where isnull(state,1)=1
--COMMIT TRAN
go

--创建作业,定时执行数据同步的存储过程
if exists(SELECT 1 from msdb..sysjobs where name='数据处理')
EXECUTE msdb.dbo.sp_delete_job @job_name='数据处理'
exec msdb..sp_add_job @job_name='数据处理'

--创建作业步骤
declare @sql varchar(800),@dbname varchar(250)
select @sql='exec p_synchro' --数据处理的命令
,@dbname=db_name() --执行数据处理的数据库名

exec msdb..sp_add_jobstep @job_name='数据处理',
@step_name = '数据同步',
@subsystem = 'TSQL',
@database_name=@dbname,
@command = @sql,
@retry_attempts = 5, --重试次数
@retry_interval = 5 --重试间隔

--创建调度
EXEC msdb..sp_add_jobschedule @job_name = '数据处理',
@name = '时间安排',
@freq_type = 4, --每天
@freq_interval = 1, --每天执行一次
@active_start_time = 00000 --0点执行
go

[txlicenhe]

1： 建一个存储过程，用JOB来调度
2： 用DTS，源数据用SQL语句只查出近三个月的数据。

[tongcheng]

厉害
能说这么多

[mrzhou]

select * from openrowset('sqloledb','211.*.*.*';'user';'userpwd',test.dbo.test)
这句话在我的SQL server query analyzer中也运行不了，出错：OLE DB provider 'sqloledb' does not contain table 'test.dbo.test，我已经将test替换成服务器中的存在的表了。
注：这个SQL服务器端口不是默认的1433，而是2433了，会不会有影响？

[mrzhou]

在我用SQL server query analyzer连接到本地的MS SQL server后，怎么去查询我那个在internet上面的服务器中的表？我想试一下是不是能用。现在能能通过SQL管理器连过去。

[zjcxc]

要求你internet服务器有固定的ip地址,并且你本地的电脑能访问它.

查询分析器中可以用:
select * from openrowset('sqloledb','远程服务器的ip地址';'sa';'',数据库名.dbo.表名)

[zjcxc]

--或创建链接服务器访问:

--创建链接服务器
exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','远程服务器IP地址'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa','密码'
go

--查询示例
select * from srv_lnk.数据库名.dbo.表名

--导入示例
select * into 表 from srv_lnk.数据库名.dbo.表名

go
--处理完成后删除链接服务器
exec sp_dropserver 'srv_lnk','droplogins'

[mrzhou]

按照zjcxc的做法结果如下：
(1 row(s) affected)
(1 row(s) affected)
Server added.
(0 row(s) affected)
(1 row(s) affected)
(0 row(s) affected)
最后执行：select * from srv_lnk.数据库名.dbo.表名
显示：
OLE DB provider 'SQLOLEDB' does not contain table '"数据库名"."dbo"."表名"'.
我已经将数据库及表名替换成真实的了

[mrzhou]

我刚用：select * from openrowset('sqloledb','211.*.*.*';'sa';'',test.dbo.test)试了另一台在网上的服务器，可以用。但就是要用的这台不支持，为什么？是不是要设端口还是权限不够？现在显示：SQL Server does not exist or access denied.但是查询器能连到并且能查到数据

[mrzhou]

我看了一下SQL帮助，按以下方法可以实行：
SELECT a.*

FROM OPENROWSET('SQLOLEDB','211.*.*.*;'user';userpwd',

'SELECT * FROM test.table ') AS a
GO
这样就可以查出记录了，真奇怪！(test.dbo.table加个DBO也不行，出错）

[capcom]

select * from openrowset('sqloledb','211.*.*.*';'user';'userpwd',test.dbo.test)
这句话在我的SQL server query analyzer中也运行不了，出错：OLE DB provider 'sqloledb' does not contain table 'test.dbo.test，我已经将test替换成服务器中的存在的表了。
注：这个SQL服务器端口不是默认的1433，而是2433了，会不会有影响
--------------------------------------------------------------
应该是有影响！你改回来看看撒~~

[mrzhou]

各位：问题还是没有解决，（这个MS SQL是租了润迅服务器上的，没有SA的权限）现在的状况是如下：
在我的SQL管理器的我创建了一个LINK SERVER，名叫CM_SQL，连接成功了。点里面的tables能够显示出里面的表的名称了。但是在查询器的无法执行象select * from CM_SQL.数据库.DBO.表名 这样的查询，显示：OLE DB provider 'SQLOLEDB' does not contain table '"SZFLF"."DBO"."CLUB"'.出错了。我的SQL和CM上的都是7.0版本。我测试了和其它的在公网上的SQL2000服务器，成功了。就是CM这个搞不通。不知道是怎么回事。
请高手帮我！

[dlpseeyou]

ding

[cgsun]

up

[mrzhou]

SELECT a.*
FROM OPENROWSET('SQLOLEDB','211.*.*.*';'user';'userpwd',
'SELECT * FROM test') AS a
GO
这样的语句可以执行。但将'SELECT * FROM test'改为：***.dbo.test则行不通。
救命啊

[venket]

其实假如要用两个表保持同步的话，用复制的分布不就很简单啊！！！！

[mrzhou]

??

[mrzhou]

TO:zjcxc(邹建) 请将你EMAIL或QQ/MSN号告诉我，我告诉你服务器参数，你帮我联上去看一下。谢谢