合并复制中增加一个列但订阅服务器无法将数据同步回发布服务器

小灯数据-脚本小王子 2009-04-14 01:14:45
请教邹老大,我有A(发布服务器)、B(分发服务器)、C(订阅服务器)、D(订阅服务器)服务器,我在A服务器运行了以下语句增加了一下列:

sp_repladdcolumn @source_object = 'tblcase'
, @column = 'Flag'
, @typetext = 'INT'
, @publication_to_add = 'TelSell'

当在A服务器对Flag列进行update时,数据都可以同步到C和D服务器,但是在C或D服务器进行update时,却不能把数据同步到其它服务器!

实在找不到办法,只能求邹老师了,请指教
...全文
187 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
  • 打赏
  • 举报
回复
不行,问题还没解决~~
claro 2009-04-14
  • 打赏
  • 举报
回复
帮顶。
htl258_Tony 2009-04-14
  • 打赏
  • 举报
回复
create trigger upd_7C7EB787062A46A19F6D5A145B23D635 on [dbo].[tblcase] FOR UPDATE AS  
if sessionproperty('replication_agent') = 1 and (select trigger_nestlevel()) = 1
return
/* Declare variables */

declare @article_rows_updated int
select @article_rows_updated = count(*) from inserted
declare @contents_rows_updated int, @updateerror int
declare @bm varbinary(500), @missingbm varbinary(500), @lineage varbinary(255), @cv varbinary(2048)
declare @tablenick int, @nick int, @ccols int, @partchange int, @joinchange int
declare @partchangebm varbinary(500), @joinchangebm varbinary(500)
declare @oldmaxversion int

set nocount on
set @tablenick = 4201078
select @oldmaxversion= maxversion_at_cleanup from dbo.sysmergearticles where nickname = @tablenick

/* Use intrinsic funtion to set bits for updated columns */
set @bm = columns_updated()

/* only do the map down when needed */
set @missingbm = 0x000080E607
set @partchangebm = 0x00
set @joinchangebm = 0x00
if update([rowguid])
begin
if @@trancount > 0
rollback tran

RAISERROR (20062, 16, -1)
end


/* See if the partition might have changed */
if @partchangebm = 0x0
set @partchange = 0
else
set @partchange= { fn INTERSECTBITMAPS (@bm, @partchangebm) }

/* See if a column used in a join filter changed */
if @joinchangebm = 0x0
set @joinchange = 0
else
set @joinchange= { fn INTERSECTBITMAPS (@bm, @joinchangebm) }
execute master..xp_mapdown_bitmap 0x000080E607, @bm output

exec dbo.sp_MSgetreplnick @nickname = @nick output
select @ccols = 37

set @lineage = { fn UPDATELINEAGE(0x0, @nick, @oldmaxversion+1) }
set @cv = { fn INITCOLVS(@ccols, @nick) }
if (@@error <> 0)
begin
goto FAILURE
end
set @cv = { fn UPDATECOLVBM(@cv, @nick, @bm, @missingbm, { fn GETMAXVERSION(@lineage) }) }



update dbo.ctsv_7C7EB787062A46A19F6D5A145B23D635
set lineage = { fn UPDATELINEAGE(lineage, @nick, @oldmaxversion+1) },
generation = A.gen_cur,
joinchangegen = case when (@joinchange = 1) then A.gen_cur else joinchangegen end,
partchangegen = case when (@partchange = 1) then A.gen_cur else partchangegen end,

colv1 = { fn UPDATECOLVBM(colv1, @nick, @bm, @missingbm, { fn GETMAXVERSION({ fn UPDATELINEAGE(lineage, @nick, @oldmaxversion+1) }) }) }
FROM inserted as I JOIN dbo.ctsv_7C7EB787062A46A19F6D5A145B23D635 as V
ON (I.rowguidcol=V.rowguid)
and V.tablenick = @tablenick
JOIN (select top 1 nickname, gen_cur = isnull(gen_cur, 0) from dbo.sysmergearticles where nickname = @tablenick) as A
ON V.tablenick = A.nickname

select @updateerror = @@error, @contents_rows_updated = @@rowcount

if @article_rows_updated <> @contents_rows_updated
begin

