Activation proc failed

Good day,

I have send a msg successful,but the proc failed. I altered the proc to correct the problem in the proc. The msg are still in the queue. Is there a manual way to excute the proc again to process the msg in the q? or do I wait for service broker to do it after a retry time (self recover)?

thank you in advance.

If the proc fails (i.e. throws an exception), Service Broker will try to restart the proc every 5 seconds. However, if your proc actually did a RECEIVE which was rolled back, then the rollback handler will detect this as a poisoned message and disable the queue. You can check this by doing:

select is_receive_enabled from sys.service_queues where name='YourQueue';

You can enable the queue by:

alter queue [YourQueue] with status = on;

|||

Rudhi

what I did :

receive top(1) @.receivedMsg = message_body,

@.h = conversation_handle,

@.msgType = message_type_name

from q111 -- wrong queue ,finger trouble.

In theLog file viewer :
The activated proc [dbo].[HandleRecieveMsg] running on queue EOGDB.dbo.q2eog output the following: 'Invalid object name 'q111'.'

So I corrected the proc to point to the correct queue.

I did as you said: "select is_receive_enabled from sys.service_queues where name='q2eog'; " q return a value of 1.

The message still is in the correct q after 5 seconds and longer.

Is there something else I can look or do?

|||

Okay I followed the steps in the help:

"Symptom: Activation Stored Procedures Do Not Run". And discovered that the proc I execute inside the RecieveProc fails.

(the proc I exec inside the recieving proc is proc reference a external assmbly)

Now my q is if a proc fails and the msg is allready retrieved from the queue, what happens to the msg? where does it go or the error msg?

|||

You can look at the state of the queue monitor:

select * from sys.dm_broker_queue_monitors;

If it is in 'NOTIFIED' state (which means, waiting for someone to do a RECEIVE), you can either reset the state by doing a RECEIVE or an ALTER QUEUE:

ALTER QUEUE [YourQueue] WITH STATUS = ON;

Hope that helps.

|||If you are doing that within a transaction scope, the transaction should rollback and the message should be put back into the queue.

Activated Stored Proc blocking?

Hi There

I was wondering, i have experienced alot of stored procedure blocking where you have a stored procedure that get executed very frequently and is complex, when the sp has to recompile it is locked and cannot be executed by other processes.

How will this work with an activated stored procedure, our activated stored procedure will probably not be too complex but it may exec complex ones base on the message type.

Now lets say that a message is recieved by the activated sp he in turn executes a complex sp to process the message this causes the complex sp to recompile, during this time another activated sp is spawned by the queue with the same message type he execs the same complex sp that is busy recompiling, i presume that the second activated sp will be blocked until the sp has completed recompiling.

Is this correct, because on a very busy queue with very complex sp's being called by the activated stored procedure, the benefit of having multiple queue readers is negated by the blocking caused by the recompiling sp's called by the activated sp.

Basically no matter how many queue readers kick in to handle the messages they all wait for the sp to recompile from the first message.

I am hoping multiple spawned activated stored procedures somehow over come this? Or work differently somehow.

Does anyone know ?

Thanx

The stored proc will be compiled only once. After that, all callers should be able to use the stored proc without blocking other callers.

If you need the stored procs to be modified frequently, that could result in constant recompilation as you have described above. In that case, you could use dynamic SQL and put the frequently changing code as strings in a table.

Hope that helps.

|||

Hi Rushi

Perhaps i need to found out more about sp recompiles, we have a large sp about 1000 lines. It never changes, however it recompiles everytime it executes, not sure why. Thats why i was under the impression that very large complex sp's executed by the activated stored prcedure would blocked the additional spawned queue readers?

What are you r thoughts on this? Is it correct that sp's that have large amounts of dynamic sql will recompile frequently, i cannot put these strings in a table since they are dynamically created with variables passed to the sp.

Thank You

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 stored proc multiple return values

