SQLTeam.com | Weblogs | Forums

Replication - Issue Deleting Local Subscription w/TSQL

sql2008
ssms
tsql

#1

Hi all,

I am trying to write a script to delete the local publications and local subscriptions but everytime I try it it leaves the local subscription behind which causes other issues:

Here is my script:

-- Drop Replication
IF
(SELECT is_published FROM sys.databases WHERE name = 'NTTMI') = 1
BEGIN
EXEC
NTTMI.sys.sp_subscription_cleanup
@publisher = 'PENGUIN',
@publisher_db = 'NTTMI',
@publication = 'NTTMI_STAGING_TEST'

-- Dropping the transactional subscriptions
EXEC 
	NTTMI.sys.sp_dropsubscription 
		@publication = N'NTTMI_STAGING_TEST', 
		@subscriber = N'PENGUIN', 
		@destination_db = N'NTTMI_STAGING', 
		@article = N'all'
-- Dropping the transactional articles
EXEC 
	NTTMI.sys.sp_dropsubscription 
		@publication = N'NTTMI_STAGING_TEST', 
		@article = N'CallScripterSource', 
		@subscriber = N'all', 
		@destination_db = N'all'
EXEC 
	NTTMI.sys.sp_droparticle 
		@publication = N'NTTMI_STAGING_TEST', 
		@article = N'CallScripterSource', 
		@force_invalidate_snapshot = 1
-- Dropping the transactional publication
EXEC 
	NTTMI.sys.sp_droppublication 
		@publication = N'NTTMI_STAGING_TEST'
-- Disabling the replication database
EXEC 
	master.sys.sp_replicationdboption 
		@dbname = N'NTTMI', 
		@optname = N'publish', 
		@value = N'false'

END

Can anyone offer any advice?


#2

Sorry to double post but is anyone able to help with this?