1
vote
0answers
1k views

DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS'

I have inherited a few scripts that are pumped through a c#.NET custom app to update a SQL2005 SP3 database. One of these scripts does a deletion on a user table called msw_timer_task, based on a ...
4
votes
1answer
310 views

Why does WAITFOR (RECEIVE FROM QUEUE) show up in the monitor incorrectly?

I kick off the following command in my app: WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM MyQueue) When I run `sp_who2 active', I get Status=SUSPENDED and Command=DELETE Note: ...
13
votes
2answers
299 views

Does firing off an indefinite WAITFOR increase the log file size?

In the last release of my app, I added a command that tells it to wait when something arrives in the Service Broker queue: WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM MyQueue) The ...
5
votes
2answers
802 views

Performance impact of waiting on a service broker message

I have a situation where multiple client apps send messages via the Service Broker (utilizing stored procs). These messages are picked up by yet another client app and then processed. The way the ...
4
votes
1answer
251 views

Check Index Fragmentation on SQL Server Audit Queues

I've read this excellent article, http://rusanu.com/2010/03/09/dealing-with-large-queues/, and seem to be having the same issue, due to a low space issue, the service broker was turned off on a ...
1
vote
1answer
156 views

Recommendations on creating user-objects on system database

I want to setup a bunch of Event Notifications on SQL Server using Service Broker for our production database. I've setup a new database on my testing machine, and everything works well. The problem ...
1
vote
1answer
659 views

Elegant way of sending e-mails with Service Broker

Is there an elegant way for sending e-mails using a Service Broker service/queue? I want to setup an EVENT NOTIFICATION for SQL Server, and I'd like to send e-mails to operators for each message on ...
1
vote
1answer
421 views

Setting Alerts for Autogrow

Can I set alerts for an autogrow event in SQL Server (2005 or 2008 R2)? I've searched around and haven't found the 'error number' for autogrowth, if there is one. In case this isn't possible, how ...
3
votes
1answer
978 views

How do query notifications detect changes in the query result?

I am working with a legacy database that has implemented query notifications against a table called ColorList. While running a server-side trace for an unrelated issue, I noticed the query SELECT ...
2
votes
1answer
266 views

Accessing Service Broker from Different Databases

In my Application we have a lot of databases nearly 100 dbs. We are planning to use Service Broker for calling a Stored Proc Async. Instead of running Service Broker on all the Databases, Is it ...
1
vote
0answers
432 views

Can the service-broker cause SQL Server to crash when using SQLDependency

I have C# application that uses SQLDependency to get updates from a message table. I have tested it quite extensively and worked great in the development environment but when I ran it on the ...