question

VDT-7677 avatar image
0 Votes"
VDT-7677 asked ErlandSommarskog commented

Modifying Service Broker Queue's Activated Stored Procedure

Hi,

Running SQL Server 2017. We have an activated stored procedure (which is encrypted) attached to a service broker queue to process messages. Everything is working correctly.

Now, we want to modify the stored procedure which is used by the queue. As the stored procedure in question is encrypted we are unable to ALTER, we instead need to DROP then CREATE. In order to do this, what steps do we have to take? Is it as simple as turning the queue off, re-creating the procedure, then turning the queue back on while referencing the newly-created procedure in the WITH ACTIVATION clause?

Thanks!

sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Confused - why can you not alter? If you have the source code then alter should work without a problem. Either way, it is probably a good idea to turn the queue off before making the change.

0 Votes 0 ·

Indeed. This runs without errors:

CREATE PROCEDURE nisse WITH ENCRYPTION AS
   PRINT 'Hoppla'
go
CREATE OR ALTER PROCEDURE nisse WITH ENCRYPTION AS
   PRINT 'Tralala'

So there is no need to DROP + CREATE.

0 Votes 0 ·

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

That should do it, I think.

But it is always a good idea to do this in a test environment before you try this in production.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.