Can you/should you use SQL Server Service Broker with .NET applications?

.net sql-server message-queue service-broker
Jonathan Allen picture Jonathan Allen · Sep 21, 2009 · Viewed 17.7k times · Source

I have many operations in the database that need to trigger application code. Currently I am using database polling, but I hear that SQL Server Service Broker can give me MSMQ-like functionality.

  1. Can I listen to SQL Server Service Broker queues from .NET applications running on a different machine?
  2. If so, should I do it?
  3. If not, what would you recommend?

Answer

Remus Rusanu picture Remus Rusanu · Sep 22, 2009

SSB (SQL Service Broker) has a feature named Activation that enabled a stored procedure to be attached to a queue. SQL Server will run this procedure internally when there are messages to consume in a queue. The queue attached procedure can be a CLR procedure, enabling for managed code business logic modules to run (C#, VB.Net etC).

An alternative to an internal activated stored procedure is to have an external client 'listen' on a queue with a WAITFOR(RECEIVE ... ) statement. This syntax is special for SSB and does a non-pooling block until there are messages to receive. Applications then consume the received messages as an ordinary T-SQL result set (like a SELECT). There is also a sample of an External Activator for Service Broker that leverages the event notification mechanism to know when to start an application for consuming messages from a queue.

If you want to see a sample of T-SQL code that leverages SSB internal Activation check out Asynchronous procedure execution.

Related questions

how to insert datetime into the SQL Database table?

How can I insert datetime into the SQL Database table ? Is there a way to insert this query through the insert command in C# / .NET?

Read More
C# Equivalent of SQL Server DataTypes

For the following SQL Server datatypes, what would be the corresponding datatype in C#? Exact Numerics bigint numeric bit smallint decimal smallmoney int tinyint money Approximate Numerics float real Date and Time date datetimeoffset datetime2 smalldatetime datetime time Character Strings …

Read More
How can I solve a connection pool problem between ASP.NET and SQL Server?

The last few days we see this error message in our website too much: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and …

Read More