SQLServerException: The server failed to resume the transaction. D

[Scott Berry]

Hi All,

I have a block of code that does the following:

1. Call Stored Procedure A which internally:
a. Opens a transaction
b. Does work
c. Closes the transaction
2. Calls Stored Procedure B

When it runs it gives the following error:

SQLServerException: The server failed to resume the transaction. Desc:
and then one of the following error codes -
3800000001
3b00000001
4000000001

Auto commit is off and there is no transaction being initiated on the Java
side. When I run SQL profiler the only transaction being created is the one
inside the procedure.

If I remove the transaction management code from the proc then everything
works.

Has anyone seen this before or know how to fix it? I have a test harness if
anyone is interested.

Regards,

Scott

[Granados@discussions.microsoft.com Omar Granados]

[Omar Granados]

Hi, I'm facing the same problem... could you find a solution..or the cause ???

[joe.we...@gmail.com]

On Apr 6, 11:22 pm, Scott Berry <ScottBe...@discussions.microsoft.com>
wrote:

You shouldn't mix Java transaction control (autoCommit(false)) with
DBMS-side transaction control (procedures or SQL doing 'BEGIN TRAN'
and 'COMMIT" etc.).
What happens if you just use a connection in it's default
autoCommit(true)
mode?
Joe Weinstein at BEA Systems

[Scott Berry]

I found the root cause.

I wasn't closing the Statement object upon completion of the first database
call.

adding the following fixed it:
stmt.Close();

[Grant@discussions.microsoft.com Tom Grant]

"joeN...@bea.com" wrote:

I'm getting a similar error message. My application loops over 100,000 rows
and calls a stored procedure to insert the rows into a database. auto commit
is set to false on my connection and the stored procedure doesn't have any
transaction calls.

I'm preparing a CallableStatement at the start of the loop and I'm reusing
it for all 100,000 rows. I call conn.commit() every N rows.

I'm getting a com.microsoft.sqlserver.jdbc.SQLServerException with the
message:
The server failed to resume the transaction. Desc:3a00000001.

This same code is working with other MySQL and Oracle databases except on
those platforms I'm using PreparedStatements and SQL instead of stored
procedures. Any ideas? the earlier suggestion of closing the statement for
every row would drastically reduce performance.

[Evan T. Basalik (MSFT)]

Are you doing transaction management in your stored procedure?

Can you post your stored procedure defintion, plus your JDBC code?

Evan
--------------------
>Thread-Topic: SQLServerException: The server failed to resume the transactio
>thread-index: AcjP+EuoWF3JB8PDSoWVuiWRN/h5aw==
>X-WBNR-Posting-Host: 207.46.19.197
>From: =?Utf-8?B?VG9tIEdyYW50?= <Tom Gr...@discussions.microsoft.com>
>References: <14CFEB02-9083-4029...@microsoft.com> <affb3a2c-ddd5-4a28-b249-d9565a1451d4
@m1g2000pre.googlegroups.com>
>Subject: Re: SQLServerException: The server failed to resume the transactio
>Date: Mon, 16 Jun 2008 14:31:10 -0700
>Lines: 56
>Message-ID: <769D22B7-E966-4F62...@microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
>Newsgroups: microsoft.public.sqlserver.jdbcdriver
>Path: TK2MSFTNGHUB02.phx.gbl
>Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.jdbcdriver:526
>NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
>X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver

Evan T. Basalik
This posting is provided “AS IS” with no warranties, and confers no rights.

[Lefebvre@discussions.microsoft.com Pete Lefebvre]

Evan -

We are running into a similar issue. Transaction management is being done
in stored procedures. Autocommit is true.

We're seeing that when calling stored procedures in sequence (separate
connections), if the sps have transaction management code included and the
first SP throws an exception (of any kind), the next SP call will fail with
the "server failed to resume the transaction" message. If the transaction
management code is removed, the error is not thrown. Other drivers (jTDS,
IBM) do not appear to exhibit this behaviour. Are transactions within SPs
supported?

1. Call SP1
a. Open transaction
b. Rollback transaction due to error
2. Handle exception
3. Call SP2

Database product name : Microsoft SQL Server
Database product version : 9.00.3239
JDBC driver name : Microsoft SQL Server 2005 JDBC Driver
JDBC driver version : 1.2.2828.203

Thanks in advance for your assistance,
Pete

[joe.we...@gmail.com]

On Aug 15, 9:36 am, Pete Lefebvre <Pete

Lefeb...@discussions.microsoft.com> wrote:
> Evan -
>
> We are running into a similar issue. Transaction management is being done
> in stored procedures. Autocommit is true.
>
> We're seeing that when calling stored procedures in sequence (separate
> connections), if the sps have transaction management code included and the
> first SP throws an exception (of any kind), the next SP call will fail with
> the "server failed to resume the transaction" message. If the transaction
> management code is removed, the error is not thrown. Other drivers (jTDS,
> IBM) do not appear to exhibit this behaviour. Are transactions within SPs
> supported?
>
> 1. Call SP1
> a. Open transaction
> b. Rollback transaction due to error
> 2. Handle exception
> 3. Call SP2

So the SP1 does a "begin transaction" and then fails, (presumably
without
calling rollback?)
Then your code calls setAutoCommit(false),
then calls conn.rollback()
then calls SP2, which tries to do a "begin transaction" and fails?

Fundamentally, it will be a mess that the coders have to
manage if there are transactions being defined at different
levels in the stack...
Joe Weinstein at Oracle

[jcgal...@gmail.com]

for java:
put at the beginnig of your store procedure SET NOCOUNT ON; (to get last operation of your sp on sqlserver like :select insert or update)

so jdbc will get the last operation of your store procedure from sqlserver
on getResultSet() and after you could execute another sp from jdbc.
Example:

CREATE PROCEDURE sp_my

AS
BEGIN
SET NOCOUNT ON;

SELECT ........
INSERT .......
UPDATE .......
SELECT .......

END;