Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Carat
Yak Posting Veteran
92 Posts |
 Posted - 2008-12-08 : 07:56:32
|
| I use Service Broker to capture certain Event Notifications in a table. I have a trigger on this table that is executed whenever a notification is logged which has a severity higher than 17. In my trigger I use a webservice (CLR) to send an sms to my phone.We have a contract with another company that provides us the possibiliy to sending sms. This company delivered the webservice.The last 2 months this worked fine but today I noticed a problem. I get the following error in the SQL Server Error log:Date 8/12/2008 13:35:20Log SQL Server (Current - 8/12/2008 13:35:00)Source spid70sMessageThe activated proc "..." running on queue "..." output the following: 'The service queue "..." is currently disabled.'I tried to execute the activation SP manually:alter queue "..." with status = onexec "..."When I run this I get an error of the webservice:A .NET Framework error occurred during execution of user-defined routine or aggregate "..."It seems that there is a problem with the webservice, there is a problem with the gateway of the other company.When I try to send an sms manually it succeeds. So it could be that is was a temporary problem with there gateway. But I still get the error when I execute the activation SP manually to send an sms.Is there a way to delete this message from the queue so that the other messages can be logged in my table? When I query my queue I see 34 records waiting. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-12-08 : 08:02:51
|
| what you get is poison messages. you have to reenable your queue.and note that calling a web service call in the trigger is a very very very bad idea. why don't you use another service broker queue for web service call to call your web service?___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
 |
|
|
Carat
Yak Posting Veteran
92 Posts |
Posted - 2008-12-08 : 08:18:46
|
| I tried the following:alter queue queue_eventlog with status = onWhen I look in 'sys.service_queues', is_receive_enabled and is_enqueue_enabled are 0. And the error keeps comming.Can you tell me why it is a bad idea to call a webservice in a trigger? Security?When I use the same queue I could call the webservice in my activation procedure. Is this better?Thanks for your help. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-12-08 : 09:07:30
|
| poison messages are messages that error out and are not handled. after 5 such retires the queue gets disabled. so first you need to add error handling to your code.what i would do is create a new queue into which you put messages in your trigger. in the message you can put whatever data you need.have another activation sproc that send on that second queue that sends the sms.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
Carat
Yak Posting Veteran
92 Posts |
Posted - 2008-12-08 : 11:47:24
|
| Ok, thanks.I will try this. |
|
|
|
|
|
|
Problem: The activated proc ... running on queue