[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

replication monitoring undelivered commands

Hello,

In this script, I try to monitore undelivered commands, but I see that this query in the script doesn't return distinct rows :
DECLARE @sql3 VARCHAR(MAX)

            SET @sql3 = ' INSERT INTO [msdb].[dbo].[test_replcmds_distrib]( [ArticleSource],[Publication],[AgentReplication], [UndelivCmdsInDistDB],[DelivCmdsInDistDB], [DateAnomalie])  select distinct a.article,p.publication,SUBSTRING(agents.[name], 16, 35) AS [Name],s.UndelivCmdsInDistDB,s.DelivCmdsInDistDB, getdate() from [' + @SrvDistributeur + '].[' + @DbDistributeur +
                        '].[dbo].[MSdistribution_status]s INNER JOIN [' +
                        @SrvDistributeur + '].[' + @DbDistributeur +
                        '].[dbo].MSdistribution_agents AS agents ON agents.[id] = s.agent_id INNER JOIN ['
                                    +
                              @SrvDistributeur +
                    '].[' + @DbDistributeur + '].[dbo].MSpublications AS p ON p.publication = agents.publication INNER JOIN [' + @SrvDistributeur +
                        '].['
                        +
                    @DbDistributeur + '].[dbo].MSarticles AS a ON a.article_id = s.article_id and p.publication_id = a.publication_id WHERE 1=1 AND s.UndelivCmdsInDistDB <> 0 AND agents.subscriber_db NOT LIKE ''virtual'' ORDER BY s.UndelivCmdsInDistDB DESC'


Here the script :
CREATE PROCEDURE [dbo].[monitoring_zone]

AS 
DECLARE @SrvSource varchar (30)
DECLARE @SrvAbonne varchar (30); 
DECLARE @DbSource varchar(30); 
DECLARE @SchSource varchar(30); 
DECLARE @Publication varchar(30); 
DECLARE @ArticleSource varchar(30); 
DECLARE @DbCible varchar(30);
DECLARE @SchCible varchar (30);
DECLARE @ArticleCible varchar(30);
DECLARE @SrvDistributeur VARCHAR (30); 
DECLARE @DbDistributeur VARCHAR (30);  
DECLARE @Statement nvarchar(max);
DECLARE @compteur integer;
declare @ErrMestest NVARCHAR(2047)
			,@ErrNumber INT
			,@ErrSeverity TINYINT
			,@ErrState TINYINT;
  

BEGIN TRY
IF EXISTS (SELECT 1
             FROM   sysobjects
             WHERE  id = OBJECT_ID('dbo.test_anomalies_zone'))
    TRUNCATE TABLE dbo.test_anomalies_zone
    TRUNCATE TABLE dbo.test_anomalies_repl
	TRUNCATE TABLE dbo.test_replcmds_distrib

SET @SrvSource = @SrvSource
SET @SrvAbonne=@SrvAbonne 
SET @DbSource= @DbSource 
SET @SchSource = @SchSource
SET @Publication = @Publication
SET @ArticleSource = @ArticleSource 
SET @DbCible = @DbCible 
SET @SchCible = @SchCible
SET @ArticleCible = @ArticleCible 
SET @SrvDistributeur= @SrvDistributeur 
SET @DbDistributeur= @DbDistributeur 
SET @compteur=0

		
					IF Cursor_Status('global', 'parcours_serv_replaccounting') >= 1
						BEGIN
							CLOSE parcours_serv_replaccounting
							DEALLOCATE parcours_serv_replaccounting
						END

					DECLARE parcours_serv_replaccounting CURSOR FAST_FORWARD FOR SELECT distinct ServeurSource as SrvSource, ServeurDestination as SrvAbonne
					FROM master.dbo.test_Ref_Replaccounting

					OPEN parcours_serv_replaccounting 
					FETCH NEXT FROM parcours_serv_replaccounting INTO @SrvSource, @SrvAbonne					
					WHILE @@FETCH_STATUS = 0
						BEGIN
							SET QUOTED_IDENTIFIER OFF;

IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 
AND srv.name = @SrvSource)
EXEC master.dbo.sp_dropserver @server=@SrvSource, @droplogins='droplogins'

IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 
AND srv.name = @SrvAbonne)
EXEC master.dbo.sp_dropserver @server=@SrvAbonne, @droplogins='droplogins'

IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 
AND srv.name = @SrvDistributeur)
EXEC master.dbo.sp_dropserver @server=@SrvSource, @droplogins='droplogins'

---Création d'un serveur lié pour accéder au serveur éditeur
EXEC master.dbo.sp_addlinkedserver @server = @Srvsource, @srvproduct=N'SQL Server'

declare @sql nvarchar(max);
set @sql = 'EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname =N''' + @SrvSource + ''', @useself=N''True'',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL' 
exec sp_executesql @sql

EXEC master.dbo.sp_serveroption @server=@SrvSource, @optname=N'collation compatible', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@SrvSource, @optname=N'data access', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@SrvSource, @optname=N'dist', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@SrvSource, @optname=N'pub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@SrvSource, @optname=N'rpc', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@SrvSource, @optname=N'rpc out', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@SrvSource, @optname=N'sub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@SrvSource, @optname=N'connect timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=@SrvSource, @optname=N'collation name', @optvalue=null

EXEC master.dbo.sp_serveroption @server=@SrvSource, @optname=N'lazy schema validation', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@SrvSource, @optname=N'query timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=@SrvSource, @optname=N'use remote collation', @optvalue=N'true'

EXEC master.dbo.sp_addlinkedserver @server = @SrvAbonne, @srvproduct=N'SQL Server'
declare @sql1 nvarchar(max);
set @sql1 = 'EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname =N''' + @SrvAbonne + ''', @useself=N''True'',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL' 
exec sp_executesql @sql1

EXEC master.dbo.sp_serveroption @server=@SrvAbonne, @optname=N'collation compatible', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@SrvAbonne, @optname=N'data access', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@SrvAbonne, @optname=N'dist', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@SrvAbonne, @optname=N'pub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@SrvAbonne, @optname=N'rpc', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@SrvAbonne, @optname=N'rpc out', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@SrvAbonne, @optname=N'sub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@SrvAbonne, @optname=N'connect timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=@SrvAbonne, @optname=N'collation name', @optvalue=null

EXEC master.dbo.sp_serveroption @server=@SrvAbonne, @optname=N'lazy schema validation', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@SrvAbonne, @optname=N'query timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=@SrvAbonne, @optname=N'use remote collation', @optvalue=N'true'

FETCH NEXT FROM parcours_serv_replaccounting INTO @SrvSource, @SrvAbonne
END
IF Cursor_Status('global', 'parcours_serv_replaccounting ') >= 1
		BEGIN
			CLOSE parcours_serv_replaccounting
			DEALLOCATE parcours_serv_replaccounting
		END
IF Cursor_Status('global', 'parcours_distributeur_replaccounting') >= 1
						BEGIN
							CLOSE parcours_distributeur_replaccounting
							DEALLOCATE parcours_distributeur_replaccounting
						END

					DECLARE parcours_distributeur_replaccounting CURSOR FAST_FORWARD FOR SELECT distinct ServeurDistributeur as SrvDistributeur
					FROM master.dbo.test_Ref_Replaccounting

					OPEN parcours_distributeur_replaccounting 
					FETCH NEXT FROM parcours_distributeur_replaccounting INTO @SrvDistributeur					
					WHILE @@FETCH_STATUS = 0
						BEGIN
							SET QUOTED_IDENTIFIER OFF;
IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 
AND srv.name = @SrvDistributeur)
EXEC master.dbo.sp_dropserver @server=@SrvDistributeur, @droplogins='droplogins'

EXEC master.dbo.sp_addlinkedserver @server = @SrvDistributeur, @srvproduct=N'SQL Server'
declare @sql2 nvarchar(max);
set @sql2 = 'EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname =N''' + @SrvDistributeur + ''', @useself=N''True'',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL' 
exec sp_executesql @sql2

EXEC master.dbo.sp_serveroption @server=@SrvDistributeur, @optname=N'collation compatible', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@SrvDistributeur, @optname=N'data access', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@SrvDistributeur, @optname=N'dist', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@SrvDistributeur, @optname=N'pub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@SrvDistributeur, @optname=N'rpc', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@SrvDistributeur, @optname=N'rpc out', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=@SrvDistributeur, @optname=N'sub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@SrvDistributeur, @optname=N'connect timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=@SrvDistributeur, @optname=N'collation name', @optvalue=null

EXEC master.dbo.sp_serveroption @server=@SrvDistributeur, @optname=N'lazy schema validation', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=@SrvDistributeur, @optname=N'query timeout', @optvalue=N'0'

EXEC master.dbo.sp_serveroption @server=@SrvDistributeur, @optname=N'use remote collation', @optvalue=N'true'

FETCH NEXT FROM parcours_distributeur_replaccounting INTO @SrvDistributeur
END
IF Cursor_status('global', 'parcours_replcmd_distributeur') >= 1 
        BEGIN 
            CLOSE parcours_replcmd_distributeur 

            DEALLOCATE parcours_replcmd_distributeur 
        END 
DECLARE parcours_replcmd_distributeur CURSOR FAST_FORWARD FOR 
        SELECT serveurdistributeur AS srvdistributeur, 
               basedistributeur    AS dbdistributeur 
        FROM   MASTER.dbo.test_ref_replaccounting 

      OPEN parcours_replcmd_distributeur 

      FETCH NEXT FROM parcours_replcmd_distributeur INTO @SrvDistributeur, @DbDistributeur 

      WHILE @@FETCH_STATUS = 0 
        BEGIN 
            DECLARE @sql3 VARCHAR(MAX) 

            SET @sql3 = ' INSERT INTO [msdb].[dbo].[test_replcmds_distrib]( [ArticleSource],[Publication],[AgentReplication], [UndelivCmdsInDistDB],[DelivCmdsInDistDB], [DateAnomalie])  select distinct a.article,p.publication,SUBSTRING(agents.[name], 16, 35) AS [Name],s.UndelivCmdsInDistDB,s.DelivCmdsInDistDB, getdate() from [' + @SrvDistributeur + '].[' + @DbDistributeur + 
                        '].[dbo].[MSdistribution_status]s INNER JOIN [' + 
                        @SrvDistributeur + '].[' + @DbDistributeur + 
                        '].[dbo].MSdistribution_agents AS agents ON agents.[id] = s.agent_id INNER JOIN [' 
						+ 
					@SrvDistributeur + 
                    '].[' + @DbDistributeur + '].[dbo].MSpublications AS p ON p.publication = agents.publication INNER JOIN [' + @SrvDistributeur + 
                        '].[' 
                        + 
                    @DbDistributeur + '].[dbo].MSarticles AS a ON a.article_id = s.article_id and p.publication_id = a.publication_id WHERE 1=1 AND s.UndelivCmdsInDistDB <> 0 AND agents.subscriber_db NOT LIKE ''virtual'' ORDER BY s.UndelivCmdsInDistDB DESC' 

            PRINT ( @sql3 ) 

            EXECUTE (@sql3) 

            FETCH NEXT FROM parcours_replcmd_distributeur INTO @SrvDistributeur, 
            @DbDistributeur 
        END 
IF Cursor_status('global', 'parcours_replcmd_distributeur ') >= 1 
        BEGIN 
            CLOSE parcours_replcmd_distributeur 

            DEALLOCATE parcours_replcmd_distributeur 
        END 
IF Cursor_Status('global', 'parcours_articles_replaccounting') >= 1
						BEGIN
							CLOSE parcours_articles_replaccounting
							DEALLOCATE parcours_articles_replaccounting
						END

					DECLARE parcours_articles_replaccounting CURSOR FAST_FORWARD FOR SELECT ServeurSource as SrvSource, BaseSource as DbSource,dossierSource as SchSource, NomPublication as NomPublication, NomArticle as ArticleSource, 
					ServeurDestination as SrvAbonne,BaseAbonne as DbCible, dossierDestination as SchCible, ArticleDestination as ArticleCible 
					FROM master.dbo.test_Ref_Replaccounting
					OPEN parcours_articles_replaccounting 
					FETCH NEXT FROM parcours_articles_replaccounting INTO @SrvSource, @DbSource,@SchSource,@Publication, @ArticleSource , @SrvAbonne, @DbCible, @SchCible, @ArticleCible
					WHILE @@FETCH_STATUS = 0
						BEGIN


declare @sql5 varchar(max)
set @sql5 = '
INSERT INTO [msdb].[dbo].[test_anomalies_zone]( [ZROWID_0],[ServeurSource],[DbSource], [SchSource], [Publication],[ArticleSource], [DateAnomalie]) 
select a.[ZROWID_0]   , '''+@SrvAbonne+''', '''+@DbCible+''' , '''+@SchCible+''', '''+@Publication+''', '''+@ArticleCible+''', getdate()
from ['+@SrvAbonne + '].['+ @DbCible + '].['+ @SchCible + '].['+ @ArticleCible + ']a
where a.[ZORIDOS_0] = '''+@Schsource+''' and not exists (select  1 from ['+@SrvSource + '].['+ @DbSource + '].['+ @SchSource + '].['+ @ArticleSource + ']s)
and not exists (select distinct '''+@ArticleSource+''' from msdb.dbo.test_replcmds_distrib d where publication = '''+@Publication+''')'

print (@sql5)
execute (@sql5)

FETCH NEXT FROM parcours_articles_replaccounting INTO @SrvSource, @DbSource,@SchSource, @Publication, @ArticleSource , @SrvAbonne, @DbCible, @SchCible, @ArticleCible

END

IF Cursor_Status('global', 'parcours_articles_replaccounting ') >= 1
		BEGIN
			CLOSE parcours_articles_replaccounting
			DEALLOCATE parcours_articles_replaccounting
		END

END TRY

BEGIN CATCH
	---Copie du détail de l'erreur dans des variables locales
  
	select @ErrMestest=ERROR_MEStest() + N' #' + 
				CAST(ERROR_NUMBER() AS NVARCHAR(10))
				,@ErrNumber=ERROR_NUMBER()
				,@ErrSeverity=ERROR_SEVERITY()
                ,@ErrState=ERROR_STATE();

    --Re-raise l'erreur d'origine
    RAISERROR(@ErrMestest,@ErrSeverity,@ErrState) WITH LOG;   
END CATCH

Open in new window


How can I modify it? I think there is a problem in:
INSERT INTO [msdb].[dbo].[test_anomalies_zone]( [ZROWID_0],[ServeurSource],[DbSource], [SchSource], [Publication],[ArticleSource], [DateAnomalie])
select a.[ZROWID_0]   , '''+@SrvAbonne+''', '''+@DbCible+''' , '''+@SchCible+''', '''+@Publication+''', '''+@ArticleCible+''', getdate()
from ['+@SrvAbonne + '].['+ @DbCible + '].['+ @SchCible + '].['+ @ArticleCible + ']a
where a.[ZORIDOS_0] = '''+@Schsource+''' and not exists (select  1 from ['+@SrvSource + '].['+ @DbSource + '].['+ @SchSource + '].['+ @ArticleSource + ']s)
and not exists (select distinct '''+@ArticleSource+''' from msdb.dbo.test_replcmds_distrib d where publication = '''+@Publication+''')'


Thanks

Regards

bibi
0
bibi92
Asked:
bibi92
1 Solution
 
lcohanDatabase AnalystCommented:
did you tried without the "GETDATE()" in the first SELECT DISTINCT you posted above? That returns different values for each record in your record set and implicitely will make that particular row as DISTINCT - just drop the column from select see what you get.
To fix that you will need a GETDATE() without the time portion in my opinion.
0
 
bibi92Author Commented:
Thanks bibi
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now