insert into dbo.ctsv_7C7EB787062A46A19F6D5A145B23D635 (tablenick, rowguid, lineage, colv1, generation, partchangegen, joinchangegen)
select @tablenick, rowguidcol, @lineage, @cv, A.gen_cur,
case when (@joinchange = 1 or @partchange = 1) then A.gen_cur else NULL end,
case when @joinchange = 1 then A.gen_cur else NULL end
from inserted,
(select top 1 nickname, gen_cur = isnull(gen_cur, 0) from dbo.sysmergearticles where nickname = @tablenick) as A
where rowguidcol not in (select rowguid from dbo.ctsv_7C7EB787062A46A19F6D5A145B23D635 where tablenick = @tablenick)

if @@error <> 0
GOTO FAILURE
end


return
FAILURE:
if @@trancount > 0
rollback tran
raiserror (20041, 16, -1)
return
用这些代码试试。
htl258_Tony 2009-04-14
  • 打赏
  • 举报
回复
[Quote=引用 4 楼 feiyingnet 的回复:]
这是'sp_sel_7C7EB787062A46A149E4EE7021594E72_pal'的代码:

SQL code
create trigger upd_7C7EB787062A46A19F6D5A145B23D635 on [dbo].[tblcase] FOR UPDATE AS
if sessionproperty('replication_agent') = 1 and (select trigger_nestlevel()) = 1
return
/* Declare variables */

declare @article_rows_updated int
select @article_rows_updated = count(*) from inserted
d…
[/Quote]
把过程里面所有不带前缀的表(如:ctsv_7C7EB787062A46A19F6D5A145B23D635)前面加上dbo. ,(如:dbo.ctsv_7C7EB787062A46A19F6D5A145B23D635)
petunsecn 2009-04-14
  • 打赏
  • 举报
回复
添加列

* 若要将一个新列添加到表中并在现有发布中包括此列,请执行 ALTER TABLE <Table> ADD <Column>。默认情况下,此列然后将被复制到所有订阅服务器中。此列必须允许 NULL 值或包含默认约束。有关添加列的详细信息,请参阅本主题中的“合并复制”部分。
* 若要将一个新列添加到表中但不在现有发布中包括此列,请禁用架构更改复制,然后执行 ALTER TABLE <Table> ADD <Column>。
* 若要在现有发布中包括现有列,请使用 sp_articlecolumn (Transact-SQL)、sp_mergearticlecolumn (Transact-SQL) 或“发布属性 - <发布>”对话框。
有关详细信息,请参阅如何定义和修改列筛选器(复制 Transact-SQL 编程)和如何定义和修改列筛选器 (SQL Server Management Studio)。这要求重新初始化订阅。
* 不支持向已发布的表中添加标识列,因为将列复制到订阅服务器中时,可能会导致无法收敛。发布服务器中的标识列中的值取决于受影响表中的行的物理存储顺序。行在订阅服务器中的存储顺序可能会有所不同;因此对于相同的行,标识列的值可能会不同。
petunsecn 2009-04-14
  • 打赏
  • 举报
回复
重新初始化一次,再执行同步
shuiniu 2009-04-14
  • 打赏
  • 举报
回复
看一下帮助中“发布数据库上的架构更改”的说明。
里面讲的很清楚。
jia_guijun 2009-04-14
  • 打赏
  • 举报
回复
重新初始化一次。
ai2757 2009-04-14
  • 打赏
  • 举报
回复

  • 打赏
  • 举报
回复
是啥问题呢?
  • 打赏
  • 举报
回复
这是'sp_sel_7C7EB787062A46A149E4EE7021594E72_pal'的代码:

create trigger upd_7C7EB787062A46A19F6D5A145B23D635 on [dbo].[tblcase] FOR UPDATE AS
if sessionproperty('replication_agent') = 1 and (select trigger_nestlevel()) = 1
return
/* Declare variables */

declare @article_rows_updated int
select @article_rows_updated = count(*) from inserted
declare @contents_rows_updated int, @updateerror int
declare @bm varbinary(500), @missingbm varbinary(500), @lineage varbinary(255), @cv varbinary(2048)
declare @tablenick int, @nick int, @ccols int, @partchange int, @joinchange int
declare @partchangebm varbinary(500), @joinchangebm varbinary(500)
declare @oldmaxversion int