Hi,
I have a problem. I have two stored procs. One I am building currently
(sp_load) and another that is already in the data warehouse and which I
have no control over (sp_log_event).
sp_log_event is for control logging. It accepts a process name
parameter. It outputs 3 return parameters by issuing the following
command:
SELECT
load_id,
last_succ_load_id,
datEventDate
FROM
ctl_event_log_header
WHERE
load_Id = @.intLoadId
I am no expert on this but as I understand it these are technically not
output parameters. If I create an Execute SQL Task in DTS I have the
option of setting these 3 return values to my global variables in my
package - which is easy enough and I am already doing this.
However my problem is that I now need to call this (sp_log_event) from
within the stored proc I am creating (sp_load). Something like EXEC
MY_SP @.processname
If the return was an output parameter i could simply do EXEC MY_SP
@.processname, @.loadid output
Also if it was just one return value I could do
EXEC @.loadid = (MY_SP @.processname)
But it is neither of these scenarios and I can't work out how I can get
access to these 3 returned values from the confines of my procedure.
load id is a primary key so the select will definitely only return one
record. how do i get access to the 3 return variables and assign them
to variables within my stored proc (sp_load)evs
BOL has very good examples how to use storerd procedure that has a few
OUTPUT parameters
BTW , it is really bad practice to use sp_ prefix to name stored
procedures, because in that way SQL Server is going to check for system
stored procedures first
"evs" <evan.winstanley@.gmail.com> wrote in message
news:1149050325.045357.63970@.u72g2000cwu.googlegroups.com...
> Hi,
> I have a problem. I have two stored procs. One I am building currently
> (sp_load) and another that is already in the data warehouse and which I
> have no control over (sp_log_event).
> sp_log_event is for control logging. It accepts a process name
> parameter. It outputs 3 return parameters by issuing the following
> command:
> SELECT
> load_id,
> last_succ_load_id,
> datEventDate
> FROM
> ctl_event_log_header
> WHERE
> load_Id = @.intLoadId
>
> I am no expert on this but as I understand it these are technically not
> output parameters. If I create an Execute SQL Task in DTS I have the
> option of setting these 3 return values to my global variables in my
> package - which is easy enough and I am already doing this.
> However my problem is that I now need to call this (sp_log_event) from
> within the stored proc I am creating (sp_load). Something like EXEC
> MY_SP @.processname
> If the return was an output parameter i could simply do EXEC MY_SP
> @.processname, @.loadid output
> Also if it was just one return value I could do
> EXEC @.loadid = (MY_SP @.processname)
> But it is neither of these scenarios and I can't work out how I can get
> access to these 3 returned values from the confines of my procedure.
> load id is a primary key so the select will definitely only return one
> record. how do i get access to the 3 return variables and assign them
> to variables within my stored proc (sp_load)
>|||Hi Uri,
Thanks for the reply. First of all, what is BOL? :)
Second of all - I am not actually naming my stored procs like that. I
just used that for simplicity. They are actually USP_CTL_xxx and
USP_ETL_xxx
Cheers though.
Uri Dimant wrote:
> evs
> BOL has very good examples how to use storerd procedure that has a few
> OUTPUT parameters
> BTW , it is really bad practice to use sp_ prefix to name stored
> procedures, because in that way SQL Server is going to check for system
> stored procedures first
>
>
> "evs" <evan.winstanley@.gmail.com> wrote in message
> news:1149050325.045357.63970@.u72g2000cwu.googlegroups.com...|||evs
BOL -Books On Line (tool suppliedb by MS with SQL Server)

> just used that for simplicity. They are actually USP_CTL_xxx and
> USP_ETL_xxx
I was referencing to <(sp_log_event). from your previous post
"evs" <evan.winstanley@.gmail.com> wrote in message
news:1149052185.818089.227430@.h76g2000cwa.googlegroups.com...
> Hi Uri,
> Thanks for the reply. First of all, what is BOL? :)
> Second of all - I am not actually naming my stored procs like that. I
> just used that for simplicity. They are actually USP_CTL_xxx and
> USP_ETL_xxx
> Cheers though.
>
> Uri Dimant wrote:
>
>|||On 30 May 2006 21:38:45 -0700, evs wrote:

