|
Home > Archive > BizTalk Server Orchestration > January 2006 > BTS SQL Adapter - Rollback Tran Woes
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
BTS SQL Adapter - Rollback Tran Woes
|
|
| christopherkilmer@gmail.com 2006-01-20, 5:55 pm |
| I created a sproc in Sql Server 2005 that has the following basic
structure:
CREATE PROC InsertSomething
@msg xml
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN
INSERT INTO SomeTable
SELECT FROM xml (here I use the new xquery capabilities)
...more code that needs to be inside the transaction
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
END
When I run the sproc using query analyzer, everything works great, i.e.
valid operations cause a commit and invalid operations cause a
rollback.
The problem occurs when I send a message to the sproc from BizTalk 2006
using the Sql Adapter. If the message has good data, the sproc
executes correctly and the sql sproc successfully commits the
transaction. However, if the message I send through the Sql Adapter
generates an error in the sproc, the following message is generated
when ROLLBACK TRAN is called:
"...Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing..."
As I said, outside of the BTS Sql Adapter, the sproc works fine. I've
seen one other post with this exact problem, but there was no response
or resolution for the poster. Can this be done? What I'm trying to do
seems like a very basic operation that is critical to creating solid
sprocs. Am I missing something simple? Can anyone shed some light?
Any help would be appreciated. Thanks.
| |
| McGeeky 2006-01-23, 7:51 am |
| I don't think BizTalk permits you to commit or rollback transactions in
stored procedures if invoked by the SQL Adapter. Consider that the SQL
Adapter always assumes that it is responsible for issuing the final commit
or rollback based on any error code returned from the stored procedure. You
have just issued a roll back but the SQL Adapter doesn't know that so it
tries to issue a commit.
That's my understanding (though it may be flawed :-).
Bear in mind that you should not explicitly set the transaction isolation
level either.
--
McGeeky
http://mcgeeky.blogspot.com
<christopherkilmer@gmail.com> wrote in message
news:1137792610.315925.236310@z14g2000cwz.googlegroups.com...
>I created a sproc in Sql Server 2005 that has the following basic
> structure:
>
> CREATE PROC InsertSomething
> @msg xml
> AS
> BEGIN
> SET NOCOUNT ON;
>
> BEGIN TRY
> BEGIN TRAN
> INSERT INTO SomeTable
> SELECT FROM xml (here I use the new xquery capabilities)
>
> ...more code that needs to be inside the transaction
>
> COMMIT TRAN
> END TRY
> BEGIN CATCH
> ROLLBACK TRAN
> END CATCH
>
> END
>
> When I run the sproc using query analyzer, everything works great, i.e.
> valid operations cause a commit and invalid operations cause a
> rollback.
>
> The problem occurs when I send a message to the sproc from BizTalk 2006
> using the Sql Adapter. If the message has good data, the sproc
> executes correctly and the sql sproc successfully commits the
> transaction. However, if the message I send through the Sql Adapter
> generates an error in the sproc, the following message is generated
> when ROLLBACK TRAN is called:
>
> "...Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing..."
>
> As I said, outside of the BTS Sql Adapter, the sproc works fine. I've
> seen one other post with this exact problem, but there was no response
> or resolution for the poster. Can this be done? What I'm trying to do
> seems like a very basic operation that is critical to creating solid
> sprocs. Am I missing something simple? Can anyone shed some light?
> Any help would be appreciated. Thanks.
>
|
|
|
|
|