November 14, 2023 at 5:12 am
Hi All,
I am trying to get table row count. Looks like it not not moving to next table.
Can anyone advise.
USE [DBAUtil]
GO
GO
/*
USE [DBAUtil]
GO
drop table [Tbl_Repl_Rowcount]
go
CREATE TABLE [dbo].[Tbl_Repl_Rowcount](
[PublisherName] [varchar](200) NOT NULL,
[Table_name] [varchar](400) NOT NULL
,[Row_Count] [int] NULL
)
select * from Tbl_Repl_Rowcount
*/
use DBAUtil
go
alter PROCEDURE [dbo].[USP_Repl_row_count_loop]
AS
BEGIN
SET nocount ON
-- select * from DBAUtil.dbo.Tbl_Repl_Rowcount
Truncate table DBAUtil.dbo.Tbl_Repl_Rowcount
DECLARE @Pub_name SYSNAME
DECLARE @Tbl_name SYSNAME
DECLARE @sql varchar(8000)
DECLARE @sql_cmd varchar(8000)
DECLARE @minrow int
DECLARE @maxrow int
declare @repl_info table (id int primary key identity,
PublisherName varchar(500),TableName varchar(500))
insert into @repl_info
select top 10
sp.name as PublisherName
, sa.name as TableName
--, 'select count(*) as [Row_Count] from HDXDB.dbo.['+sa.name+'] with (nolock)' as count_cmd
from hdxdb.dbo.syspublications sp
join hdxdb.dbo.sysarticles sa on sp.pubid = sa.pubid
join hdxdb.dbo.syssubscriptions s on sa.artid = s.artid
join master.dbo.sysservers srv on s.srvid = srv.srvid
where sp.name ='XX'
order by sa.name
SELECT @minrow = MIN(id)FROM @repl_info
SELECT @maxrow = MAX(id) FROM @repl_info
while (@minrow <=@maxrow)
begin
begin try
select @Pub_name=PublisherName ,
@tbl_name=TableName
from @repl_info where ID = @minrow
set @sql=
'EXEC(''DECLARE @sql_cmd varchar(8000);
select @SQL_cmd=''''''''select top 1 '''''+@Pub_name+''''' as [PublisherName],'''''+@tbl_name+''''' as [Table_name]
,count(*) as [Row_Count]
from hdxdb.dbo.syspublications sp
join hdxdb.dbo.sysarticles sa on sp.pubid = sa.pubid
join hdxdb.dbo.syssubscriptions s on sa.artid = s.artid
join master.dbo.sysservers srv on s.srvid = srv.srvid
where sp.name =''''XXX''''
'')'
insert into dbautil.dbo.Tbl_Repl_Rowcount
exec(@sql)
--print @SQL
end try
BEGIN CATCH
SELECT 'Rpl_Row_count',[Error_Line] = ERROR_LINE(),[Error_Number] = ERROR_NUMBER(),
[Error_Severity] = ERROR_SEVERITY(),[Error_State] = ERROR_STATE(),
[Error_Message] = ERROR_MESSAGE(),GETDATE()
END CATCH
set @minrow =@minrow +1
end
END
November 14, 2023 at 2:21 pm
I'm not sure what you mean by "moving onto the next table" but looking at your script, I suspect that your script is running and never ending, right? Your while loop never updates the @minrow or @maxrow variables, so as long as it hits the loop once, those values never update so your loop runs forever. You will need to update one or both of those values so that your while loop can terminate.
What I mean is if @minrow is 1 and @maxrow is 3, the loop will keep going with 1 and 3 for those as you never update @minrow. You will need to update @minrow by 1 in my scenario, but that MAY not be what you expect in your scenario. It COULD be that @minrow is 1 and @maxrow is 100000 even though you are only grabbing 10 rows. You will need to determine how to increment the value.
Now, with the above being said, I see no benefit in making that dynamic SQL; especially dynamic SQL INSIDE dynamic SQL... that just feels crazy... Why are you making dynamic SQL inside your dynamic SQL? And why use dynamic SQL at all for that? And why use the "TOP 1" when only 1 row will be returned by your query?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 15, 2023 at 1:36 am
Hi,
I was trying some test. I wanted to get all replicated tables count to inserted to another table per publication. So that, I will cross check with publisher and subscriber.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply