Please use FireFox to view this page

create procedure sp_MSupdate_mqserver_subdb as begin if exists (select * from dbo.MSsubscription_agents where queue_id IS NOT NULL and substring(queue_id, 1, 10) != N'mssqlqueue' and queue_server IS NULL) begin -- -- we have entries for active Queued subscriptions using MSMQ that need upgrade -- declare @queue_server sysname ,@publisher sysname ,@publisher_db sysname ,@publication sysname DECLARE hC CURSOR LOCAL FAST_FORWARD FOR select publisher, publisher_db, publication from dbo.MSsubscription_agents where queue_id IS NOT NULL and substring(queue_id, 1, 10) != N'mssqlqueue' and queue_server IS NULL OPEN hC FETCH hC INTO @publisher, @publisher_db, @publication WHILE (@@fetch_status != -1) BEGIN -- -- the table MSsubscription_properties has to exist; -- prepare the queue server name from distributor name -- strip the instance name if necessary -- select @queue_server = ISNULL(distributor, @@servername) from dbo.MSsubscription_properties where UPPER(publisher) = UPPER(@publisher) and publisher_db = @publisher_db and publication = @publication if (charindex(N'\', @queue_server) > 0) select @queue_server = substring(@queue_server, 1, charindex(N'\', @queue_server) - 1) -- -- set the queue_server for this entry -- update dbo.MSsubscription_agents set queue_server = @queue_server where UPPER(publisher) = UPPER(@publisher) and publisher_db = @publisher_db and publication = @publication -- -- get next entry -- FETCH hC INTO @publisher, @publisher_db, @publication END CLOSE hC DEALLOCATE hC end end