As we have seen earlier posts for Service Broker for single database and Service Broker between databases, we have implemented it with examples. Hope you liked those posts.

Remember, in service broker example for single database, we have created stored procedure to process requested data from in target database sent from initiator database. Then we have manually ran the stored procedure to process history data. Please open the link for that example here. We have tested the same there as following,

USE SourceDB
GO

TRUNCATE TABLE TransactionTable
TRUNCATE TABLE TransactionHistory
GO

-- Inserting a record
INSERT INTO TransactionTable
SELECT 2,'Insert',Getdate()

-- Updating a records
UPDATE TransactionTable
SET TranType = 'Update',
TranDate = GETDATE()
WHERE TranId = 2

-- Deleting a record
DELETE FROM TransactionTable
WHERE TranId = 2

GO

-- Executing stored procedure manualy
EXEC SB_ReceiveData
GO

SELECT 
* 
FROM TransactionTable

SELECT 
* 
FROM TransactionHistory
GO

Now come out from the link and look what i will do?
Instead of running that stored procedure manually as above, we can set stored procedure self executing as following. Whenever the new incomming requested messages come this stored procedures executed and process the data for history. We will alter target queue here. This is called internal activation of stored procedure. Service broker will active the stored procedure to process incomming message from target queue and will call anytime when message arrive in target queue.

-- Altering queue with self execution of stored procedure
ALTER QUEUE DestinationQueue
    WITH ACTIVATION
    ( STATUS = ON,
      PROCEDURE_NAME = SB_ReceiveData,
      MAX_QUEUE_READERS = 100,
      EXECUTE AS SELF
    )
GO

Retseting :
As resteting the automated self execution of stored procedure, we will do the same but we will not call the stored procedure.

USE SourceDB
GO

-- Truncating table
TRUNCATE TABLE TransactionTable
TRUNCATE TABLE TransactionHistory
GO

-- Inserting a record
INSERT INTO TransactionTable
SELECT 2,'Insert',Getdate()

-- Updating a records
UPDATE TransactionTable
SET TranType = 'Update',
TranDate = GETDATE()
WHERE TranId = 2

-- Deleting a record
DELETE FROM TransactionTable
WHERE TranId = 2

GO

-- Reviewing the data from tables
SELECT 
* 
FROM TransactionTable

SELECT 
* 
FROM TransactionHistory
GO

Finally the output comming for both testing of manually and self execution of stored procedure as captured following,



Hope you liked this post for setting stored procedure self execution with target queue.