Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 125
sql 124
ms sql server 119
ms sql 118
database 109
tsql 81
#SQL Server 78
t-sql 75
#sql 71
sql server general 67

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

SQL Server - Stored Procedure Internal Activation in Service Broker

Sep 16 2011 9:03AM by Paresh Prajapati   

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.

Tags: sql, tsql, sql server, ms sql, ms sql server, t-sql, database, sql server general, SQL Scripts, query, Service Broker, #SQLServer,


Paresh Prajapati
6 · 23% · 7464
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"SQL Server - Stored Procedure Internal Activation in Service Broker" rated 5 out of 5 by 3 readers
SQL Server - Stored Procedure Internal Activation in Service Broker , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]