create procedure sys.sp_MSdropmqforsubscription
(
@queueserver sysname -- server name for created queue
,@queueid sysname -- queue id for created queue
)
as
begin
declare @retcode int
,@qservicestatus int
-- Check if the MSMQ service is running, if not running,
-- return with error
-- BYPASS the check for clusters
if ( SERVERPROPERTY('IsClustered') = 0 )
begin
exec @retcode = sys.xp_controlqueueservice
@control_command = 1,
@return_result = @qservicestatus output
if (@retcode != 0 or @@error != 0)
begin
raiserror(21542, 16, 1, @@error, 'xp_controlqueueservice')
return 1
end
if (@qservicestatus != 1)
begin
raiserror(21763, 16, 1)
return 1
end
end
-- delete local private queue
exec @retcode = sys.xp_deleteprivatequeue @queueserver, @queueid
if (@retcode != 0 or @@error != 0)
begin
raiserror(21542, 16, 2, @@error, 'xp_deleteprivatequeue')
return 1
end
-- all done
return 0
end