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.

[ Post a follow-up to this message ]

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.
>

[ Post a follow-up to this message ]