create procedure sys.sp_MSaddmqforsubscription
(
@queueserver sysname output -- output server name for created queue
,@queueid sysname output -- output queue id for created queue
)
as
begin
declare @retcode int
,@qservicestatus int
,@tries tinyint
-- Check if the MSMQ service is running
-- 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, 3, @@error, 'xp_controlqueueservice')
return 1
end
if (@qservicestatus != 1)
begin
raiserror(21763, 16, 2)
return 1
end
/***************************************************
***** For NOW we will not AUTOSTART MSMQ services
****************************************************
if (@qservicestatus != 1)
begin
-- Queue service is not running. Try starting it only if it is
-- stopped. Otherwise, return with error (we do not want to
-- handle the cases for Pause/Pending/Continue states)
if (@qservicestatus = 2)
begin
-- start the service
exec @retcode = sys.xp_controlqueueservice
@control_command = 2,
@return_result = @qservicestatus output
if (@retcode != 0 or @@error != 0)
begin
raiserror(21542, 16, 4, @@error, 'xp_controlqueueservice')
return 1
end
-- check status (try 3 times with 2 sec delays)
select @tries = 0
while ((@qservicestatus != 1) and (@tries < 3))
begin
WAITFOR DELAY '00:00:02'
select @tries = @tries + 1
exec @retcode = sys.xp_controlqueueservice
@control_command = 1,
@return_result = @qservicestatus output
if (@retcode != 0 or @@error != 0)
begin
raiserror(21542, 16, 5, @@error, 'xp_controlqueueservice')
return 1
end
end -- end while
-- Did the service start
if (@qservicestatus != 1)
begin
raiserror(21763, 16, 3)
return 1
end
end -- service status = 2
else
begin
raiserror(21763, 16, 4)
return 1
end
end -- service status != 1
***************************************************/
end
-- create local private queue
select @queueid = cast(NEWID() as sysname)
select @queueserver = @queueid
exec @retcode = sys.xp_createprivatequeue @queueserver output
if @@ERROR != 0 or @retcode != 0
begin
select @queueid = NULL
,@queueserver = NULL
raiserror(21542, 16, 6, @@error, 'xp_createprivatequeue')
return 1
end
-- all done
return 0
end