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 a single database, we have created stored procedure to process requested data from inside the 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 follows,
Instead of running that stored procedure manually as above, we can set stored procedure self executing as follows. Whenever the new incoming requested messages come this stored procedure executed and process the data for history. We will alter the target queue here. This is called the internal activation of the stored procedure. Service broker will act the stored procedure to process incoming messages from target queue and will call anytime when messages arrive in the target queue.
As retesting the automated self execution of stored procedure, we will do the same, but we will not call the stored procedure.
Hope you liked this post for setting stored procedure self execution with target queue.
Remember, in service broker example for a single database, we have created stored procedure to process requested data from inside the 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 follows,
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 GONow 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 follows. Whenever the new incoming requested messages come this stored procedure executed and process the data for history. We will alter the target queue here. This is called the internal activation of the stored procedure. Service broker will act the stored procedure to process incoming messages from target queue and will call anytime when messages arrive in the 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 ) GORe-testing :
As retesting 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 GOFinally the output coming for both testing of manually and salf execution of the stored procedure as captured following,
Hope you liked this post for setting stored procedure self execution with target queue.
Comments
Post a Comment