set nocount on
set @tablenick = 4201078
select @oldmaxversion= maxversion_at_cleanup from dbo.sysmergearticles where nickname = @tablenick

/* Use intrinsic funtion to set bits for updated columns */
set @bm = columns_updated()

/* only do the map down when needed */
set @missingbm = 0x000080E607
set @partchangebm = 0x00
set @joinchangebm = 0x00
if update([rowguid])
begin
if @@trancount > 0
rollback tran

RAISERROR (20062, 16, -1)
end


/* See if the partition might have changed */
if @partchangebm = 0x0
set @partchange = 0
else
set @partchange= { fn INTERSECTBITMAPS (@bm, @partchangebm) }

/* See if a column used in a join filter changed */
if @joinchangebm = 0x0
set @joinchange = 0
else
set @joinchange= { fn INTERSECTBITMAPS (@bm, @joinchangebm) }
execute master..xp_mapdown_bitmap 0x000080E607, @bm output

exec dbo.sp_MSgetreplnick @nickname = @nick output
select @ccols = 37

set @lineage = { fn UPDATELINEAGE(0x0, @nick, @oldmaxversion+1) }
set @cv = { fn INITCOLVS(@ccols, @nick) }
if (@@error <> 0)
begin
goto FAILURE
end
set @cv = { fn UPDATECOLVBM(@cv, @nick, @bm, @missingbm, { fn GETMAXVERSION(@lineage) }) }



update ctsv_7C7EB787062A46A19F6D5A145B23D635
set lineage = { fn UPDATELINEAGE(lineage, @nick, @oldmaxversion+1) },
generation = A.gen_cur,
joinchangegen = case when (@joinchange = 1) then A.gen_cur else joinchangegen end,
partchangegen = case when (@partchange = 1) then A.gen_cur else partchangegen end,

colv1 = { fn UPDATECOLVBM(colv1, @nick, @bm, @missingbm, { fn GETMAXVERSION({ fn UPDATELINEAGE(lineage, @nick, @oldmaxversion+1) }) }) }
FROM inserted as I JOIN ctsv_7C7EB787062A46A19F6D5A145B23D635 as V
ON (I.rowguidcol=V.rowguid)
and V.tablenick = @tablenick
JOIN (select top 1 nickname, gen_cur = isnull(gen_cur, 0) from dbo.sysmergearticles where nickname = @tablenick) as A
ON V.tablenick = A.nickname

select @updateerror = @@error, @contents_rows_updated = @@rowcount

if @article_rows_updated <> @contents_rows_updated
begin

insert into ctsv_7C7EB787062A46A19F6D5A145B23D635 (tablenick, rowguid, lineage, colv1, generation, partchangegen, joinchangegen)
select @tablenick, rowguidcol, @lineage, @cv, A.gen_cur,
case when (@joinchange = 1 or @partchange = 1) then A.gen_cur else NULL end,
case when @joinchange = 1 then A.gen_cur else NULL end
from inserted,
(select top 1 nickname, gen_cur = isnull(gen_cur, 0) from dbo.sysmergearticles where nickname = @tablenick) as A
where rowguidcol not in (select rowguid from ctsv_7C7EB787062A46A19F6D5A145B23D635 where tablenick = @tablenick)

if @@error <> 0
GOTO FAILURE
end


return
FAILURE:
if @@trancount > 0
rollback tran
raiserror (20041, 16, -1)
return


htl258_Tony 2009-04-14
  • 打赏
  • 举报
回复
存储过程的代码是什么,估计里面的表名只需加上dbo就可以了。
  • 打赏
  • 举报
回复

sp_repladdcolumn @source_object = 'tblcase'
, @column = 'Flag'
, @typetext = 'INT'
, @publication_to_add = 'TelSell'


警告: 仅运行 SQL Server 2000 的订阅服务器才能与发布 'TelSell' 同步,因为执行了架构复制。
无法在 sysdepends 中添加当前存储过程所对应的行,因为缺少该存储过程所依赖的对象 'sp_sel_7C7EB787062A46A149E4EE7021594E72_pal'。仍将创建该存储过程。


这个警告是否正常呢?
  • 打赏
  • 举报
回复
以前这个表用sp_repladdcolumn新增的列都可以相互同步,就是今天开始新增的列都不能向发布服务器同步

22,210

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