May 14, 2012

sp_MSfixupbeforeimagetables (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSfixupbeforeimagetables(uniqueidentifier @pubid)

MetaData:

 create procedure sys.sp_MSfixupbeforeimagetables(@pubid uniqueidentifier)  
AS
begin
-- This procedure updates the generations in the existing rows in the before-image
-- tables of all articles in the specified publication to the current gen_cur value for
-- the respective article.
-- This is done after the subscription has been reinitialized (at a republisher). The reason
-- is that the genhistory rows at the subscriber no longer correspond to the values before
-- the reinit. They are simply bcp-ed in from the bcp generated at the publisher.
-- Setting the existing before image rows to gen_cur has the effect of avoiding the retention-
-- based cleanup of these rows based on invalid coldate values (since they are in context of
-- the publisher). The cleanup thus gets delayed to when the current gen_cur value expires retention.
declare @before_image_tablename sysname, @gencur bigint, @gencur_str nvarchar(30), @cmd nvarchar(1000)
declare @tablenick int

if @pubid is null
return 0

if ({ fn ISPALUSER(@pubid) } <> 1)
begin
RAISERROR (14126, 11, -1)
return (1)
end

declare beforeimagetables_cursor CURSOR LOCAL FAST_FORWARD for
select distinct object_name(before_image_objid), nickname
from dbo.sysmergearticles
where artid in
(select artid from dbo.sysmergearticles where pubid = @pubid)
and before_image_objid is not null
FOR READ ONLY

open beforeimagetables_cursor
fetch next from beforeimagetables_cursor into @before_image_tablename, @tablenick

while (@@fetch_status <> -1)
begin
begin transaction

exec sys.sp_MSmerge_getgencur @tablenick, 0, @gencur output

if @before_image_tablename is not null
begin
select @gencur_str = convert(nvarchar, @gencur)
select @cmd = 'update ' + quotename(@before_image_tablename) + ' set generation = ' + @gencur_str
exec (@cmd)
end

commit transaction

fetch next from beforeimagetables_cursor into @before_image_tablename, @tablenick
end

close beforeimagetables_cursor
deallocate beforeimagetables_cursor

return 0
end

No comments:

Post a Comment

Total Pageviews