This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(140.8, 2%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EV7%3C/text%3E%3C/svg%3E)
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!
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(192, 23%, 28%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJW%3C/text%3E%3C/svg%3E)
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.
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.
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.