use activepos_read
GO

/***********************************************************************************
* Dropping the subscriptions on the publisher and  drop the publication            *
* ---------------------------------------------------------------------------------*
* This file was previously moved to the "Master" database sequence but is now      *
* again at the beginning of the ActivePos_read sequence. This is OK now because    *
* there is only one connection active to the database with the new system. Before  *
* the database was locked by the second connection (with transaction) and the      *
* script was not working properly.                                                 *
***********************************************************************************/
/*  Stops the log reader agent before droping the publication to avoid concurrency errors. */
DECLARE @jobId  BINARY(16) ,
 @jobName VARCHAR(200) ,
 @status INT

 /* Need to stop all ActivePos log reader agents possible orphan agents */
SELECT TOP 1
    @jobId = job_id,
    @jobName = j.name
FROM msdb.dbo.sysjobs j
    inner join msdb.dbo.syscategories c
        ON c.category_id = j.category_id
WHERE c.name like 'REPL-LogReader'
      and j.name like '%-ActivePos_read-%'
ORDER BY j.date_created DESC

SET @status=(select top 1 run_status from msdb.dbo.sysjobhistory where job_id=@jobID order by instance_id desc)

IF @jobName like '%-ActivePos_read-%' 
 AND EXISTS (SELECT 1
               FROM msdb.dbo.sysjobactivity AS sja
              INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
              WHERE sj.name = @jobName 
				AND sja.start_execution_date IS NOT NULL
                AND sja.stop_execution_date IS NULL)
		EXEC msdb.dbo.sp_stop_job @jobName
GO
	
/* Stop custom log reader agent if running */
if EXISTS (   SELECT ja.job_id,
                     j.name AS job_name,
                     ja.start_execution_date,
                     ISNULL(last_executed_step_id, 0) + 1 AS current_executed_step_id,
                     Js.step_name
                FROM msdb.dbo.sysjobactivity ja
                LEFT JOIN msdb.dbo.sysjobhistory jh
                  ON ja.job_history_id                       = jh.instance_id
                JOIN msdb.dbo.sysjobs j
                  ON ja.job_id                               = j.job_id
                JOIN msdb.dbo.sysjobsteps js
                  ON ja.job_id                               = js.job_id
                 AND ISNULL(ja.last_executed_step_id, 0) + 1 = js.step_id
               WHERE ja.session_id = (   SELECT TOP 1 session_id
                                           FROM msdb.dbo.syssessions
                                          ORDER BY agent_start_date DESC)
                 and j.name        = 'DR00470 - ActivePos_read Log reader'
                 AND start_execution_date is not null
                 AND stop_execution_date is null)
    EXEC msdb.dbo.sp_stop_job N'DR00470 - ActivePos_read Log reader'
GO 
    
/* Dropping the subscriptions on the publisher
   Added a parameter to ignore distributor */

/* DROP custom distribution agents */
DECLARE @JobName VARCHAR(100);

DECLARE c_job CURSOR LOCAL FORWARD_ONLY STATIC FOR
SELECT name
  FROM msdb.dbo.sysjobs
 WHERE name LIKE 'ActivePosTran distribution agent%';

OPEN c_job;

FETCH NEXT FROM c_job
 INTO @JobName;
WHILE @@fetch_status = 0
BEGIN

    EXEC msdb.dbo.sp_delete_job @job_name = @JobName;

    FETCH NEXT FROM c_job
     INTO @JobName;
END;

CLOSE c_job;
DEALLOCATE c_job;

IF  EXISTS (SELECT name, is_published  FROM sys.databases  WHERE is_published = 1 AND name = 'ActivePos_read')
    IF EXISTS (SELECT  * FROM syspublications where name = 'ActivePosTran')
        EXEC sp_dropsubscription @publication = N'ActivePosTran', @subscriber = N'all', 
		@destination_db = N'ActivePos_Read', @article = N'all' --, @ignore_distributor=1
GO

IF  EXISTS (SELECT name, is_published  FROM sys.databases  WHERE is_published = 1 AND name = 'ActivePos_read')
    IF  EXISTS (SELECT  * FROM syspublications where name = 'ActivePosTran')
        EXEC sp_droparticle @publication = N'ActivePosTran', @article = N'all', @force_invalidate_snapshot = 1
GO

BEGIN TRY

/*Dropping the  publication */
  IF  EXISTS (SELECT name, is_published  FROM sys.databases  WHERE is_published = 1 AND name = 'ActivePos_read')
    IF  EXISTS (SELECT  * FROM syspublications where name = 'ActivePosTran')
        EXEC sp_droppublication @publication = N'ActivePosTran'
    ELSE PRINT 'There is no ActivePosTran publication on the server'
  ELSE PRINT 'The ActivePos_read database is not yet published on this server'
END TRY
BEGIN CATCH
 PRINT ERROR_MESSAGE()
 WAITFOR DELAY '00:02:00'
 /* Dropping the subscriptions in case a pos registered in between causing deadlock*/
 IF EXISTS (SELECT name, is_published  FROM sys.databases  WHERE is_published = 1 AND name = 'ActivePos_read')
    IF EXISTS (SELECT  * FROM syspublications where name = 'ActivePosTran')
        EXEC sp_dropsubscription @publication = N'ActivePosTran', @subscriber = N'all', 
		@destination_db = N'ActivePos_Read', @article = N'all' --, @ignore_distributor=1

/* Try Dropping the  publication again */
  IF  EXISTS (SELECT name, is_published  FROM sys.databases  WHERE is_published = 1 AND name = 'ActivePos_read')
    IF  EXISTS (SELECT  * FROM syspublications where name = 'ActivePosTran')
        EXEC sp_droppublication @publication = N'ActivePosTran'
    ELSE PRINT 'There is no ActivePosTran publication on the server'
  ELSE PRINT 'The ActivePos_read database is not yet published on this server'

END CATCH
GO

/* Because the replication will be set up again almost from scratch */
EXEC sp_removedbreplication 'ActivePos_Read'
GO

IF EXISTS (SELECT name  FROM sys.databases  WHERE name = 'distribution') 
    AND EXISTS (SELECT name, is_published  FROM sys.databases  WHERE is_published = 1 AND name = 'ActivePos_read')
    EXEC sp_replicationdboption @dbname = N'ActivePos_Read', @optname = N'publish', @value = N'false'
GO