receive and transactions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Richard - 09 May 2006 21:08 GMT

I need to receive from a queue, parse the data from the queue message_body,
and send the result set from the procedure to a C++ application. Is there
an easy way to cause the receive command to rollback in the case of an
application error or timeout, or for the receive command to not commit until
the application indicates success?

Richard

Reply to this Message

Kent Tegels - 09 May 2006 21:31 GMT

Hello Richard,

R> I need to receive from a queue, parse the data from the queue
R> message_body, and send the result set from the procedure to a C++
R> application. Is there an easy way to cause the receive command to
R> rollback in the case of an application error or timeout, or for the
R> receive command to not commit until the application indicates
R> success?

In this case, I think about moving whole of the processing logic over to
the C++ code and starting the transaction from there. There's some drill
down on listen to WMI at http://msdn2.microsoft.com/en-us/library/ms186371.aspx.
I believe you'd just need to look for a broker activation event to trigger
off your receive. There's also some examples in C:\Program Files\Microsoft
SQL Server\90\Samples\Engine\ServiceBroker.

Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Reply to this Message

Remus Rusanu [MSFT] - 09 May 2006 23:22 GMT

RECEIVE is fully integrated with the database transactions, so to roll it
back or commit simply use transactions:

BEGIN TRANSACTION;
RECEIVE * FROM [queue];
-- process messages
IF ERROR
ROLLBACK;
ELSE
COMMIT;

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

HTH,
~ Remus Rusanu

SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx

>I need to receive from a queue, parse the data from the queue message_body,
>and send the result set from the procedure to a C++ application. Is there
[quoted text clipped - 3 lines]
>
> Richard

Reply to this Message

Richard - 11 May 2006 07:11 GMT

Your example illustrates the challenge that I initially wrote about. A
procedure is called to receive data, the data needed to be sent to another
application for processing, and then depending the results of the
application, a commit or rollback issued for the receive command.

Here are brief descriptions of 2 possible solutions, in addition to Kent's
suggestion.

1) issue a begin transaction before calling the stored procedure, call the
procedure, process the result set via an application, then issue a commit or
rollback depending on the results of the application.

2) within the procedure that has the receive command, pass the data to a c#
stored procedure that interfaces with the application, then issue commit or
rollback depending on the return code of the c# procedure.

Does any of this sound feasible?

> RECEIVE is fully integrated with the database transactions, so to roll it
> back or commit simply use transactions:
[quoted text clipped - 14 lines]
>>
>> Richard

Reply to this Message

Kent Tegels - 11 May 2006 14:22 GMT

Hello Richard,

R> 1) issue a begin transaction before calling the stored procedure,
R> call the procedure, process the result set via an application, then
R> issue a commit or rollback depending on the results of the
R> application.

Transactions are session scoped w/r/t participation. That is, suppose you
start a transaction in SPID 57 that takes write locks on the affected data.
You'd somehow have to get your client side code to enlist in that transaction
avoid deadlocking issues. That won't be easy, so I wouldn't start by going
down this path.

I think the better way here is to listen the WMI event feed, then start your
own Tx from the client side. At least you should own the Tx context then.

R> 2) within the procedure that has the receive command, pass the data
R> to a c# stored procedure that interfaces with the application, then
R> issue commit or rollback depending on the return code of the c#
R> procedure.

That's going to mean writing unsafe code and getting that into production
can be complicated (see for a post from my blog about that [http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx])
but sure, it's doable.

Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Reply to this Message

Roger Wolter[MSFT] - 11 May 2006 17:54 GMT

The safer approach is definitely to have the external application connect to
the database, start a transaction receive the message, process the message,
and commit the transaction. SQL already has a lot of smarts around handling
your external application going away with a connection open or a transaction
active. If you write a stored procedure to call out to your external
program you will have deal with managing the lifetime of the application in
your stored procedure which may not be trivial. This obviously can be done
but it's usually a good practice to keep the moving parts to an absolute
minimum.

That being said, a CLR stored procedure which external access privileges
could do what you want to do but I would believe that it will generally be
less efficient and mare fragile.

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

> Your example illustrates the challenge that I initially wrote about. A
> procedure is called to receive data, the data needed to be sent to another
[quoted text clipped - 32 lines]
>>>
>>> Richard

Reply to this Message