Skip to content

Instantly share code, notes, and snippets.

View occdevcamp's full-sized avatar

occdevcamp

View GitHub Profile
@occdevcamp
occdevcamp / Service Broker 04 - Write to Queue and Monitor Progress
Created January 28, 2015 16:13
This will again put some messages on the queue. Once it has completed (quickly) the processing will have been started and can be monitored by re-running the final Select * From dbo.T_QueueProcessingResult With (NoLock) statement. Every ~5s, a new row should appear (5 in total).
Use ServiceBrokerDemo
GO
Set NoCount On
-- Clear out any previous results of processing the queue's messages.
Delete dbo.T_QueueProcessingResult
RaisError ('Adding messages to the queue ...', 0, 1) With NoWait
Exec dbo.Proc_Queue_Send_Message
@occdevcamp
occdevcamp / Service Broker 03 - Alter Queue for Automatic Processing
Created January 28, 2015 16:12
Now, Proc_Queue_Process_Messages will be called automatically whenever a message is put on the queue – i.e. there’s no need to explicitly call it.
Use ServiceBrokerDemo
GO
Alter Queue Queue_Demo
-- Now process the queue automatically.
With Activation (
Status = On,
Max_Queue_Readers = 1,
Procedure_Name = dbo.Proc_Queue_Process_Messages,
Execute As Owner)
@occdevcamp
occdevcamp / Service Broker 02 - Write to Queue and Process
Created January 28, 2015 16:11
This puts some messages on the queue by calling the Proc_Queue_Send_Message stored procedure (which completes quickly). It then calls the Proc_Queue_Process_Messages stored procedure; this reads messages off the queue and processes them, putting the results in the T_QueueProcessingResult table. Proc_Queue_Process_Messages mimics doing “hard work…
Use ServiceBrokerDemo
GO
Set NoCount On
-- Clear out any previous results of processing the queue's messages.
Delete dbo.T_QueueProcessingResult
RaisError ('Adding messages to the queue ...', 0, 1) With NoWait
Exec dbo.Proc_Queue_Send_Message
@occdevcamp
occdevcamp / Service Broker Demo 01 - Create Database
Created January 28, 2015 16:09
Run from any existing database on a SQL Server, version 2005 or later. It creates a new database called ServiceBrokerDemo and makes in it a Queue, Service, Conversation, Message Type and a couple of example stored procedures.
Use Master
GO
If Not Exists (Select 1 From sys.databases Where (Name = 'ServiceBrokerDemo'))
Begin
Print 'Creating database ...'
Create Database ServiceBrokerDemo
End
GO