I’ve seen a number of customers reporting problems about ERRORLOG growing out of control (tens of GBs) because of error like following:

2007-10-12 11:18:32.44 spid25s The query notification dialog on conversation handle ‘{EC54573A-9978-DC11-961C-00188B111155}.’ closed due to the following error: ‘<?xml version=”1.0??><Error xmlns=”http://schemas.microsoft.com/SQL/ServiceBroker/Error”><Code>-8490</Code><Description>Cannot find the remote service ‘SqlQueryNotificationService-4869b411-fa1c-4d8a-ab37-5bf5762eb98b’ because it does not exist.</Description></Error>’.

2007-10-12 11:37:20.69 spid51s The activated proc [dbo].[ SqlQueryNotificationStoredProcedure-633a0c13-66e4-410e-8bd8-744146d2258d] running on queue tempdb.dbo. SqlQueryNotificationService-633a0c13-66e4-410e-8bd8-744146d2258d output the following: ‘Could not find stored procedure ‘dbo. SqlQueryNotificationStoredProcedure-633a0c13-66e4-410e-8bd8-744146d2258d‘

2007-10-12 10:59:39.32 spid51 Error: 28054, Severity: 11, State: 1.
2007-10-12 10:59:39.32 spid51 Service Broker needs to access the master key in the database ‘tempdb’. Error code:25. The master key has to exist and the service master key encryption is required.

All these messages are related one way or another to the ADO.Net component SqlDependency. I’ll present each one how it is caused and how to avoid id.

Read the rest of this entry »

Klaus has announced that he will release an update on his ‘Pro SQL Server 2005 Service Broker’ book with changes covering new features, revisiting some of the 2005 features like Query Notifications with more detailed coverage and much more. Not only that, but his announcement at http://www.csharp.at/blog/PermaLink,guid,a863c47b-2cfd-4395-bb32-e87bf33d6efe.aspx actually invites you to post back comments with what would you like to see in the new book.

The question was asked on the forums at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2320807&SiteID=1: How to enable a WCF based application to benefit from SQL Server 2005 Query Notifications? Basically the front end clients should be notified when data is changed on the back-end server tables as possible with least possible pooling.

Deploying SqlDependency in the WCF service itself to leverage the server’s Query Notification is a relatively easy process, simply follow the SqlDependency usage guidelines for any application: start SqlDependency, then attach a SqlDependency object instance to your SqlCommand objects, use this object’s OnChange event to react to changes, and subscribe again once notified.

Read the rest of this entry »

Error handling in T-SQL traditionally has been a sort of misterious voo-doo for most developers, with it’s mixture of error severities, SET settings like XACT_ABORT, ARITHABORT or ARITHIGNORE and the options to handle the error on the server or at the client. For a long time now the best resource I know on this subject was, and perhaps still is, Erland Sommarskog set of articles at http://www.sommarskog.se/error-handling-I.html and http://www.sommarskog.se/error-handling-II.html. But the introduction of Service Broker activated procedures adds some new issues to consider when designing your application and this post is about what these issues are these and how to best cope with them. 

Read the rest of this entry »

The CU3 of SQL Server 2005 SP2 was just released on the web, http://support.microsoft.com/kb/939537

It contains an update to Service Broker:

The title of the fix is derived from the original incident case, but the fix is actualy for the one case that would lead to ‘leaked’ conversation endpoints on the target in remote scenarios (communication between two different SQL Server instances) was fixed. The case fixed is when the pattern of message exchange is correct:

– Initiator sends one or more messages

– Target issue END CONVERSATION and sends the EndDialog message

– Initiator receives the EndDialog and responds by issuing it’s own END CONVERSATION

The defect was that if the initiator issues the END CONVERSATION within 1 (one) seconds of the target sending the EndDialog message, the target endpoint was ‘leaked’.

Note that this fix does not address local cases (two databases within the same SQL Server instance) nor the case of remote ‘incorrect’ message exchange pattern, when the initiator ends the conversation first w/o ever receiving any message from the target.

So how does one deploy Service Broker services in a large enterprise? Hundreds of services that change location every now and then (just enough to create a major outage exactly at the wrong moment!), and each database requiring a route to any service it wishes to interact with. Creating hundreds and thousands of routes and maintaining them as each new service is deployed, is retired or is moved is a difficult task and obviously prone to operational mistakes, pretty much a disaster waiting to happen.

Read the rest of this entry »

In my previous post Reusing Conversations I promised I’ll follow up with a solution to the question about how to end the conversations that are reused for the data-push scenario (logging, auditing, ETL for DW etc).

Read the rest of this entry »

One of the most common deployed patterns of using Service Broker is what I would call ‘data push’, when Service Broker conversations are used to send data one way only (from initiator to target). In this pattern the target never sends any message back to the initiator. Common applications for this pattern are:

· ETL from the transactions system to the data warehouse

· audit and logging

· aggregation of data from multiple sites

Read the rest of this entry »

A question was asked on the newsgroups: how can a C# program be notified when the schema of a table was modified (i.e. a column was added)? The short answer is to use Event Notifications, see http://msdn2.microsoft.com/en-us/library/ms189453.aspx

But an example would help J. First thing, we need to set up a queue and service that will receive the notifications:

Read the rest of this entry »

OK, since the devconnections site is taking a while to update, I’m posting directly my slides and code here.
sql_connections_2007.zip