>Hi,
>I have a problem. I have two stored procs. One I am building currently
>(sp_load) and another that is already in the data warehouse and which I
>have no control over (sp_log_event).
>sp_log_event is for control logging. It accepts a process name
>parameter. It outputs 3 return parameters by issuing the following
>command:
>SELECT
> load_id,
> last_succ_load_id,
> datEventDate
>FROM
> ctl_event_log_header
>WHERE
> load_Id = @.intLoadId
>
>I am no expert on this but as I understand it these are technically not
>output parameters. If I create an Execute SQL Task in DTS I have the
>option of setting these 3 return values to my global variables in my
>package - which is easy enough and I am already doing this.
>However my problem is that I now need to call this (sp_log_event) from
>within the stored proc I am creating (sp_load). Something like EXEC
>MY_SP @.processname
>If the return was an output parameter i could simply do EXEC MY_SP
>@.processname, @.loadid output
>Also if it was just one return value I could do
>EXEC @.loadid = (MY_SP @.processname)
>But it is neither of these scenarios and I can't work out how I can get
>access to these 3 returned values from the confines of my procedure.
>load id is a primary key so the select will definitely only return one
>record. how do i get access to the 3 return variables and assign them
>to variables within my stored proc (sp_load)
Hi evs,
CREATE TABLE #tmp ( load_id -- datatype
, last_succ_load_id -- datatype
, datEventDate -- datatype
);
INSERT INTO #tmp (load_id, last_succ_load_id, datEventDate)
EXEC MY_SP @.processname;
SELECT load_id, last_succ_load_id, datEventDate
FROM #tmp;
DROP TABLE #tmp;
Hugo Kornelis, SQL Server MVP|||Hugo,
Thank you so much mate! Works perfectly. I was aware of temporary
tables I have just never used them before and didn't think of it as an
option. Thanks again.
Hugo Kornelis wrote:
> On 30 May 2006 21:38:45 -0700, evs wrote:
>
> Hi evs,
> CREATE TABLE #tmp ( load_id -- datatype
> , last_succ_load_id -- datatype
> , datEventDate -- datatype
> );
> INSERT INTO #tmp (load_id, last_succ_load_id, datEventDate)
> EXEC MY_SP @.processname;
> SELECT load_id, last_succ_load_id, datEventDate
> FROM #tmp;
> DROP TABLE #tmp;
> --
> Hugo Kornelis, SQL Server MVP

Accessing output parameter in Stored Proc. using OBDC API...

hi pals,
I would like to ge the output parameter using ODBC API, say
SQLExecDirect("EXEC SP_New") . How could I get the output parameter value
from my application?
note: forgive me if this group is not a relevant one.
Thanks and Regards,
shahul.Hi
For bound parameters you can call SQLBindParameter
http://msdn.microsoft.com/library/d...r />
_729e.asp
Also check out:
http://msdn.microsoft.com/library/d...>
t10_7t2s.asp
and the sample ProcessReturnCodes.cpp.
More information is at
http://support.microsoft.com/defaul...kb;en-us;152174
For unbound parameters you can call SQLGetData e.g.
http://support.microsoft.com/defaul...kb;en-us;241147
John
"Shahul" wrote:

> hi pals,
> I would like to ge the output parameter using ODBC API, say
> SQLExecDirect("EXEC SP_New") . How could I get the output parameter value
> from my application?
> note: forgive me if this group is not a relevant one.
>
> Thanks and Regards,
> shahul.
>
>

Accessing output parameter in Stored Proc. using OBDC API...

hi pals,
I would like to ge the output parameter using ODBC API, say
SQLExecDirect("EXEC SP_New") . How could I get the output parameter value
from my application?
note: forgive me if this group is not a relevant one.
Thanks and Regards,
shahul.
Hi
For bound parameters you can call SQLBindParameter
http://msdn.microsoft.com/library/de...dbc_b_729e.asp
Also check out:
http://msdn.microsoft.com/library/de...cht10_7t2s.asp
and the sample ProcessReturnCodes.cpp.
More information is at
http://support.microsoft.com/default...b;en-us;152174
For unbound parameters you can call SQLGetData e.g.
http://support.microsoft.com/default...b;en-us;241147
John
"Shahul" wrote:

> hi pals,
> I would like to ge the output parameter using ODBC API, say
> SQLExecDirect("EXEC SP_New") . How could I get the output parameter value
> from my application?
> note: forgive me if this group is not a relevant one.
>
> Thanks and Regards,
> shahul.
>
>

Accessing output parameter in Stored Proc. using OBDC API...

hi pals,
I would like to ge the output parameter using ODBC API, say
SQLExecDirect("EXEC SP_New") . How could I get the output parameter value
from my application?
note: forgive me if this group is not a relevant one.
Thanks and Regards,
shahul.Hi
For bound parameters you can call SQLBindParameter
http://msdn.microsoft.com/library/d...r />
_729e.asp
Also check out:
http://msdn.microsoft.com/library/d...>
t10_7t2s.asp
and the sample ProcessReturnCodes.cpp.
More information is at
http://support.microsoft.com/defaul...kb;en-us;152174
For unbound parameters you can call SQLGetData e.g.
http://support.microsoft.com/defaul...kb;en-us;241147
John
"Shahul" wrote:

> hi pals,
> I would like to ge the output parameter using ODBC API, say
> SQLExecDirect("EXEC SP_New") . How could I get the output parameter value
> from my application?
> note: forgive me if this group is not a relevant one.
>
> Thanks and Regards,
> shahul.
>
>