Re: SQL Stored Proc

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I need to insert into 4 tables, if any of them fail, I need to rollback all
of the previous ones...
Eric

"rclb" <ray.crager@xxxxxxxxxxxxxxx> wrote in message
news:1155966189.155918.157710@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Do you have a COMMIT or ROLLBACK statement in the proc? If so, remove
them.


Eric Stott wrote:
When I run a stored proc in QA, it runs successfully, however when I run
the
code against BizTalk I get the following error:

The adapter failed to transmit message going to send port
"SQL://database/".
It will be retransmitted after the retry interval specified for this Send
Port. Details:"HRESULT="0x80004005" Description="Transaction count after
EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is
missing. Previous count = 1, current count = 0."

Is there a tool that I can find out why I am getting this error, and why
would I only be getting this with BizTalk, why isn't QA coming back with
the
same error?

Thanks



.

Relevant Pages

  • Re: Continuing an outer loop from an inner loop
    ... else if == FAIL) ... enum result ... (The first thing one types into the just-opened scope dependent on the SUCCESS of op_k() ... Reverse ordering of rollback steps needs extra attention. ...
    (comp.lang.c)
  • Re: Embedded SP
    ... "Dan Guzman" wrote: ... only want the entire 1st sp to fail if anything ... Is ROLLBACK needed in this case or will it auto rollback if it ... COMMIT even after an error occurs in the INSERT, ...
    (microsoft.public.sqlserver.programming)
  • Re: rollback transaction not rolling back
    ... >mean I will not be able to rollback this transaction?? ... INSERT statement that might fail ... You shouyld change this to ...
    (microsoft.public.sqlserver.programming)
  • Re: Embedded SP
    ... only want the entire 1st sp to fail if anything ... Is ROLLBACK needed in this case or will it auto rollback if it never ... COMMITs the tran? ... I see no error handling in the first proc so the code might continue and COMMIT even after an error occurs in the INSERT, ...
    (microsoft.public.sqlserver.programming)
  • Re: What to do in case rollback fails?
    ... If you mean you want to handle the circumstance that the rollback *itself* fails, ... SQL Server tries to give strong guarantees that that can't happen (assuming a transaction was started, of course), and if those fail there's nothing meaningful you could do from within your stored procedure. ... because error handling in stored procedures is more subtle than you might expect. ...
    (microsoft.public.sqlserver.programming)