Activated Proc not firing.

Hi There

I just had the following scenario.

I checked sys.service_queues to confirm the my queue has an activated proc assigned to it and is activation_enabled.

I send messages to the service, however i see that the messages just sit in the queue. I run profiler with all SB and Error events there is nothing, i check the sql server log, there is also nothing.

So i have no idea why the activated proc is not firing, all i see in the profiler trace is that the messages are acknowledged but the activated proc does not execute.

For a while i was stumped. i then tried to execute the proc myself and i got a syntax error.

Basically what happened was that i altered a table that the proc used and now the proc cannot execute cos there are more columns that must be inserted so i altered the proc.

However the activated proc still did not fire.

I had to disable and re-enable activation for it to work.

What i am wondering is why was there no indication in profiler or the sql og as the the fact that something was wrong with the activated proc ? Surely the activated proc should have fired and given the same error i got either to the sql log or profiler ?

And why did i have to re-enable activation ? Does sql cache the proc when you activate it and not know that the proc has changed when you alter it ?

Thanx

I had a similar issue...and I am trying to get a resolution on this. In my scenario, it was a new service broker startup (complete rebuild of queues from build scripts that were working in our development database environment)...We have about 30 queues in which all but 1 worked fine. When I went to look to see what happened, the queue was up and activation was on and the messages were sitting in the target queue. When I manually executed the activation stored procedure everything was successful and processed fine (no errors). This is not the first time this has happened, and after it is manually executed the activation seems to occur without issue from that point forward. I am just kind of concerned that when this system is in production and they have to take it down to perform maintenance, this will happen again and since nothing is really "wrong" with the queue, monitoring this is fairly difficult.

Any ideas or suggestions?

|||

When you run into this problem, look at sys.dm_broker_queue_monitors, sys.dm_broker_activated_tasks and sys.service_queues views. We need the content of these views to diagnose the issue.

HTH,
~ Remus

|||

Hi Remus

Ok next time i will get that information.

Thanx

Activated Proc not firing.

Hi There

I just had the following scenario.

I checked sys.service_queues to confirm the my queue has an activated proc assigned to it and is activation_enabled.

I send messages to the service, however i see that the messages just sit in the queue. I run profiler with all SB and Error events there is nothing, i check the sql server log, there is also nothing.

So i have no idea why the activated proc is not firing, all i see in the profiler trace is that the messages are acknowledged but the activated proc does not execute.

For a while i was stumped. i then tried to execute the proc myself and i got a syntax error.

Basically what happened was that i altered a table that the proc used and now the proc cannot execute cos there are more columns that must be inserted so i altered the proc.

However the activated proc still did not fire.

I had to disable and re-enable activation for it to work.

What i am wondering is why was there no indication in profiler or the sql og as the the fact that something was wrong with the activated proc ? Surely the activated proc should have fired and given the same error i got either to the sql log or profiler ?

And why did i have to re-enable activation ? Does sql cache the proc when you activate it and not know that the proc has changed when you alter it ?

Thanx

I had a similar issue...and I am trying to get a resolution on this. In my scenario, it was a new service broker startup (complete rebuild of queues from build scripts that were working in our development database environment)...We have about 30 queues in which all but 1 worked fine. When I went to look to see what happened, the queue was up and activation was on and the messages were sitting in the target queue. When I manually executed the activation stored procedure everything was successful and processed fine (no errors). This is not the first time this has happened, and after it is manually executed the activation seems to occur without issue from that point forward. I am just kind of concerned that when this system is in production and they have to take it down to perform maintenance, this will happen again and since nothing is really "wrong" with the queue, monitoring this is fairly difficult.

Any ideas or suggestions?

|||

When you run into this problem, look at sys.dm_broker_queue_monitors, sys.dm_broker_activated_tasks and sys.service_queues views. We need the content of these views to diagnose the issue.

HTH,
~ Remus

|||

Hi Remus

Ok next time i will get that information.

Thanx

Accessing multiple recordsets in MRS

Hi all,
Can anybody tell me how to acccess multiple recordsets in mrs. Please
read the following scenario.
I have written a Stored Procedure in SQL Server, this SP returns two
recordsets, suppose the sp name is my_test_sp (lets assign some alias
for those two recordsets as recordsrt1 and recordset2). I am creating a
report which is fetching data from my_test_sp, lets call this report as
my_test_report.
When I set the dataset properties of my_test_report to access
my_test_sp, the field list it generates contais fields that are from
recordset1, it dosenot display any field from recordset2.
Now here; is there any way i can access both recordset1 and recordset2
at a same time. It ll be ok if i create more than one datasets, but
then the question will be can i connect a singal data object like table
to multiple datasets at the same time? and will there be any link
between these two datasets?
I hope I am not missing anything at the design time. Your help will be
grately appriciated.
Thanks in advace. :)
Manoj SA few design issues to consider:
1. RS can only handle one result set at a time from a Stored Procedure (as
you have seen). If you want both you would need the SP to have a parameter
that tells it which to return and then call the SP twice.
2. RS does not associated datasets with one another.
3. Subreports are how you Master-detail
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Monty" <manojshirgurkar@.gmail.com> wrote in message
news:1164179273.330677.7870@.b28g2000cwb.googlegroups.com...
> Hi all,
> Can anybody tell me how to acccess multiple recordsets in mrs. Please
> read the following scenario.
> I have written a Stored Procedure in SQL Server, this SP returns two
> recordsets, suppose the sp name is my_test_sp (lets assign some alias
> for those two recordsets as recordsrt1 and recordset2). I am creating a
> report which is fetching data from my_test_sp, lets call this report as
> my_test_report.
> When I set the dataset properties of my_test_report to access
> my_test_sp, the field list it generates contais fields that are from
> recordset1, it dosenot display any field from recordset2.
> Now here; is there any way i can access both recordset1 and recordset2
> at a same time. It ll be ok if i create more than one datasets, but
> then the question will be can i connect a singal data object like table
> to multiple datasets at the same time? and will there be any link
> between these two datasets?
> I hope I am not missing anything at the design time. Your help will be
> grately appriciated.
> Thanks in advace. :)
> Manoj S
>|||you're talking about MARS right?
MARS was a scam; it doesn't work
I still can't open two datareaders on the same connection.. and that's
what it's advertised as fixing
those cocksuckers just need to go back to ADO; things have been buggy
as hell ever since they went down this ADO.net route.. I mean; what a
joke
-Aaron
Monty wrote:
> Hi all,
> Can anybody tell me how to acccess multiple recordsets in mrs. Please
> read the following scenario.
> I have written a Stored Procedure in SQL Server, this SP returns two
> recordsets, suppose the sp name is my_test_sp (lets assign some alias
> for those two recordsets as recordsrt1 and recordset2). I am creating a
> report which is fetching data from my_test_sp, lets call this report as
> my_test_report.
> When I set the dataset properties of my_test_report to access
> my_test_sp, the field list it generates contais fields that are from
> recordset1, it dosenot display any field from recordset2.
> Now here; is there any way i can access both recordset1 and recordset2
> at a same time. It ll be ok if i create more than one datasets, but
> then the question will be can i connect a singal data object like table
> to multiple datasets at the same time? and will there be any link
> between these two datasets?
> I hope I am not missing anything at the design time. Your help will be
> grately appriciated.
> Thanks in advace. :)
> Manoj S