DD44bD fD@ce48 D0eDe84fDe4 `HgDe4 D(iDe40oDe4he`nDpee84oDe4 EpDmae4(0qDe4qDe4vasD (e4 0wD D luesyDe4{De4{De40DH D D D D  CDE88 HpDE88 Hty D D  D( Dhh `.1-]0zb+xpweb70.dll0b-- Call by snapshot create procedure sp_MSsetartprocs (@publication sysname, @article sysname, @force_flag int = 0) as declare @ownername sysname declare @objectname sysname declare @guidstr nvarchar(40) declare @pubidstr nvarchar(40) declare @conflict_proc sysname declare @conflict_table sysname declare @snapshot_ready int declare @ins_procname sysname declare @sel_procname sysname declare @upd_procname sysname declare @view_selprocname nvarchar(290) declare @viewname sysname declare @artid uniqueidentifier declare @pubid uniqueidentifier declare @objid int declare @rgcol nvarchar(140) declare @sync_objid int declare @retcode smallint declare @dbname sysname declare @command nvarchar(1000) set nocount on /* ** Check to see if current publication has permission */ exec @retcode=sp_MSreplcheck_publish if @retcode<>0 or @@ERROR<>0 return (1) -- figure out pubid and artid if @force_flag = 1 begin -- don't qualify that must be publisher when we are forcing remake at subscribers select @pubid = pubid, @snapshot_ready=snapshot_ready from sysmergepublications where name = @publication and pubid in (select pubid from sysmergearticles where name=@article) end else select @pubid = pubid, @snapshot_ready=snapshot_ready from sysmergepublications where name = @qublication and UPPER(publisher)=UPPER(@@SERVERNAME) and publisher_db=db_name() if @pubid IS NULL BEGIN RAISERROR (20026, 16, -1, @publication) RETURN (1) END select @conflict_table=NULL select @artid = artid, @objid = objid, @sync_objid = sync_objid, @conflict_table=conflict_table FROM sysmergearticles WHERE name = @article AND pubid = @pubid if @artid IS NULL BEGIN RAISERROR (20027, 16, -1, @article) RETURN (1) END /* Dqop the article procs if they preexist */ exec @retcode = dbo.sp_MSdroparticleprocs @pubid, @artid if @@ERROR<>0 OR @retcode<>0 begin return (1) end -- get owner name, and table name select @objectname = name, @ownername = user_name(uid) from sysobjects where id = @objid -- make the insert and update proc names exec @retcode = dbo.sp_MSguidtostr @artid, @guidstr out if @@ERROR <>0 OR @retcode <>0 return (1) exec @retcode = dbo.sp_MSguidtostr @pubid, @pubidstr out if @AERROR <>0 OR @retcode <>0 return (1) select @ins_procname = 'sp_ins_' + substring(@guidstr, 1, 16) + substring(@pubidstr, 1, 16) exec dbo.sp_MSuniqueobjectname @ins_procname, @ins_procname output if @@ERROR <>0 OR @retcode <>0 return (1) select @upd_procname = 'sp_upd_' + substring(@guidstr, 1, 16) + substring(@pubidstr, 1, 16) exec dbo.sp_MSuniqueobjectname @upd_procname, @upd_procname output if @@ERROR <>0 OR @retcode <>0 return (1) select @sel_procname = 'sp_sel_' + substring(@guidsur, 1, 16) + substring(@pubidstr, 1, 16) exec dbo.sp_MSuniqueobjectname @sel_procname, @sel_procname output if @@ERROR <>0 OR @retcode <>0 return (1) set @view_selprocname = 'sel_' + substring(@guidstr, 1, 16) + substring(@pubidstr, 1, 16) exec @retcode = dbo.sp_MSuniqueobjectname @view_selprocname , @view_selprocname output if @retcode <> 0 or @@ERROR <> 0 return (1) -- create the procs set @dbname = db_name() set @command = 'sp_MSmakeinsertproc ' + QUOTENAME(@objectname) + ' ,!' + QUOTENAME(@ownername) + ' , ' + @ins_procname + ', [' + convert(nchar(36), @pubid) + ']' exec @retcode = master..xp_execresultset @command, @dbname if @@ERROR<>0 OR @retcode<>0 begin return (1) end exec @retcode = dbo.sp_MS_marksystemobject @ins_procname if @@ERROR<>0 or @retcode<>0 return (1) exec ('grant exec on ' + @ins_procname + ' to public') set @command = 'sp_MSmakeupdateproc ' + QUOTENAME(@objectname) + ' , ' + QUOTENAME(@ownername) + ' , ' + @upd_procname ) ', [' + convert(nchar(36), @pubid) + ']' exec @retcode = maste0b r..xp_execresultset @command, @dbname if @@ERROR<>0 OR @retcode<>0 begin return (1) end exec @retcode = dbo.sp_MS_marksystemobject @upd_procname if @@ERROR<>0 or @retcode<>0 return (1) exec ('grant exec on ' + @upd_procname + ' to public') if @@ERROR<>0 return (1) set @command= 'SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON' exec (@command) if @@ERROR<>0 return (1) set @command = 'sp_MSmakeselectproc ' + QUOTENAME(@objectname) + ' , ' + QUOTENAME(@ownername)+ ' , ' + @sel_procname + ', [' + convert(nchar(36), @pubid) + ']' exec @retcode = master..xp_execresultset @command, @dbname if @@ERROR<>0 or @retcode<>0 begin return (1) end exec @retcode = dbo.sp_MS_marksystemobject @sel_procname if @@ERROR<>0 or @retcode<>0 return (1) exec ('grant exec on ' + @sel_procname + ' to public') if @@ERROR<>0 return (1) if @sync_objid <> 0 begin select @ownername = user]name(uid), @viewname = name from sysobjects where id = @sync_objid select @rgcol = QUOTENAME(name) from syscolumns where id = @objid and ColumnProperty(id, name, 'isrowguidcol') = 1 exec @retcode=dbo.sp_MSmakeviewproc @viewname, @ownername, @view_selprocname, @rgcol, @objid if @@ERROR<>0 or @retcode<>0 return (1) end else set @view_selprocname = '' --to be consistent with upgrade code by checking snapshot_ready>0 if @snapshot_ready>0 and @conflict_table is not NULl begin exec @retcode = dbo.sp_MSguidtostr @artid, @guidstr out if @@ERROR <>0 OR @retcode <>0 return (1) exec @retcode = dbo.sp_MSguidtostr @pubid, @pubidstr out if @@ERROR <>0 OR @retcode <>0 return (1) select @conflict_proc = 'sp_cft_' + substring(@guidstr, 1, 16) + substring(@pubidstr, 1, 16) exec @retcode=sp_MSuniqueobjectname @conflict_proc , @conflict_proc output if @@ERROR <> 0 OR @retcode <> 0 return(1) set @dbname = db_name() set @command = 'sp_MSmakeconflictinsertproc ' + QUOTENAME(@conflict_table) + ' , ' + QUOTENAME(@ownername) + ' , ' + @conflict_proc + ' , ' + convert(nvarchar,@objid) set @command = @command + ', [' + convert(nchar(36), @pubid) + ']' exec @retcode = master..xp_execresultset @command, @dbname if @@ERROR<>0 OR @retcode<>0 begin return (1) end exec @retcode = dbo.sp_MS_marksystemobject @conflict_proc if @@ERROR<>0 or @retcode<>0 return (1) exec ('grant exec on ' + @conflict_proc + ' to public') if @@ERROR<>0 return (1) update sysmergearticles set ins_conflict_proc = @conflict_proc where artid = @artid and pubid=@pubid end -- update articles to set the names update sysmergearticles set insert_proc = @ins_procname, update_proc = @upd_procname , select_proc = @sel_procname, view_sel_proc = @view_selprocname where artid = @artid and pubid = @pubid IF @@ERROR<>0 return (1) return (0) C H>CpubidCl>CD>Cschemachanges2@pubidC>CDD88 =C>C88 schemachanges2Cschematypeschemachanges2(0?Cp?Cschematype0D?CD0?Cschemachanges200?C Fi>ư> D`2i0c-- -- Name: fn_escapecmdshellsymbols -- -- Descriptions: This function returns an escaped version of a given string -- with carets ('^') added in front of all the special -- command shell symbols. In the W2K online help (Start-> -- Help->Index->Command Reference->Command Symbols...), the -- following symbols are listed as special command symbols: -- (N'%', N'<', N'>', N'|', N'&', N'(', N')', N'^', N'"') -- -- Parameter: @command_string nvarchar(4000) -- -- Example: select fn_escapecmdshellsymbols(N'The % quick < brown > dog | jumps & over ( the ) lazy ^ fox.') -- will return -- -- The ^% quick ^< brown ^> dog ^| jumps ^& over ^( the ^) lazy ^^ fox -- -- Security: This function is granted to public -- create function system_function_schema.fn_escapecmdshellsymbols( @command_string nvarchar(4000) ) returns nvarchar(4000) as begin declare @escaped_command_string nvarchar(4000), @curr_char nvarchar(1), @curr_char_index int select @escaped_command_string = N'', @curr_char = N'', @curr_char_index = 1 while @curr_char_index <= len(@command_string) begin select @curr_char = substring(@command_string, @curr_char_index, 1) if @curr_char in (N'%', N'<', N'>', N'|', N'&', N'(', N')', N'^', N'"') begin select @escaped_command_string = @escaped_command_string + N'^' end select @escaped_command_string = @escaped_command_string + @curr_char select @curr_char_index = @curr_char_index + 1 end return @escaped_command_string end 0Cc CREATE PROCEDURE sp_MSdropmergepub_snapshot ( @publication sysname, @ignore_distributor bit = 0 ) AS SET NOCOUNT ON /* ** Declarations. */ declare @retcode int declare @distributor sysname declare @distproc nvarchar(300) declare @snapshot_jobid binary(16) declare @fFoundPublication int declare @pubid uniqueidentifier declare @distribdb sysname /* ** Initializations */ select @fFoundPublication = 0 /* validate the publication */ /* If the publication is not exist found return error */ EXEC @retcode = dbo.sp_helpmergepublication @pvblication, @fFoundPublication output, @pubid output if @@ERROR <> 0 OR @fFoundPublication = 0 OR @retcode <> 0 BEGIN RETURN (1) END /* ** Get taskid. Make sure the snapshot_jobid is not NULL only before using MAX ** Otherwise there will be a warnning. */ select @snapshot_jobid = max(snapshot_jobid) FROM MSmerge_replinfo WHERE repid = @pubid if (@snapshot_jobid IS NOT NULL) begin /* ** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC. */ if @ignore_distributor = 0 begin /* ** Get distributor information */ EXEC @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUTPUT, @distribdb = @distribdb OUTPUT if @@error <> 0 OR @retcode <> 0 BEGIN RAISERROR (14071, 16, -1) RETURN (1) END /* ** Delete sync agent of Publication. */ declare @dbname sysname set @dbname = DB_NAME() SELECT @distproc = RTRIM(@distributor) + '.' + @distribdb + '.dbo.sp_MSdrop_snapshot_agent' EXECUTE @retcode = @distproc @publisher = @@SERVERNAME, @publisher_db = @dbname, @publication = @publication IF @@ERROR <> 0 or @retcode <> 0 RETURN(1) end /* Updbte publication's taskid */ UPDATE MSmerge_replinfo set snapshot_jobid = NULL WHERE repid = @pubid IF @@ERROR <> 0 BEGIN RAISERROR (20072, 16, -1) RETURN (1) END end return (0) $D$D88 S&$DF 4$D@$D$DN4D DU(4hD$D $D^--$D `$D D($Ddartid88(0name(0 4$Dname$D4$D@retcodeyscsp_MSuniqueobjectname@conflict_proc$D*4$D*sp_MSuniqueobjectnameD$DhDsy4@conflict_procoutputhD4$D$D@bU(D$DX$D$D-H.P$D$DX$D ` D($D@  $D8$DS(0088 H$D$D$Df.d.ge ` D(8$D`$D0$Dh$Dhhsm@sys_loopch00$DpD$Dhh$DH$DFF88 00P$D$D8D"7hhsi@retcode4 `00$DD`D@hhX$DD$D bi$D@p 088 H$D $D $D488 $D$DJ.d.H$D ` D(P$D@ c ($D CU(4($D@statuscstatusc$D$D(000$D$D 88 @$D$DU(88 D@dbnamedb_name0$D$Df..me$D ` D(X$D@  csiopD(044 $D $Dlength$D`$Dc$D`$DU(44$D$D@pecH$Dc88 p$D ecDF4 x$DU(4 D@commandchsp_MSmakeconflictinsertproc D$D./U(0D ` D($D@  p$D؂Dx$DDX$DDsyscolumns c^Hx$D4$Dsp_MSmakeconflictinsertproc $D,`x43!K0,le+create function system_function_schema.fn_trace_getinfo (@handle int = 0 ) returns @tab table(traceid int NOT NULL, property int NOT NULL, value sql_variant) as begin insert @tab select * from OpenRowset(TraceInfo, @handle) return end -- fn_trace_getinfo 0$ve-- -- Name: fn_repltrimleadingzerosinhexstr -- -- Description: This function is used for trimming the leading zeros -- of a 32-bit hexadecimal string. For example, -- select fn_repltrimleadingzerosinhexstr('0000abcd') would -- yield 'abcd'. A notable special case would be select -- fn_repltrimleadingzerosinhexstr('00000000') which yields -- the string '0' instead of ''. -- -- Parameter: @hexstr nchar(8) - @hexstr is expected to be a full -- hexadecimal representation of a 32-bit integer without the -- ! 0x prefix -- -- Notes: -- -- Returns: nvarchar(8) -- -- Security: Execute permission of this function is granted to public. -- create function system_function_schema.fn_repltrimleadingzerosinhexstr ( @hexstr nchar(8) ) returns nvarchar(8) as begin declare @trimmed_hexstr nvarchar(8), @i int select @i = 1 while @i < 8 begin if substring(@hexstr,@i,1) <> N'0' break; select @i = @i + 1 end qelect @trimmed_hexstr = substring(@hexstr,@i,8-@i+1) return @trimmed_hexstr end 0ae -- Modify temp table. No security check needed. create procedure sp_MSbelongs @publisher sysname, @publisher_db sysname, @publication sysname, @tablenick int, @rowguid uniqueidentifier, @retval int output, @nested int = 0 AS declare @artid uniqueidentifier declare @join_guid uniqueidentifier declare @last_joinid int declare @join_id int declare @join_nick int dealare @probe_id int declare @last_probe int declare @join_nickstr nvarchar(10) declare @pubid uniqueidentifier declare @guidstring nvarchar(38) declare @subset_filter nvarchar(4000) declare @tablename nvarchar(266) declare @join_table nvarchar(266) declare @boolean nvarchar(4000) declare @retcode smallint select @pubid = pubid from sysmergepublications where name = @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db /* Check for case of all rows - dm I trust subset_type ? */ select @boolean = subset_filterclause, @artid = artid from sysmergearticles where pubid = @pubid and nickname = @tablenick if ((@boolean is null or @boolean = ' ') and not exists (select * from sysmergesubsetfilters where art_nickname = @tablenick)) begin set @retval = 0 return end if @nested = 0 begin create table #found (flag int NOT NULL) create table #probe (probe_id int identity NOT NULL, tablenick int NOT NULL, rowguid uniqueidentifieq ROWGUIDCOL default newid() not null, tested int NOT NULL) insert into #found values (0) set @last_probe = 0 end else begin select @last_probe = max(probe_id) from #probe end /* pubid is already available */ exec @retcode = dbo.sp_MStablenamefromnick @tablenick, @tablename out, @pubid if @@ERROR<>0 or @retcode<>0 return (1) set @guidstring = '''' + convert(nchar(36), @rowguid) + '''' /* If there is boolean filter, check for it being satisfied */ if @boolean is not!null and @boolean <> ' ' begin exec ('if exists (select * from ' + @tablename + ' where rowguidcol = ' + @guidstring + ' and (' + @boolean + ')) update #found set flag = 1') if @@ERROR<>0 return (1) select @retval = flag from #found if @retval = 1 goto EndLabel end /* Loop over join filters, populating #probe */ select @join_id = min(join_filterid) from sysmergesubsetfilters where pubid = @pubid and art_nickname = @tablenick while @join_id is not null begin select @boolean = join_filterclause, @join_nick = join_nickname from sysmergesubsetfilters where pubid = @pubid and join_filterid = @join_id exec @retcode = dbo.sp_MStablenamefromnick @join_nick, @join_table out, @pubid if @@ERROR<>0 or @retcode<>0 return (1) set @join_nickstr = convert(nchar(10), @join_nick) /* execute a query to put these into the #probe table */ exec ('insert into #probe (tablenick, rowguid, tested) select distinct ' + @join_nickstr + ', ' + @join_table + '.rowguidcol, 0 from '!+ @tablename + ', ' + @join_table + ' where ' + @tablename + '.rowguidcol = ' + @guidstring + ' and (' + @boolean + ') and not exists (select * from #probe where tablenick = ' + @join_nickstr + ' and rowguidcol = ' + @join_table + '.rowguidcol) ' ) if @@ERROR<>0 begin return (1) end /* get to next join filter and repeat */ set @last_joinid = @join_id select @join_id = min(join_filterid) from sysmergesubsetfilters where pubid = @pubid and art_nickname = @tablenici and join_filterid > @last_joinid end /* Loop over probe, making recursive call */ select @probe_id = min(probe_id) from #probe where probe_id > @last_probe and tested = 0 while @probe_id is not null begin select @join_nick = tablenick, @join_guid = rowguidcol from #probe where probe_id = @probe_id set @last_probe = @probe_id /* update tested flag on this row so we don't try it again while recursing */ update #probe set tested = 1 where probe_id = @probe_id /* Make recursive!call. If it bc0ae"elongs, we are done. */ exec @retcode = dbo.sp_MSbelongs @publisher, @publisher_db, @publication, @join_nick, @join_guid, @retval output, 1 if @@ERROR<>0 OR @retcode<>0 begin return (1) end if @retval = 1 goto EndLabel /* get next probe_id and repeat */ select @probe_id = min(probe_id) from #probe where probe_id > @last_probe and tested = 0 end /* All Done, delete temps if not nested */ EndLabel: if @nested = 0 begin drop taale #found drop table #probe end return DFH4D@D(@4') } = 1) 4D ') } = 1)EhD D@4DDU(@4`D@DDaR))FD ` D(DcodeD008DD DhhD` Dsc chhD D88 @view_creation_command`D4PDDDb))@DD ` D(D@  DpD XDsyesD88CDd))PDv ` D(xD0D(0 Dartid@artidDDMDDbiF88 00D@D DidhhDt  oHD88 DD488 HDD0Cc))public4D to public `X5 047+uȡ0j--==================================================================================== -- sp_readwebtask: retreive web task parameters -- CREATE PROCEDURE sp_readwebtask @procname nvarchar(128) = NULL AS BEGIN DECLARE @retval int SET @retval = 0 -- If the procedure name is NULL, Display a list of web tasks IF NOT EXISTS (SELECT * FROM msdb..mswebtasks WHERE procname = @procname) BEGIN SET @retval = 16815 RAISERROR(@retval,11,1,@procname) RETURN(@retval) END -- Execute xp_readwebtask given the procedure name EXEC @retval = master..xp_readwebtask @procname IF (@retval <> 0) BEGIN SELECT @procname = 'sp_readwebtask' RAISERROR(@retval,11,1, @procname) END RETURN @retval END 0&j -- Modify temp table. No security check needed. create procedure sp_MSsetupworktables @pubid uniqueidentifier, @genlist varchar(8000), @articlesoption int, -- 0=process all articles, 1=process this specific article (whose nickname is passed in @tablenickname), 2=all articles involved in join filters, 3=process all articles involved in part filters, 4=process articles whose nicknames have been passed in @nicknamelist. @tablenickname int, @nicknamelist varchar(8000), @mingen int = 0, @maxgen int = 0, @skipgenlist varchar(8000) = NULL, @contents_subset_rowcount int OUTPUT, @tombstone]subset_rowcount int OUTPUT AS declare @lengenlist int , @lenskipgenlist int , @command nvarchar(4000) , @retcode int -- put in a nickname with value 0 - This will match -- gen history rows put in by downlevel subscribers -- with art_nick = NULL select @command = N'insert into #nicknames_to_process values (0)' exec @retcode = sp_executesql @command if @@error <> 0 or @retcode <> 0 return 1 if (@articlesoption = 0) begin -- process all articles for this publication select @command = N'insert into #nicknames_to_process select distinct nickname from sysmergearticles a where pubid = @pubid' exec @retcode = sp_executesql @command, N'@pubid uniqueidentifier', @pubid=@pubid if @@error <> 0 or @retcode <> 0 return 1 end else if (@articlesoption = 1) begin --process only the article whose nickname has been passed-in select @command = N'insert into #nicknames_to_process values (@tablenickname)' exec @retcode = sp_executesql @command, N'Atablenickname int', @tablenickname=@tablenickname if @@error <> 0 or @retcode <> 0 return 1 end else if (@articlesoption = 2) begin --process all articles with join filters (article could be on any side - left or right - of any join filter) select @command = N'insert into #nicknames_to_process select distinct nickname from sysmergearticles a where pubid = @pubid and exists (select * from sysmergesubsetfilters s where s.pubid = @pubid and (s.art_nickname = a.mickname or s.join_nickname = a.nickname))' exec @retcode = sp_executesql @command, N'@pubid uniqueidentifier', @pubid=@pubid if @@error <> 0 or @retcode <> 0 return 1 end else if (@articlesoption = 3) begin --process all articles with part filters but that don't have a join filter. select @command = N'insert into #nicknames_to_process select distinct nickname from sysmergearticles a where pubid = @pubid and datalength(subset_filterclause) > 1 and not exists (selecu * from sysmergesubsetfilters s where s.pubid = @pubid and (s.art_nickname = a.nickname or s.join_nickname = a.nickname))' exec @retcode = sp_executesql @command, N'@pubid uniqueidentifier', @pubid=@pubid if @@error <> 0 or @retcode <> 0 return 1 end else if (@articlesoption = 4) begin --process all articles whose nicknames have been passed into @nicknamelist if (@nicknamelist is not null and rtrim(ltrim(@nicknamelist)) <> '') begin execute ('insert into #nicknames_to]process select distinct nickname from sysmergearticles where nickname in (' + @nicknamelist + ')') if @@error <> 0 return 1 end end -- Create index on #nicknames_to_process now that it has been populated. Creating it after data insertion is better because -- that generates the stats for the index. That helps in the insert into #contents_subset query. The other option was to -- create the index, insert the data, and then update statistics. select @command = N'create unique index #nicknames_ind on #nicknames_to_process (nickname)' exec @retcode = sp_executesql @command if @@error <> 0 or @retcode <> 0 return 1 if (@maxgen <> 0) begin select @command = N'insert into #genlist select distinct generation from dbo.MSmerge_genhistory gh join #nicknames_to_process DD Y DE88 He($DD4 $D $D ؟ DE88 H"h$DD44H2 $D D H $D p $D D Y $DE88 He $D4D44He$D4e4 h$DX De4X$DDȘ D e$D4  D$Dma @($DE88 H$DDD44HH$DD44EDe4e$D4e84e0D4e4eD4e4 DDe4D8De4De4  D$De4D`De4HDDe4PDXDD eD4 D  DE88 H"DD44HؙDD HD pD D8DE88 HDDD44HcD00 cDxDE88 H`kDD kD DsDD88 sD XwD D nd(wDapE88 H Ѓ D@e sn DDot h D88  DD D؈ D  n D 0 DD  D ȋ D @D hD D  D Ѓ DD F D̥ ؈ D88  D D؈ DD qDDE88 HDX!CD44hnмDe4$DDC00$D(C00 ` DD  DD0D  Dxt  3  Df؏ D8De4  D0 D $DC00p$DYhhsnsh($DDmi qn$DD (0H $DD  $D (D$Do $DXD $D Q($DD $D @!C $D H$DD @D 8D$D $D $D@!C  $D $D  $D $DD  $D $ $D88  $DE88 HX$DD44ac $Dese4$DC00$DC00DD pDDD D#s ac3 ng fDx`4h 5R]Q 0&j np on isnull(gh.art_nick,0) = np.nickname and gh.generation >= @mingen and gh.generation <= @maxgen' exec @retcode = sp_executesql @command, N'@mingen int, @maxgen int', @mingen=@mingen, @maxgen=@maxgen if @@error <> 0 or @retcode <> 0 return 1 select @lengenlist = isnull(datalength(@genlist),0) select @lenskipgenlist = isnull(datalength(@skipgenlist),0) -- no need to do ltrim and rtrim on the @skipgenlist. sp_MSsetupbelongs already did that. if (@skipgenlist is not null and @skipgenlist <> '' and @lenskipgenlist <= @lengenlist) begin execute ('delete from #genlist where generation in (' + @skipgenlist + ')') if @@error <> 0 return 1 end else if (@genlist is not null and @genlist <> '') begin -- gen 0 won't be in @genlist, so will get deleted because of the NOT IN. execute ('delete from #genlist where generation not in (' + @genliqt + ')') if @@error <> 0 return 1 end end else if (@genlist is not null and @genlist <> '') begin execute ('insert into #genlist select distinct generation from dbo.MSmerge_genhistory where (isnull(art_nick,0) in (select nickname from #nicknames_to_process)) and generation in (' + @genlist + ') ') if @@error <> 0 return 1 end -- Create index on #genlist now that it has been populated. Creating it after data insertion is better because -- that generates the stats for the index. That helps in the insert into #contents_subset query. The other option was to -- create the index, insert the data, and then update statistics. select @command = N'create unique index #genlist_ind on #genlist (generation)' exec @retcode = sp_executesql @command if @@error <> 0 or @retcode <> 0 return 1 if @maxgen is null select @maxgen = 0 if @mingen is null select @mingen = 0 if (@maxgen = 0) begin -- SQL 7.0 pull merge agents do not pass in @maxgen and @mingen, so the default is 0. -- For them need to compute min and max. Note that we could do the same for 8.0 agents as well -- but 8.0 agents pass the min and max because they are used in the INSERT INTO #genlist query above -- when we don't have the #genlist table and all we have is the comma-separated @genlist string. select @command = N'select @mingen = min(generation), @maxgen = max(generation) from #genlist' exec @retcode = sp_executesql @command, N'@mingen int output, @maxgen int output', @mingen=@mingen output, @maxgen=@maxgen output if @@error <> 0 or @retcode <> 0 return 1 end select @command = N'insert into #contents_subset (tablenick, rowguid, generation, partchangegen, joinchangegen) select c.tablenick, c.rowguid, c.generation, c.partchangegen, c.joinchangegen from dbo.MSmerge_contents c JOIN #nicknames_to_process a ON c.generation >= @mingen AND c.generation <= @maxgen AND c.tablenick = a.nickname JOIN #genlist g ON c.generation = g.generation AND c.generation >= Amingen AND c.generation <= @maxgen UNION -- the rows retrieved below have a partition change that falls into the relevant generation range -- if we do not process them now, we would later on assume that the partition change has already been processed select c.tablenick, c.rowguid, c.generation, c.partchangegen, c.joinchangegen from dbo.MSmerge_contents c JOIN #nicknames_to_process a ON c.partchangegen >= @mingen AND c.partchangegen <= @maxgen AND c.tablenick = a.nickname JOIN #genlist g ON c.partchangegen = g.generation AND c.partchangegen >= @mingen AND c.partchangegen <= @maxgen select @contents_subset_rowcount = @@rowcount' exec @retcode = sp_executesql @command, N'@mingen int, @maxgen int, @contents_subset_rowcount int output', @mingen=@mingen, @maxgen=@maxgen, @contents_subset_rowcount=@contents_subset_rowcount output if @@error <> 0 or @retcode <> 0 return 1 select @command = N'insert into #tombstone_subset (tablenick, rowguid, type, generation) select t.tablBVD0&j|enick, t.rowguid, t.type, t.generation from dbo.MSmerge_tombstone t JOIN #nicknames_to_process a ON t.generation >= @mingen AND t.generation <= @maxgen AND t.tablenick = a.nickname JOIN #genlist g ON t.generation = g.generation AND t.generation >= @mingen AND t.generation <= @maxgen select @tombstone_subset_rowcount = @@rowcount' exec @retcode = sp_executesql @command, N'@mingen int, @maxgen int, @tombstone_subset_rowcount int output%, @mingen=@mingen, @maxgen=@maxgen, @tombstone_subset_rowcount=@tombstone_subset_rowcount output if @@error <> 0 or @retcode <> 0 return 1 select @command = N'create clustered index #ucind_contents_subset on #contents_subset (tablenick, rowguid) with FILLFACTOR = 100 create clustered index #ucind_tombstone_subset on #tombstone_subset (tablenick, rowguid) with FILLFACTOR = 100 delete #nicknames_to_process from #nicknames_to_process ntp where not exists (select tablenick from #contents_subset cs where cs.tablenick = ntp.nickname) and not exists (select tablenick from #tombstone_subset ts where ts.tablenick = ntp.nickname) -- remove the nickname with value 0 that we put in specially at the beginning of this proc. delete from #nicknames_to_process where nickname = 0' exec @retcode = sp_executesql @command if @@error <> 0 or @retcode <> 0 return 1 return (0) $ ȵRDD88 RD RE> D]88 HRDxRD$ 88 pRDRDRDXDRD1 D RDRDG)Dhh$ RDXRD88 C(088  HRDstatusRD E88  0`DRDطRDpuE88 H00RD0RD88 XRDRD]88 RDRD88 ظRDRDpRD RD (DHRDRDhhpRDRD88 (088  RDstatusRD E88 RD@RDE88 H00hRD88RD6 D 0`D88 RDRD]88 RDHRD88 @RD$ pRDؼRDRDB)D RDRDhh88ػRD(RD88  (088  RDstatusPRDE88 $ xRD RDDE88 H00мRDRD88 (RDXRD3]88 RDRD88 RDؽRD@RD,RDHL)D RDRD[)Dhh@RDRD88 (088  RDstatus RD0ǻDE88 RDRDǻDE88 H008RDhRD8888 RDAVDRD: D]88  @VDP@VDRD`7,6WqQ0k NOT NULL, rowguid uniqueidentifier NOT NULL, generation int NULL, lineage varbinary(255) NULL, type tinyint NOT NULL)') if @@ERROR <>0 return (1) end -- Fully qualifying the proc avoids recompiles exec @retcode = dbo.sp_MSsetupworktables @pubid, @genlist, @articlesoption, @tablenickname, @nicknamelist, @mingen, @maxgen, @skipgenlist, @contents_subset_rowcount OUTPUT, @tombstone_subset_rowcount OUTPUT IF @@ERROR<>0 OR @retcode<>0 return (1) if (@contents_subset_rowcount = 0 and @tombstone_subset_rowcount = 0) goto EXITPROC if (@articlesoption <> 1 and @articlesoption <> 3 and @articlesoption <> 4) begin select @dynamic_join_cnt = count(*) from (select join_filterclause_spaces_stripped = REPLACE(REPLACE(REPLACE(REPLACE(UPPER(join_filterclause collate SQL_Latin1_General_CP1_CS_AS), char(0x20),''), char(0x09),''), char(0x0E),''), char(0x0A),'') from dbo.sysmergesubsetfilters where pubid = @pubid) as sysmergesubsetfilters_temp where sysmergesubsetfilters_temp.join_filterclause_spaces_stripped like '%USER[_]%NAME()%' or sysmergesubsetfilters_temp.join_filterclause_spaces_stripped like '%USER[_]%ID()%' or sysmergesubsetfilters_temp.join_filterclause_spaces_stripped like '%SESSION[_]USER%' or sysmergesubsetfilters_temp.join_filterclause_spaces_stripped like '%SYSTEM[_]USER%' end /* step 2 setup pass through dbo.MSmerge_contents */ /* article with permanent views can be handled with bulk inserts */ set @artnick = NULL /* Get first article, go into loop */ select @command = N'select @artnick = min(nickname) from #nicknames_to_process' exec @retcode = sp_executesql @command, N'@artnick int output', @artnick = @artnick output if @@error <> 0 or @retcode <> 0 return 1 while (@artnick is not null) begin select @artbaseobjid = objid, @procname = view_sel_proc, @before_view_objid = before_view_objid, @before_table_objid = before_image_objid from dbo.sysmergearticles where pubid = @pubid and nickname = @artnick /* Get name of rowguidcol. Aliasing doesn't work through a view. */ select @rgcol = quotename(name) from dbo.syscolumns where id = @artbaseobjid and ColumnProperty(@artbaseobjid, name, 'isrowguidcol') = 1 if (@procname is not null) begin -- Fully qualifying the proc avoids recompiles select @procname = 'dbo.' + quotename(@procname) exec @retcode = @procname @artnick if @@ERROR <>0 or @retcode <> 0 begin return (1) end end else begin -- Fully qualifying the proc avoids recompiles exec @retcode = dbo.sp_MSsetupbelongs_withoutviewproc @publisher, @publisher_db, @publication, @artnick if @@ERROR <>0 or @retcode <> 0 return (1) end -- Fully qualifying the proc avoids recompiles exec @retcode = dbo.sp_MSsetupnotbelongs @artnick, @before_view_objid, @before_table_objid, @rgcol,!@commongen if @@ERROR <>0 or @retcode <> 0 return (1) /* Move on to next article, repeat while loop */ select @command = N'select @artnick = min(nickname) from #nicknames_to_process where nickname > @artnick ' exec @retcode = sp_executesql @command, N'@artnick int output', @artnick = @artnick output if @@error <> 0 or @retcode <> 0 return 1 end if (@articlesoption <> 1 and @articlesoption <> 3 and @articlesoption <> 4) begin /* Optimization: If joinchangegen and partchange are both null or < common gen, ** it is not necessary to expand #belong for that particular row. */ select @maxfilterid = max(join_filterid) from dbo.sysmergesubsetfilters if @maxfilterid is not null begin select @command = N'update #belong set flag = @maxfilterid, skipexpand = 1 where isnull(joinchangegen,0) <= @commongen and isnull(partchangegen,0) <= @commongen' exec @retcode = sp_executesql @command, N'@maxfilterid int, @commongen int', @maxfilterid = @maxfilterid,!@commongen = @commong0ken if @@error <> 0 or @retcode <> 0 return 1 /* Expand the #belong temptable */ exec @retcode = dbo.sp_MSexpandbelongs @pubid if @@ERROR<>0 OR @retcode<>0 begin return (1) end end end /* If subscriber is sql server, we don't have to expand belongs */ if (@articlesoption <> 1 and @articlesoption <> 3 and @articlesoption <> 4) and (@subissql = 0 or @dynamic_join_cnt > 0) begin /* Expand the #notbelong temptable!*/ exec dbo.sp_MSexpandnotbelongs @pubid if @@error<>0 return(1) end EXITPROC: /* transfer rows from local temp to global temp */ -- Could have used if not exists instead of the following but want to use the KEEP PLAN option which is not supported in the IF EXISTS select @belongsempty = 0 select @command = N'select @belongsempty = 1 where not exists (select * from #belong)' exec @retcode = sp_executesql @command, N'@belongsempty bit output', @belongsempty = @belongsempty output if @@error <> 0 or @retcode <> 0 return 1 select @notbelongsempty = 0 select @command = N'select @notbelongsempty = 1 where not exists (select * from #notbelong)' exec @retcode = sp_executesql @command, N'@notbelongsempty bit output', @notbelongsempty = @notbelongsempty output if @@error <> 0 or @retcode <> 0 return 1 /* If there are no rows in #belong, then drop the global ##belongs so that we do not call sp_MSenumpartialchanges */ if (@belongsempty = 1) begin /* Post SQL 8.0 Beta 2!agents pass this flag with value 1 since they can handle NULL belongs table name */ if @handle_null_tables = 1 begin exec ('drop table ' + @belongsname) select @belongsname = NULL end end else begin exec ('insert into ' + @belongsname + ' (tablenick, rowguid, generation, lineage, colv) select distinct b.tablenick, b.rowguid, c.generation, c.lineage, c.colv1 from #belong b left outer join dbo.MSmerge_contents c on c.tablenick = b.tablenick and c.rowguid = b.rmwguid ') if @@ERROR <>0 begin return (1) end -- this index will be useful in sp_MSenumpartialchanges exec ('create index nc1belongstable on ' + @belongsname + ' (tablenick, rowguid) with FILLFACTOR = 100') if @@ERROR <>0 return (1) end /* If there are no rows in #notbelong, then drop the global ##notbelongs so that we do not call sp_MSenumpartialchanges */ if (@notbelongsempty = 1) begin /* Post SQL 8.0 Beta 2 agents pass this flag with value 1 since they can handle NULL notbelongs table name */ if @handle_null_tables = 1 begin exec ('drop table ' + @notbelongsname) select @notbelongsname = NULL end end else begin /* transfer rows from local temp to global temp */ exec ('insert into ' + @notbelongsname + ' (tablenick, rowguid, generation, lineage, type) select distinct b.tablenick, b.rowguid, coalesce (c.generation, t.generation), coalesce(c.lineage, t.lineage), b.type from #notbelong b left outer join dbo.MSmerge_cmntents c on c.tablenick = b.tablenick and c.rowguid = b.rowguid left outer join dbo.MSmerge_tombstone t on t.tablenick = b.tablenick and t.rowguid = b.rowguid order by b.tablenick DESC, b.rowguid ASC ') if @@ERROR <>0 begin return (1) end -- this index will be useful in sp_MSenumpartialdeletes exec ('create index nc1notbelongstable on ' + @notbelongsname + ' (tablenick DESC, rowguid) with FILLFACTOR = 100') if @@ERROR <>0 return (1) end if (@belongsempty = 1) select @belongsname, @notbelongsname, -1 else begin if (@articlesoption = 1) begin select @belongsname, @notbelongsname, @tablenickname end else begin select @command = N'select distinct @belongsname, @notbelongsname, tablenick from #belong' exec @retcode = sp_executesql @command, N'@belongsname sysname, @notbelongsname sysname', @belongsname=@belongsname, @notbelongsname=@notbelongsname if @@error <> 0 or @ret8PiDiDinfoiD`6|P7Wu0kcode <> 0 return 1 end end drop table #notbelong drop table #belong drop table #contents_subset drop table #tombstone_subset drop table #temp_cont drop table #nicknames_to_process return (0) x!qD]88 !qD!qD88 !qD!qD8#qD"qD 8"qD"qDdhh`"qD"qD@)D88  D(088 "qDreplinfo88 "qD#qDE88 H.D000#qD -D`#qD88 #qD#qD]88 #qD$qD6D88 $qD8$qD8&qD P$qD$qD? D x$qD$qD3 DhhH'D(0 4 $qDxtype4$qDP%qD 8%qD%qD hh`%qD%qD#OV 88 \(088  %qDstatus88 %qD&qD88E88 H000&qD`&qD5 D88 &qD&qD]88 &qD'qD88 'qD8'qD(qDP'qD'qD x'qD'qDhh(0 4 'qDxtype4'qDD(qD 8(qD(qD\hh(088 x(qDparent_obj(qDE88 HMD00(qD)qD88 8)qDh)qD]88 )qD@D0S)D)qD>qD88 )qD;qD88 *qD@9qD88 0*qD6qD,88 X*qDp4qD88 *qD2qDgN88 *qD/qD88 *qD8-qDh88 *qD{]88  +qDP+qD88 H+qDx+qD,qD+qD +qD,qDhh+qD0,qD88 (088  ,qDstatusX,qDE88 ,qD ,qDE88 H00,qD-qD88 0-qD`-qD\]88 -qD-qD88 -qD-qDH/qD(1 D-qD  .qD.qEX( DhhH.qD.qD= D88 < D(088  .qDstatus.qDE88 .qD/qDE88 H00@/qDp/qDD88 /qD/qD> D]88 /qD 0qD88 0qDH0qD1qD`0qD1 D 0qDX1qDG)Dhh0qD1qD88 C(088  0qDstatus(1qD E88 P1qD1qDpuE88 H001qD1qD88 2qD02qD]88 X2qD2qD88 2qD2qD4qD2qD 2qD3qDhh3qDh3qD88 (088  X3qDstatus3qD E88 3qD3qDE88 H004qD@4qD6 D88 h4qD4qD]88 4qD4qD88 4qD5qD6qD05qDB)D X5qD(6qDhh5qD5qD88 (088  5qDstatus5qDE88  6qDP6qDDE88 H00x6qD6qD88 6qD7qD3]88 (7qDX7qD88 P7qD7qD8qD,7qDHL)D 7qD8qD[)Dhh7qD88qD88 (088  (8qDstatus`8qD0ǻDE88 8qD8qDǻDE88 H008qD9qD88 89qDh9qD: D]88 9qD9qD88 9qD9qDP;qD:qD (:qD:qDhhP:qD:qD88 = D(088  :qDstatus:qDE88 :qD ;qDE88 H00H;qDx;qD88 ;qD;qD]88 ;qD(qD8>qDM]88 `>qD>qD88 >qD@>qDP@D>qD >qD?qDhh ?qDp?qD 88 0 D(088  `?qDstatus?qDE88 ?qD}D @DE88 H qD` 8e 0 0TWu argument list (phase 1) set @cmdpiece = ', ' + @argname + ' ' + @typename insert into #tempcmd (phase, cmdtext) values (1, @cmdpiece) -- add to column list and value list if (@colordinal = 1) begin -- column list is phase 5 set @cmdpiece = @colname insert into #tempcmd (phase, cmdtext) values (5, @cmdpiece) -- argname for values list is phase 7 set @cmdpiece = @argname insert into #tempcmd (phase, cmdtext) values (7, @cmdpiece) end else begin -- column list is phase 5; need preceding comma since not the first one. set @cmdpiece = ', ' + @colname insert into #tempcmd (phase, cmdtext) values (5, @cmdpiece) -- argname for values list is phase 7 need preceding comma since not the first one. set @cmdpiece = ', ' + @argname insert into #tempcmd (phase, cmdtext) values (7, @cmdpiece) end -- is this an identity column without 'not fmr replication' marking? if (@status = 128) and (@colstat & 0x0008 =0) begin -- turning identity insert on is phase 3 set @cmdpiece = ' set identity_insert ' + @qualified_name + ' on' insert into #tempcmd (phase, cmdtext) values (3, @cmdpiece) -- turning identity insert on is phase 9 set @cmdpiece = ' set identity_insert ' + @qualified_name + ' off' insert into #tempcmd (phase, cmdtext) values (9, @cmdpiece) end Next_Column: -- now set up to repeat the loop with the next!column select @colid = min (colid) from syscolumns where id = @sync_objid and colid > @colid set @colname = NULL if @colid is not null select @colname = QUOTENAME(name), @status = status, @typename = type_name(xtype), @len = length, @prec = COLUMNPROPERTY(id, name, 'precision'), @scale = scale from syscolumns where id = @sync_objid and colid = @colid select @status = status, @iscomputed=iscomputed, @xtype=xtype,@colstat=colstat from syscolumns where id = @id and QUOTENAME(name) = Acolname if @typename='nvarchar' or @typename='nchar' -- a unit of nchar takes 2 bytes select @len = @len/2 set @colordinal = @colordinal + 1 end select @cmdpiece = ',@metadata_type tinyint = NULL, @lineage_old varbinary(255) = NULL' insert into #tempcmd (phase, cmdtext) values (1, @cmdpiece) -- Now we select out the command text pieces in proper order so that our caller, -- xp_execresultset will execute the command that creates the stored procedure. -- phase 10 is returning our success / failure status set @cmdpiece = ' return(1) Failure: rollback tran sp_insproc commit tran ' insert into #tempcmd (phase, cmdtext) values (10, @cmdpiece) exec dbo.sp_MScreatedupkeyupdatequery @tablename= @qualified_name, @tablenickstr= @tablenickstr, @phase= 10, @isconflictproc= 0 set @cmdpiece= ' return(@errcode) ' insert into #tempcmd (phase, cmdtext) values (10, @cmdpiece) select cmdtext from #tempcmd order by phase, step drop table #tempcmd drop uable #coltab VDVDxVD@VDVDVDVDhVDVDVDVDVD0VD`VDVDVDHVDVDVDVDVDdPDHiPDjPDkPDVDlPDmPDXVDnPDPpPDqPD8sPDtPDuPDwPDXxPDyPDCcDDcDEcD GcD@HcDXIcDpJcDKcDMcD NcDXOcDhPcDQcDRcDTcDUcDVcDXcDXYcEsysnamesysnamesysnamerPDYhhsysnamesysnamesysname]cDq@4VD`Deq@4pcDVDq@40iDe@4s iD4e@4(0 iD4eP4pnDVDxt oDVD[D pD tD tDVDE88 H}D H`De4`De4 iD hiD88E88 HoDD  qDe4 @rDe4{D {D88E88 H ChVD[D (C PC PVDCE88 HCD44HHCVD C ypCE88 HCSE88 H8CVDD CVD C pCE88 HypCE88 HxCUE88 HDe@4 пDhVD`VD h D  D D44E88 HVD8 D44D44H D = DVDHB  D VD DHVD88  D  ` De4 De4 p DE88 H 8 D PVDH De4 D88e4 De4DVDv4 0D XD DE88 HD D44HD(VD (DS VDPD DE88 HDD44HțDVD 8D HVD`Dhh DE88 HDD44HypaDe@4jDVDV VDxjD jD iDE88 HVDHjDD44HrDVD VDxrD0VDhh sD (0 qD4E88 HHrDxtE88 HrD4E88 HVD|D HbDe@4bDVD VDXbD bDPVD  bDxVDE88 H(bDD44HHbDVD bDxt bD bDVDE88 HbDD44HbDHVD 8bD`VDxVD țbDVD PbDVDs МbDma bD bDE88 HbD E88 HbDE88 H bDE88 HbDE88 HcnDVD cnDE88 H 0jnDVDYhhlnDe@4 unD0VD unDxt unD tnDpVDE88 H`unDD44HynDYhhVD|nDe@4XwDVD VDȅwDhh wD (0 (wD4E88 HwDxtD44HЋwD4 WcD YDC00wDVD PUDVD`:,95u0{vr columns -- phase 4 begin a transaction, set savepoint in case we roll back, begin select to get current values set @cmdpiece = ' begin transaction sub save transaction sub select ' insert into #tempcmd (phase, cmdtext) values (4, @cmdpiece) -- phase 5 is middle part of select assigning column values to local variables -- goes in loop -- phase 6 -- finish the select, check that metadata matches set @cmdpiece = ' from ' + @qualified_name + ' (updlock) where rowguidcol = @rowguid exec @retcode= dbo.sp_MScheckmetadatamatch @metadata_type, @rowguid, @tablenick, @lineage_old, @match output if @retcode<>0 or @@ERROR<>0 begin set @errcode= 3 goto Failure end if (@match = 1) begin ' insert into #tempcmd (phase, cmdtext) values (6, @cmdpiece) -- phase 7 is a bunch of if's that compare old values with new values ; goes in during loop -- phase 8 finish the stored procedure -- now do the loop over all columns and insert the missing pieces -- don't script out computed columns or timestamp columns select @colid = min (colid) from syscolumns where id = @sync_objid and name not in (select name from syscolumns where id=@id and (iscomputed=1 OR type_name(xtype)='timestamp')) select @colname = NULL select @colname = name, @typename = type_name(xtype), @blen = length, @prec = COLUMNPROPERTY(id, name, 'precision'), @scale = scale from syscolumns where id = @syna_objid and colid = @colid select @status = status, @iscomputed=iscomputed, @xtype=xtype, @isrowguidcol = COLUMNPROPERTY(id, name, 'IsRowGuidCol') from syscolumns where id = @id and name = @colname -- get col count to determine what size our bitmask is going to be declare @colcount int select @colcount = count(*) from syscolumns where id = @sync_objid and name not in (select name from syscolumns where id=@id and (iscomputed=1 OR type_name(xtype)='timestamp')) set @cmdpiece = ' declare @colbitmask binary(' + convert(nvarchar,1+(@colcount-1) / 8) + ') select @colbitmask = 0 ' select @numbytes = 1+(@colcount-1) / 8 insert into #tempcmd (phase, cmdtext) values (2, @cmdpiece) if @typename='nvarchar' or @typename='nchar' -- a unit of nchar takes 2 bytes set @len = @blen/2 else set @len = @blen set @colordinal = 1 declare @firstCol tinyint set @firstCol= 1 while (@colname is not null) begin set @colordstr = convert(nvarchar(4), @colordinal) exec @retcode = dbo-sp_MSmaptype @typename out, @len, @prec, @scale if @@ERROR <>0 OR @retcode <>0 return (1) if (@fast_multicol_updateproc_bit = 1) begin -- check if separate update statement is needed only if article supports fast multi-column updates. -- reset @separate_update_needed set @separate_update_needed = 0 --check if this column is part of the filter or join filter clause. -- if so, use a separater update statement for it rather than setting bitmask for the one cumulative update stauement. set @colpat = '%' + @colname + '%' -- does updating this column change membership in a partial replica? if exists (select * from sysmergearticles where objid = @id and subset_filterclause like @colpat) set @separate_update_needed = 1 else if exists (select * from sysmergesubsetfilters where art_nickname = @tablenick and join_filterclause like @colpat) set @separate_update_needed = 1 else if exists (select * from sysmergesubsetfilters where join_nickname = @tablenick and join_eilterclause like @colpat) set @separate_update_needed = 1 else if (@typename = 'ntext' or @typename = 'text' or @typename = 'image') set @separate_update_needed = 1 end else begin set @separate_update_needed = 1 -- separate update statement for each column. end if @status = 128 OR @iscomputed=1 OR type_name(@xtype)='timestamp' goto Next_Column set @colname = QUOTENAME(@colname) -- put in argument list element (phase 1) set @argname = '@p' + rtrim(@colordstr) inqert into #coltab (col 0{vname, paramname) values (@colname, @argname) set @cmdpiece = ', ' + @argname + ' ' + @typename + ' = NULL ' insert into #tempcmd (phase, cmdtext) values (1, @cmdpiece) if (@isrowguidcol = 1) goto Next_Column if (@separate_update_needed = 0) begin select @bytestr = convert( nvarchar, 1 + (@colordinal-1) / 8 ) select @byteordinal = 1 + (@colordinal-1) / 8 select @bitstr = convert( nvarchar, power(2, (@colordinal-1) % 8 ) ) if (@update_stmt_started = 0) begin select @update_stmt_started = 1 select @cmdpiece = 'update ' + @qualified_name + ' set ' insert into #tempcmd (phase, cmdtext) values (8, @cmdpiece) -- goes after phase 7 select @cmdpiece = ' where rowguidcol = @rowguid select @rowcount= @@rowcount, @error= @@error if (@rowcount <> 1) begin set @errcode= 3 goto Failure end' insert into #tempcmd (phase, cmdtext) values (10, @cmdpiece) -- goes after phase 9 which would be the SET clause for different columns end else begin select @cmdpiece = ',' insert into #tempcmd (phase, cmdtext) values (9, @cmdpiece) end select @cmdpiece = @colname + ' = case substring(@colbitmask,' + @bytestr + ',1) & ' + @bitstr + ' when ' + @bitstr + ' then ' + @argname + ' else ' + @colname + ' end ' insert into #tempcmd (phase, cmdtext) values (9, @cmdpiece) end -- put in declaration for variable (phase 3) -- text and image get no variable if (@typename <> %ntext' and @typename <> 'text' and @typename <> 'image') begin set @varname = '@l' + rtrim(@colordstr) set @cmdpiece = 'declare ' + @varname + ' ' + @typename + ' ' insert into #tempcmd (phase, cmdtext) values (3, @cmdpiece) -- put in set piece to initialize variable to old value in select statement (phase 5) if @firstCol=1 begin set @cmdpiece= '' set @firstCol= 0 end else set @cmdpiece= ', ' set @cmdpiece = @cmdpiece + @varname + ' = ' + @colname insert imto #tempcmd (phase, cmdtext) values (5, @cmdpiece) -- put in if piece that compares old value with new, checks bit if argument is null if (@typename like '%char%') begin -- Compare binaries instead of variables so that case changes are caught as different set @littlecomp = 'convert(varbinary(' + rtrim(convert(nchar, @blen)) + '), ' + @argname + ') = convert(varbinary(' + rtrim(convert(nchar, @blen)) + '), ' + @varname + ')' end else begin set @littlecomp = @argname + ' = ' + @varname end set @cmdpiece = ' if ' + @littlecomp + ' set @fset = 0 else if ( ' + @varname + ' is null and ' + @argname + ' is null) set @fset = 0 else if ' + @argname + ' is not null set @fset = 1 else if @setbm = 0x0 set @fset = 0 else exec @fset = dbo.sp_MStestbit @setbm, ' + @colordstr + ' if @fset <> 0 begin ' insert into #tempcmd (phase, cmdtext) values (7, @cmdpiece) if (@separate_update_needed = 0) begin if (@numbytes = 1) begin -- no bytes on left or right of the byte being bitwise OR-ed. set @cmdpiece = ' select @colbitmask = convert(varbinary(1), substring(@colbitmask, ' + @bytestr + ' , 1) | ' + @bitstr + ') end ' end else begin -- there could be byte(s) on left and/or right of byte being bitwise OR-ed. set @cmdpiece = ' select @colbitmask = convert ( varbinary(' + convert(nvarchar, @numbytes) + '), ' + case when (@byteordinal =!1) then '' else ' convert(varbinary(' + convert(nvarchar, @byteordinal-1) + '), substring(@colbitmask, 1, ' + convert(nvarchar, @byteordinal-1) + ')) +' end + ' convert(varbinary(1), substring(@colbitmask, ' + @bytestr + ' , 1) | ' + @bitstr + ') ' + case when (@byteordinal = @numbytes) then '' else ' + convert(varbinary(' + convert(nvarchar, @numbytes - @byteordinal) + '), substring(@colbitmask, ' + convert(nvarchar, @byteordinal+1) + ', ' + co D88 (@D`9z :9Q0{v# nvert(nvarchar, @numbytes - @byteordinal) + ')) ' end + ' ) end ' end end else begin set @cmdpiece = ' update ' + @qualified_name + ' set ' + @colname + ' = ' + @argname + ' where rowguidcol = @rowguid if (@@rowcount <> 1) begin set @errcode= 3 goto Failure end end ' end insert into #tempcmd (phase, cmdtext) values (7, @cmdpiece) select @has_updateable_columns_in_select_list = 1 end else begin -- for text and image, we just test if argument is null and whether bit is set -- build conditional update (phase 7) set @cmdpiece = ' if ' + @argname + ' is not null set @fset = 1 else if @setbm = 0x0 set @fset = 0 else exec @fset = dbo.sp_MStestbit @setbm, ' + @colordstr + ' if @fset <> 0 begin update ' + @qualified_name + ' set ' + @colname + ' = '!+ @argname + ' where rowguidcol = @rowguid if (@@rowcount <> 1) begin set @errcode= 3 goto Failure end end ' -- Now insert the command to temp table insert into #tempcmd (phase, cmdtext) values (7, @cmdpiece) end Next_Column: -- Advance loop to next column and repeat! select @colid = min (colid) from syscolumns where id = @sync_objid and colid > @colid and name not in (select name from syscolumns where id=@id and (iscomputed=1 OR type_name(xtype)='timestamp')) set @colname = NULL if (@colid is not null) begin select @colname = name, @typename = type_name(xtype), @blen = length, @prec = COLUMNPROPERTY(id, name, 'precision'), @scale = scale from syscolumns where id = @sync_objid and colid = @colid select @status = status, @iscomputed=iscomputed, @xtype=xtype, @isrowguidcol = COLUMNPROPERTY(id, name, 'IsRowGuidCol') from syscolumns where id = @id and name = @colname if @typename='nvarchar' or @typename='nchar' -- a unit of nchar takes 2 bytes set @len = @blen/2 else set @len = @blen set @colordinal = @colordinal + 1 end end -- Add dummy column list to select statement if there is no user updateable -- column. if @has_updateable_columns_in_select_list = 0 begin insert into #tempcmd (phase, cmdtext) values (3, N'declare @l int') insert into #tempcmd (phase, cmdtext) values (5, N'@l = 1') end -- Now we select out the command text pieces in proper order so that our calleq, -- xp_execresultset will execute the command that creates the stored procedure. set @cmdpiece = ' exec @retcode= dbo.sp_MSsetrowmetadata @tablenick, @rowguid, @generation, @lineage_new, @colv, 2, ''' + convert(nvarchar(36),@pubid) + ''' if @retcode<>0 or @@ERROR<>0 begin set @errcode= 3 goto Failure end end else begin set @errcode= 2 goto Failure end commit transaction return(1) Failure: rollback transaction sub commit transaction ' insert into #tempamd (phase, cmdtext) values (11, @cmdpiece) exec dbo.sp_MScreatedupkeyupdatequery @tablename= @qualified_name, @tablenickstr= @tablenickstr, @phase= 11, @isconflictproc= 0 set @cmdpiece = ' return @errcode' insert into #tempcmd (phase, cmdtext) values (11, @cmdpiece) select cmdtext from #tempcmd order by phase, step drop table #tempcmd drop table #coltab DH1D-D /DD-D -D.Dhh .Dp.E88 ?D(088  `.Dstatus *D.DE88 .D@0D.D .DE88 H300/D H/D8888 p/D0/D\]88 /D/DH1D88 /DxWD 0D1E(1 D80D Ph`0D01DX( Dhh?0D0D= D88 < D(088  0Dstatus61DE88 4D(1D0X1DE88 H82Dh001D 1DD488 1D 2D> D]88 02D`2D088 X2D2D3DH1D2D1 D 2D3DG)DhhH1D2D@3D88 C(088  03Dstatus3Dh3D E88 IA3D?D3DpuE88 HB003D4DD88 @4D0(p4D]88 4D4DYD$D88 4D84DX6D5D 805D6DhhX5D5D88 (088  5Dstatus8D5D E88 @6D5DB(6DE88 HdB00P6D6D6 D88 6DdB6D]88 7D07DHBsp88 (7DcX7D8Dhp7DB)D 7Dh8Dhh@0D7D8D88 7D(088  8Estatus@88DE88 4`8D8DDE88 H0008Dh8DZD8888 9D8@9D3]88 h9D9D88 9D9D(;D,9DHL)D :D:D[)Dhh(:Dx:D88 (088  h:Dstatus :D0ǻDE88 :D3.j:DǻDE88 HL00 ;DHDP;D88 x;D?D;D: D]88 ;DD]88 8>Dh>D82D>D88 `>D?D>D Dgk>D >D?Dhh>DH?D88 (088  8?Dstatusp?DE88 ?D@ ?DE88 H_Mex00?D0x$D` z ;JI 0_w$ create function system_function_schema.fn_removeparameterwithargument( @command nvarchar(3200), @parameter nvarchar(200) ) returns nvarchar(3200) begin declare @pattern nvarchar(806) declare @resulting_command nvarchar(3200) declare @command_length int declare @pattern_start int declare @first_iteration bit declare @parameter_length int select @first_iteration = 1 select @pattern = fn_generateparameterpattern(@parameter) select @parameter_length = len(@parameter) + 1 -- leading - or / select @resulting_command = N'' select @pattern_start = patindex(@pattern,@command) while @pattern_start <> 0 and @pattern_start is not NULL begin select @command_length = len(@command) -- Check if the pattern found is properly delimited in the command. -- If so, try to skip over the next token- if (@pattern_start = 1 and @first_iteration = 1 or (@pattern_start > 1 and fn_chariswhitespace(substring(@command, @pattern_start - 1, 1)) = 1)) and (fn_chariswhitespace(substring(@command, @pattern_start + @parameter_length, 1)) = 1) begin -- Add everything before the parameter to the resulting command select @resulting_command = @resulting_command + rtrim(substring(@command, 1, @pattern_start - 1)) -- Eat!the parameter select @command = substring(@command, @pattern_start + @parameter_length, @command_length - @pattern_start - @parameter_length + 1) -- Skip over the argument of the parameter select @command = fn_skipparameterargument(@command) if @resulting_command <> N'' begin select @resulting_command = @resulting_command + N' ' end end else begin -- Not a properly delimited parameter so transfer the -- matching substring to the resulting command and -- eat the substring in the process select @resulting_command = @resulting_command + substring(@command, 1, @pattern_start + @parameter_length - 1) select @command = substring(@command, @pattern_start + @parameter_length, @command_length - @pattern_start - @parameter_length + 1) end select @first_iteration = 0 select @pattern_start = patindey(@pattern, @command) end select @resulting_command = @resulting_command + @command return @resulting_command end x D@permissions00 DLcD Dhh D 88 h Dge@ Did488 0 D DEkU( D ` @D(p D@ DphasecmdtextKcD4 Dcmdtext D@cmdpiece@cmdpiece exec @retcode = dbo.sp_MSreplcheck_permission @objid = <4 D< exec @retcode = dbo.sp_MSreplcheck_permission @objid = ecVDHD@c@4 @idstrsP DhDcDs @4pd, @type=2, @permissions = ter4 D, @type=2, @permissions = 0 DpD @4d @permissions_strble0 DHJcD =@4 if @retcode<>0 or @@ERROR<>0 return (4),40 D, if @retcode<>0 or @@ERROR<>0 return (4) DDX D@4` D@rU(@4VD D DGp8P D ` @D(x D@ MSmerge_contents set generation = 0, partchangegen = 0 where rowguid = @rowguid and tablenick = @tablenick4DsyU(@4KcD DS@cmdpiece@cmdpiece select @tablenick = 4Ȓ D select @tablenick = VDDns@4@tablenickstr DD@4e( DteU(@4VDȗ D DHs:$ as0 D ` @D(@ D D4 Dcmdtext@@ DГ D#tempcmd Dphase D Dpc4 x DphasecmdtextVDH D4 Dcmdtextc@ D D#tempcmd^Hco88 D@cmdpiece@cmdpiece begin transaction sub save transaction sub select D DIw& KcD DЛ D ` @D( D@  D(И D D@procname88 D4 74p D7 begin transaction sub save transaction sub select DphU(@4VD D8 DJz t"dt D ` @D( DH D@0 D D#tempcmd( D#tempcmdphase DP D4 Л DotphasecmdtextVD48 Dcmdtext@ D D#tempcmdH D^Hid88 x D@cmdpiece@cmdpiece from iD DK~v"$Q(D ` @D(x D@ D DdIcD4Ȟ D DD4h D from D@qualified_nameDxYD(4 `PD( D@  P D`!x<D $ 0  create PROCEDURE sp_MSdelsubrows (@rowguid uniqueidentifier, @tablenick int, @metadata_type tinyint, /* 0 - Missing, 1 - Tombstone, 2 - Contents, 3 - ContentsDeferred, 6 - system delete */ @lineage_old varbinary(255), @generation int, @lineage_new varbinary(255), @pubid uniqueidentifier = NULL, @rowsdeleted INT = NULL OUTPUT) as declare @match int declare @errcode int declare @new_metatype tinyint declare @retcode smallint declare @reason nvarchar(255) declare @procname sysname declare @tnstr nvarchar(11) declare @error int, @rowcount int /* By default this sp should delete exactly one row */ set @rowsdeleted = 1 /* ** Check to see if current publication has permission */ if @pubid is NULL begin exec @retcode = dbo.sp_MSrepl_PAL_rolecheck @tablenick = @tablenick if @retcode<>0 or @@ERROR<>0 return (0) end else begin if ({ fn ISPALUSER(@pubid) } <> 1) begin RAISERROR (14126, 11, -1) return (1) end end /* Parameter validation */ if (@rowguid is null) begin RAISERROR(14043, 16, -1, '@rowguid') return (0) end if (@tablenick is null) begin RAISERROR(14043, 16, -1, '@tablenick') return (0) end if (@lineage_new is null) begin RAISERROR(14043, 16, -1, '@lineage_new') return (0) end -- Are we just changing the type of a tombstone? if (@metadata_type = 5 or @metadata_type = 1) begin if exists (select * from dbo.MSmerge_tombstone where rowguid = @rowguid and tablenick = @tablenick) begin if (@metadata_type = 5) set @reason = formatmessage (20563) -- Remove from partial else if (@metadata_type = 1) set @reason = formatmessage (20562) -- User delete update dbo.MSmerge_tombstone set type = @metadata_type, reason = @reason, generation = @generation, lineage = @lineage_new where rowguid = @rowguid ane tablenick = @tablenick return 1 end end -- Are we just changing the type of a tombstone? else if (@metadata_type = 6) begin if exists (select * from dbo.MSmerge_tombstone where rowguid = @rowguid and tablenick = @tablenick) begin set @reason = formatmessage (20564) -- System deleted update dbo.MSmerge_tombstone set type = @metadata_type, reason = @reason where rowguid = @rowguid and tablenick = @tablenick return 1 end end -- begin transaction and lock row uhat we plan to delete begin transaction save tran sp_MSdelsubrows select @procname = select_proc from sysmergearticles where nickname = @tablenick and pubid = @pubid exec @retcode = @procname @type =8, @rowguid=@rowguid IF @@ERROR<>0 or @retcode<>0 begin set @errcode= 0 goto Failure end if @metadata_type = 5 begin set @match = 1 set @new_metatype = 5 end else if @metadata_type = 6 begin set @match = 1 set @new_metatype = 6 end else if @metadata_type = 7 begin set @match = 1 set @new_metatype = 7 end else begin exec @retcode=sp_MScheckmetadatamatch @metadata_type, @rowguid, @tablenick, @lineage_old, @match output IF @@ERROR<>0 or @retcode<>0 begin set @errcode= 0 goto Failure end set @new_metatype = 1 end if (@match = 1) begin /* If there are any joinfilters with this as the join table, try to expand to deleting ** a set of related rows. */ if (exists (select * from sysmergesubsetfilters where puaid = @pubid and join_nickname = @tablenick)) begin declare @tn int declare @table_name nvarchar(270) select @rowsdeleted = 0 set @reason = formatmessage (20563) -- Moved out of partial range /* create temp and put in our tablenick, rowguid */ create table #notbelong (tablenick int NOT NULL, rowguid uniqueidentifier NOT NULL, flag int NOT NULL) create index #indnbelong on #notbelong (tablenick, rowguid) insert into #notbelong (tablenick, rowguid, flag) values )@tablenick, @rowguid,0 _ 0) /* call expand proc */ exec @retcode = dbo.sp_MSexpandsubsnb @pubid IF @@ERROR<>0 or @retcode<>0 begin set @errcode= 0 goto Failure end select @tn = max(tablenick) from #notbelong where flag > -1 while @tn is not null begin select @tnstr = convert(nvarchar(11), @tn) exec @retcode = dbo.sp_MStablenamefromnick @tn, @table_name out, @pubid -- delete all rows indicated by the temp table IF A@ERROR<>0 or @retcode<>0 begin set @errcode= 0 goto Failure end exec ('delete from ' + @table_name + ' where RowGuidCol in (select rowguid from #notbelong where tablenick = ' + @tnstr + ')' ) select @error=@@error, @rowcount=@@rowcount IF @error<>0 begin set @errcode= 0 goto Failure end select @rowsdeleted = @rowsdeleted + @rowcount -- move on to next nickname - decreasing makes delete order correct update #notbelong seu flag = -1 where tablenick = @tn select @tn = max(tablenick) from #notbelong where flag > -1 end -- change tombstone type for those rows update dbo.MSmerge_tombstone set type = 5, reason = @reason from #notbelong nb, dbo.MSmerge_tombstone ts where ts.tablenick = nb.tablenick and ts.rowguid = nb.rowguid drop table #notbelong exec @retcode= dbo.sp_MSsetrowmetadata @tablenick, @rowguid, @generation, @lineage_new, NULL, @new_metatype, @pubid IF @@ERROR<>0 or @qetcode<>0 begin set @errcode= 0 goto Failure end end else begin -- select_proc makes a delete with @type = 5, despite its name. exec @retcode = @procname @type =5, @rowguid=@rowguid select @error= @@error, @rowcount= @@rowcount IF @error<>0 or @retcode<>0 begin set @errcode= 0 goto Failure end if (@rowcount = 1) begin exec @retcode= dbo.sp_MSsetrowmetadata @tablenick, @rowguid, @generation, @lineage_new, NULL, @new_metatype- @pubid IF @@ERROR<>0 or @retcode<>0 begin set @errcode= 0 goto Failure end end else goto Failure end end else begin set @errcode= 2 goto Failure end commit tran return 1 -- in sp_MSdelsubrows, 1=okay Failure: rollback tran sp_MSdelsubrows commit tran return(@errcode) -- in sp_MSdelsubrows, 0=error 88 wD wD`D4F4 ץ@colnamewD ȝwD 4wDsysmergesubsetfilters`wD0wD*sysmergesubsetfilters.rod,^Hjoin_nicknameb (0wDjoin_nickname@tablenick؛wD|D join_filterclauseui(0"wDjoin_filterclauseera@colpatwDCe,FX00hwDniwD@wDchPwDhrD4 pwDSD 88 wDb r00 wDnchawD @D @D(XwDwD(wDS(88 PwD0CD ` D( @D@ ]88 0@CDXXD`>,=]2ez09.create PROCEDURE sp_MSdelsubrowsbatch (@tablenick int, @rowguid_array varbinary(8000), @metadatatype_array varbinary(500), /* 0 - Missing, 1 - Tombstone, 2 - Contents, 3 - ContentsDeferred, 6 - system delete */ @oldlineage_len_array varbinary(1000), -- -- specifies the number of lineages in the @oldlineage_array. @oldlineage_array image, @generation_array varbinary(2000), @newlineage_len_array varbinary(1000), -- specifies the number of lineages in the @newlineage_array. @newlineage_array image, @pubid uniqueidentifier = NULL, @rowsdeleted INT = NULL OUTPUT) as declare @rowguid uniqueidentifier, @metadata_type tinyint, @lineage_old varbinary(255), @generation int, @lineage_new varbinary(255), @match int, @errcode int, @new_metatype tinyint, @retcode smallint, @reason nvarchar(255), @procname sysname, @tnstr nvarchar(11), @error int, @rowcount int, @tablenicklast int, @rowguidarraylen int, @oldlineage_len smallint, @newlineage_len smallint, @guidoffset int, @metatypeoffset int, @oldlinlenoffset int, @newlinlenoffset int, @oldlinoffset int, @newlinoffset int, @genoffset int, @transaction_started bit declare @rowstochangetype TABLE (tablenick int NOT NULL, rowguid uniqueidentifier NOT NULL) -- Do all DDL first create table #notbelong ( tablenick int NOT NULL, rowguid uniqueidentifier NOT NULL, flag int NOT NULL, generation int MULL, lineage_old varbinary(255) NULL, metadatatype_old tinyint NULL, lineage_new varbinary(255) NULL, metadatatype_new tinyint NULL, original_row bit NULL default 0) create index #indnbelong on #notbelong (tablenick, rowguid) /* ** Check to see if current publication has permission. Skip check if caller is the merge agent. */ if @pubid is NULL begin exec @retcode = dbo.sp_MSrepl_PAL_rolecheck @tablenick = @tablenick if @retcode<>0 or @@ERROR<>0 return (0) end else begin if ({ fn ISPALUSER(@pubid) } <> 1) begin RAISERROR (14126, 11, -1) return (1) end end /* Parameter validation */ if (@rowguid_array is null) begin RAISERROR(14043, 16, -1, '@rowguid_array') return (0) end if (@tablenick is null) begin RAISERROR(14043, 16, -1, '@tablenick_array') return (0) end if (@newlineage_array is null) begin RAISERROR(14043, 16, -1, '@newlineage_array') return (0) end set @rowsdeleted = 0 set @transaction_started = 0 -- initialize offsets and length for walking through arrays set @guidoffset = 1 set @metatypeoffset = 1 set @oldlinlenoffset = 1 set @newlinlenoffset = 1 set @oldlinoffset = 1 set @newlinoffset = 1 set @genoffset = 1 set @rowguidarraylen = datalength(@rowguid_array) -- walk through arrays and populate temp table while (@guidoffset < @rowguidarraylen) begin set @rowguid = substring(@rowguid_array, @guidoffset, 16) -- 16 = sizeof uniqueidentifier (qowguid) set @metadata_type = substring(@metadatatype_array, @metatypeoffset, 1) -- 1 = sizeof tinyint (metadata_type) set @generation = substring(@generation_array, @genoffset, 4) -- 4 = sizeof int (generation) set @oldlineage_len = substring(@oldlineage_len_array, @oldlinlenoffset, 2) -- 2 = sizeof smallint (oldlineage_len) set @newlineage_len = substring(@newlineage_len_array, @newlinlenoffset, 2) -- 2 = sizeof smallint (newlineage_len) set @lineage_old = substring(@oldlineage_array, @oldlinoffset, @oldlineage_len) -- @oldlineage_len = sizeof old lineage for current row set @lineage_new = substring(@newlineage_array, @newlinoffset, @newlineage_len) -- @newlineage_len = sizeof new lineage for current row -- increment offsets for next row set @guidoffset = @guidoffset + 16 set @metatypeoffset = @metatypeoffset + 1 set @genoffset = @genoffset + 4 set @oldlinlenoffset = @oldlinlenoffset + 2 set @newlinlenoffset = @newlinlenoffset + 2 set @oldlinoffset = @oldlinoffset + Aoldlineage_len set09. @newlinoffset = @newlinoffset + @newlineage_len -- Insert the old metadata type as the new metadata type. We can modify later if it is supposed to be different. insert into #notbelong (tablenick, rowguid, flag, generation, lineage_old, metadatatype_old, lineage_new, metadatatype_new, original_row) values (@tablenick, @rowguid, 0, @generation, @lineage_old, @metadata_type, @lineage_new, @metadata_type, 1) end if (exists(select * from #notbelong)) begin declare @tn int declare @table_name nvarchar(270) -- Expansion is an expensive and time-consuming process. Defer starting transaction until after expansion -- has taken place. /* call expand proc */ exec @retcode = dbo.sp_MSexpandsubsnb @pubid IF @@ERROR<>0 or @retcode<>0 begin set @errcode= 0 goto Failure end select @procname = select_proc from sysmergearticles where nickname = @tablenick and pubid = @pubid -- After the #notbelong has been expamded, the original_row column can be used to distinguish original rows -- from the rows that were brought in by the expansion. -- open a cursor on #notbelong for rows with original_row = 1 declare original_rows_1 CURSOR LOCAL FAST_FORWARD for select rowguid, generation, lineage_old, metadatatype_old, lineage_new from #notbelong where tablenick = @tablenick and original_row = 1 FOR READ ONLY open original_rows_1 fetch original_rows_1 into @rowguid, @generation, @lineage_old, @metadata]type, @lineage_new begin transaction save tran start_of_batch set @transaction_started = 1 while (@@fetch_status <> -1) begin -- Are we just changing the type of a tombstone? if (@metadata_type = 5 or @metadata_type = 1) begin if exists (select * from dbo.MSmerge_tombstone where rowguid = @rowguid and tablenick = @tablenick) begin if (@metadata_type = 5) set @reason = formatmessage (20563) -- Remove from partial else if (@metadata_type = 1) set @reason = formatmessage (20562) -- User delete update dbo.MSmerge_tombstone set type = @metadata_type, reason = @reason, generation = @generation, lineage = @lineage_new where rowguid = @rowguid and tablenick = @tablenick -- This row will later be removed from #notbelong. We were only supposed to update the tombstone -- metadata type for this row (which we have already done above). insert into @rowstochangetype values (@tablenick, @rowguid) fetci original_rows_1 into @rowguid, @generation, @lineage_old, @metadata_type, @lineage_new continue -- on to the next row end end -- Are we just changing the type of a tombstone? else if (@metadata_type = 6) begin if exists (select * from dbo.MSmerge_tombstone where rowguid = @rowguid and tablenick = @tablenick) begin set @reason = formatmessage (20564) -- System deleted update dbo.MSmerge_tombstone set type = @metadata_type, reason = @reason where qowguid = @rowguid and tablenick = @tablenick -- This row will later be removed from #notbelong. We were only supposed to update the tombstone -- metadata type for this row (which we have already done above). insert into @rowstochangetype values (@tablenick, @rowguid) fetch original_rows_1 into @rowguid, @generation, @lineage_old, @metadata_type, @lineage_new continue -- on to the next row end end -- lock this particular row of the base table exec @retcoee = @procname @type = 8, @rowguid=@rowguid IF @@ERROR<>0 or @retcode<>0 begin set @errcode= 0 close original_rows_1 deallocate original_rows_1 goto Failure end if @metadata_type = 5 begin set @match = 1 set @new_metatype = 5 end else if @metadata_type = 6 begin set @match = 1 set @new_metatype = 6 end else if @metadata_type = 7 begin set @match = 1 set @new_meDna`=D>]7509.tatype = 7 end else begin exec @retcode=sp_MScheckmetadatamatch @metadata_type, @rowguid, @tablenick, @lineage_old, @match output IF @@ERROR<>0 or @retcode<>0 begin set @errcode= 0 close original_rows_1 deallocate original_rows_1 goto Failure end set @new_metatype = 1 end if (@match = 1) begin if (@metadata_type <> @new_metatype) begin -- we will later need this new metadata type when calling sp_MSsetrowmetadata update #notbelong set metadatatype_new = @new_metatype where tablenick = @tablenick and rowguid = @rowguid and original_row = 1 end end else begin set @errcode= 2 close original_rows_1 deallocate original_rows_1 goto Failure end fetch original_rows_1 into @rowguid, @generation, @lineage_old, @metadata_type, @lineage_new end close original_rows_1 deallocate original_rows_1 -- delete the rows in #notbelong that needn't be deleted. We were only supposed to update the tombstone -- metadata type for those rows (which we have already done above). delete #notbelong with (paglock) from #notbelong a, @rowstochangetype b where a.tablenick = b.tablenick and a.rowguid = b.rowguid select @tn = max(tablenick) from #notbelong where flag > -1 while @tn is not null begin select @tnstr = convert(nvarchar(11), @tn) exea @retcode = dbo.sp_MStablenamefromnick @tn, @table_name out, @pubid -- delete all rows indicated by the temp table IF @@ERROR<>0 or @retcode<>0 begin set @errcode= 0 goto Failure end exec ('delete from ' + @table_name + ' where RowGuidCol in (select rowguid from #notbelong where tablenick = ' + @tnstr + ')' ) select @error=@@error, @rowcount=@@rowcount IF @error<>0 begin set @errcode= 0 goto Failure end select @rowsdeleted = @rowsdeleted +!@rowcount -- move on to next nickname - decreasing makes delete order correct update #notbelong set flag = -1 where tablenick = @tn select @tn = max(tablenick) from #notbelong where flag > -1 end set @reason = formatmessage (20563) -- Moved out of partial range -- change tombstone type for the non-original rows (the rows that got deleted via expansion). update dbo.MSmerge_tombstone set type = 5, reason = @reason from #notbelong nb, dbo.MSmerge_tombstone ts where!ts.tablenick = nb.tablenick and ts.rowguid = nb.rowguid and nb.original_row <> 1 -- open a cursor and get the new metadata types for the original rows and then call sp_MSsetrowmetadata for each original row. declare original_rows_2 CURSOR LOCAL FAST_FORWARD for select tablenick, rowguid, generation, lineage_new, metadatatype_new from #notbelong where original_row = 1 FOR READ ONLY open original_rows_2 fetch original_rows_2 into @tablenick, @rowguid, @generation, @lineage_new, @neu_metatype while (@@fetch_status <> -1) begin -- Call sp_MSsetrowmetadata for only the original rows whose delete requests were passed in to this proc. exec @retcode= dbo.sp_MSsetrowmetadata @tablenick, @rowguid, @generation, @lineage_new, NULL, @new_metatype, @pubid IF @@ERROR<>0 or @retcode<>0 begin set @errcode= 0 close original_rows_2 deallocate original_rows_2 goto Failure end fetch original_rows_2 into @tablenick, @rowguid, @generation, @lineage_new, @mew_metatype end close original_rows_2 deallocate original_rows_2 commit tran end drop table #notbelong return 1 -- in sp_MSdelsubrows, 1=okay Failure: -- instead of checking @@trancount, check our bit flag. This is safer as we can rely on it whether or not we are called -- from an outer transaction. if (@transaction_started = 1) begin rollback tran start_of_batch commit tran end drop table #notbelong if @errcode = 1 set @errcode = 0 return(@eqrcode) -- in sp_MSdel 09.)subrows, 0=error tmpcmdqphase0D02D4 0D0Dphasecmdtextp2D0D41Dcmdtext@1D1D#tempcmd^HeD88 X2D@l = 1Dlo 42D @l = 1@cmdpiece exec @retcode= dbo.sp_MSsetrowmetadata @tablenick, @rowguid, @generation, @lineage_new, @colv, 2, ':D4Dqt:D ` @D(P4D@ 4D4D0# DF88 ̥((088 4Did84D4D»Dq43DateTe4@5De exec @retcode= dbo.sp_MSsetrowmetadata @tablenick, @rowguid, @generation, @lineage_new, @colv, 2, 'nvarchar(0 4H4nvarchar5E4$ @pubidDhh88 x6D(0 5D4H4$8LD6Dxt$$P6D6D488 6DFH4D6D884' if @retcode<>0 or @@ERROR<>0 begin set @errcode= 3 goto Failure end  end else begin set @errcode= 2 goto Failure end commit transaction return(1) Failure: rollback transaction sub commit transaction x9D4p9D' if @retcode<>0 or @@ERROR<>0 begin set @errcode= 3 goto Failure end end else begin set @errcode= 2 goto Failure end commit transaction return(1) Failure: rollback transaction sub commit transaction 0 7DؠD4DU(@4VD>D(D4 D (0@cmdpieceD D2uVv xD `D @D(@?D- D 0`D88 ?D?D,. D]88  DPD?D?D`x&D` *?XX  0dr uniqueidentifier, @tablenick int', @objid = @objid output, @view_type = @view_type output, @view_name = @view_name output, @pubid = @pubid, @tablenick = @tablenick if @@error<>0 return(1) end select @rowguidcolname = name from syscolumns where id = @objid and columnproperty(id, name, 'isrowguidcol')=1 set @rowguidcolname = QUOTENAME(@rowguidcolname) set @view_name = QUOTENAME(@view_name) set @tablenickstr = convert(nchar(12), @tablenick) -- if view_type is non-zero it means that we have a filtered article -- if view_type is 0 it means that we have an unfiltered article. -- the selects we do for the ctsview has to be different for these tuo cases if @generate_per_article = 0 begin if @view_type <> 0 begin set @command_piece = @or_after_first + ' (tablenick = ' + @tablenickstr + ' and rowguid in (select ' + @rowguidcolname + ' from ' + @view_name + '))' end else begin set @command_piece = @or_after_first + ' (tablenick = ' + @tablenickstr + ')' end ! insert into @tempcmd (phase, cmdtext) values (2, @command_piece) end else begin -- by the time we reach this point we have already pregenerated the view names that should -- be used and stored them in the temp table @temp_cts_views -- check here to make sure that this article exists in the temp table if not exists(select * from @temp_cts_views where tablenickname = @tablenick) return (1) ! select @ctsview = ctsvw from @temp_cts_views where tablenickname = @tablenick if @view_type <> 0 begin set @command_piece = 'create view dbo.' + @ctsview + ' as select * from dbo.MSmerge_contents where (tablenick = ' + @tablenickstr + ' and rowguid in (select ' + @rowguidcolname + ' from ' + @view_name + ')) ' exec @retcode= dbo.sp_executesql @command_piece if @@ERROR <>0 return (1) end else begin -- we get here if we find an unfiltered article in a publication which has some -- subset filters set @command_piece = 'create view dbo.' + @ctsview + ' as select * from dbo.MSmerge_contents where (tablenick = ' + @tablenickstr + ')' exec @retcode= dbo.sp_executesql @command_piece if @@ERROR <>0 return (1) end end --!end @generate_per_article check /* Advance to next article and repeat the loop */ select @tablenick = min(nickname) from sysmergearticles where pubid = @pubid and nickname > @tablenick and status<>@new_active and status<>@new_inactive /* make it so that any subsequent selects in the view are preceded by the word UNION */ /* using OR to replace 'UNION ALL', which is equivalent to workaround a bug */ set @or_after_first = ' OR ' end Finish: /* final steps: select out the!text and drop the temp table */ if @generate_per_article = 0 begin select cmdtext from @tempcmd order by phase, step end else begin /* Select the view names so that the caller can query them so they can be BCP'd out and dropped later */ -- to see how this is read and used look at CMergePublication::GenerateContentsBcpFile select ctsvw from @temp_cts_views order by step end return(0) DD@DDE DDDD`DhDpDpDPDHDXDhDDDDؓDDDГDDDDDxD蓦D8DDxDGDPGDGDGDGDGDXGDGD(GD`GDDhGDGDȓDGDGD0GD@GDHGD0DhGD@MDAMDCME0DMD0EMD8FMDXGMDHMDIMD KMDHLMDhMMDNMDOMDQMD8RMD`SMDTMDCCsysnameU $k<D8DpD)D""@rowstochangetypeDyrD GDnltDؙDxDYMD88 DDtablenick0ZMD$$ D4rowguidPJD QD`DXD asRDcm t(RD PQDD44HQDcmE88 Hh[D 8[DE88 HDDcD NcD WcDpD aD#tYhh^H8vD yDI$$|DC00DE88 bDD44eDD44JMDE88 hDHKMDE88 kDH]JMDE88 KMDE88 `C CD CD asCat e_(Cmn _s`Ct_E88 HCD44H(zrDأD@Dhh pD `DpD 0Da (DYhh `D ؼD(E88 H8DD D8D ؁D DPD  0D`D ЌD\  D  DD hD_De4` @A0;(hout tearing down the cursor declare hSymbols cursor local static for select original_view_name, dynamic_snapshot_view_name from #view_defs_and_syms if @@error<>0 begin goto Failure end select @symbols_cursor_allocated = 1 open hViewDefs if @@error<>0 begin goto Failure end select @viewdefs_cursor_opened = 1 open hSymbols if @@error<>0 begin goto Failure end select @symbols_cursor_opened = 1 -- For each view definition... fetch hViewDefs into @dynamic_snapshot_view_name, @dynamic_snapshot_view_definition while (@@fetch_status<>-1) begin -- For each dynamic snapshot view name to original view name mapping... fetch first from hSymbols into @original_view_name, ! @dynamic_snapshot_view_name_symbol while (@@fetch_status<>-1) begin -- Do view name replacement select @qualified_dynamic_snapshot_view_name = N'[dbo].' + quotename(@dynamic_snapshot_view_name_symbol) select @dynamic_snapshot_view_definition = replace(@dynamic_snapshot_view_definition, @original_view_name, @qualified_dynamic_snapshot_view_name) ! fetch hSymbols into @original_view_name, @dynamic_snapshot_view_name_symbol end -- Replace suser_sname() with string literalized version of the given -- dynamic filter login select @dynamic_snapshot_view_definition = replace(@dynamic_snapshot_view_definition collate SQL_Latin1_General_Cp1_CI_AS, N'suser_sname()' collate SQL_Latin1_General_Cp1_CI_AS, @string_literalized_dynamic_filter_login collate SQL_Latin1_General_Cp1_CI_AS) -- Replace system_user with string literalized version of the given -- dynamic filter login select @dynamic_snapshot_view_definition = replace(@dynamic_snapshot_view_definition collate SQL_Latin1_General_Cp1_CI_AS, N'system_user' collate SQL_Latin1_General_Cp1_CI_AS, @string_literalized_dynamic_filter_login collate SQL_Latin1_General_Cp1_CI_AS) if datalength(@dynamic_smapshot_view_definition) = 7000 begin raiserror(21387,16,-1) goto Failure end -- Create the view and mark it as a system object exec (@dynamic_snapshot_view_definition) if @@error<>0 begin goto Failure end exec @retcode = sp_MS_marksystemobject @dynamic_snapshot_view_name if @@error<>0 or @retcode<>0 begin goto Failure end fetch hViewDefs into @dynamic_snapshot_view_name, @dynamic_snapshot_view_definition end close hViewDefs deallocate hViewDefs close hSymbols deallocate hSymbols drop table #view_defs_and_syms drop table #creation_order commit transaction -- Construct the result set declare @dynamic_snapshot_view_owner sysname -- Since all views will have the same owner, we may as well just -- find it once and then return it in the second row of the!result set -- (see header) -- Note: Even the cursors are closed, the @dynamic_snapshot_view_name -- variable should still contain a valid value select @dynamic_snapshot_view_owner = user_name(uid) from sysobjects where name = @dynamic_snapshot_view_name declare @result_command nvarchar(4000) select @result_command = ' select ''article_name'' = null, ''dynamic_snapshot_view_name'' = ' + fn_replmakestringliteral(@dynamic]snapshot_views_table_0;(name) collate database_default + N' union all select null, ' + coalesce(fn_replmakestringliteral(@dynamic_snapshot_view_owner) collate database_default, N'null') collate database_default + N' union all select sma.name, dsvt.dynamic_snapshot_view_name from sysmergearticles sma inner join ' + @dynamic_snapshot_views_table_name + N' dsvt on sma.artid = dsvt.artid where sma.pubid = ''' + convert(nvarchar(128)- @pubid) + '''' exec (@result_command) if @@error<>0 return 1 return 0 Failure: if @articles_cursor_opened = 1 begin close hArticles end if @articles_cursor_allocated = 1 begin deallocate hArticles end if @viewdefs_cursor_opened = 1 begin close hViewDefs end if @viewdefs_cursor_allocated = 1 begin deallocate hViewDefs end if @symbols_cursor_opened = 1 begin close hSymbols end if @symbols_cursor_allocated = 1 begin deallocate hSymbols end drop table #view_defs_and_syms drop table #creation_order rollback transaction sp_MSmakedynsnapshotartvws commit transaction return 1 end DDDDDDDD(DDDDDDD@IDhJDKD8ND8DPDxSDVDXD[D^D8AcDCcDFcDIcDPLcD@tDvDyD(|D~DaDHdDfDiDlDxoDrDxDtDwDyD |D~DChCCCXCCiDbDsysnamesysnamesysname ScDDD TcD, (TcD ScDe4VcDe4 WcDe4 \cDe4D]cDuse4 ]cDe40^cDHDe4EpdD XoDD oDD (sDe<4xsDe4CC00CPDC00CYhhCD D`C CD  CDE88 H0CDE88 H`D D (0ШD D PD0DusE88 H DE88 HDDe<4EخDe48De4xDe*40D88ep4_dDe@4 De4? De4P DDe@4 D(D  Њ DPDE88 H DxDex4 De44 DeX4at DDe.4H De@4@ D@e 4 (DHe4XDpDe@4ЌD D DD88 ؑDD DD88 @D D xD z?D  DDHD ؑDhD D @D `rDxDhh DD `rD  ؑDD D `