Google Custom Search

Monday, March 23, 2009

Script to Validate Subscribers for all articles and publication

declare @pubdb nvarchar(50)
declare @artobj nvarchar(100)
declare @owner nvarchar(50)
declare @params nvarchar(100)
declare @cnt1 nvarchar(50)
declare @cmd nvarchar(1000)
declare @cmd1 nvarchar(1000)
DECLARE pub_validate CURSOR FOR
select publisher_db,source_owner,source_object from distribution.dbo.MSarticles
open pub_validate
fetch next from pub_validate into @pubdb,@owner,@artobj
while @@fetch_status=0
begin
select @cmd = 'select @cnt=count(*) from '+@pubdb+'.'+@owner+'.'+@artobj
select @params=N'@cnt varchar(100) OUTPUT'
exec sp_executesql @cmd,@params,@cnt=@cnt1 output
--select @cnt1
select @cmd1='use '+@pubdb+CHAR(10)+'go'+CHAR(10)+'sp_table_validation @table='+@artobj+',@expected_rowcount='+@cnt1
select @cmd1
--exec sp_executesql @cmd1
fetch next from pub_validate into @pubdb,@owner,@artobj
end
close pub_validate
deallocate pub_validate

No comments: