DBA > Job Interview Questions > Microsoft SQL Server FAQs

You created a SQL Server 2005 Service Broker que

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

You created a SQL Server 2005 Service Broker queue ....

You created a SQL Server 2005 Service Broker queue, but neglected to add an activation procedure. Fortunately you had not set the status to ON. You use the ALTER QUEUE statement to specify a stored procedure as follows:
ALTER QUEUE PublicationQueue
WITH ACTIVATION (
PROCEDURE_NAME = new_stored_proc,
EXECUTE AS SELF) ;

However the procedure does not seem to run when you send a message to the queue. What is wrong?

Answer: The queue activation status needs to be set to on.
Explanation: When you alter a queue and change the activation stored procedure, this does not affect the activation status of the queue. You could need to alter the queue again with the "ACTIVATION (STATUS = ON)" parameter.

(Continued on next question...)

Other Job Interview Questions