SQLServerException: The server failed to resume the transa..

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

"Scott Berry" wrote:

> 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
>
 >> Stay informed about: SQLServerException: The server failed to resume the transa.. 

On Apr 6, 11:22 pm, Scott Berry
wrote:
> 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

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
 >> Stay informed about: SQLServerException: The server failed to resume the transa.. 

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();

"Omar Granados" wrote:

> Hi, I'm facing the same problem... could you find a solution..or the cause ???
>
> "Scott Berry" wrote:
>
> > 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
> >
 >> Stay informed about: SQLServerException: The server failed to resume the transa.. 

"joeNOSPAM@bea.com" wrote:

> On Apr 6, 11:22 pm, Scott Berry
> wrote:
> > 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
>


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.
 >> Stay informed about: SQLServerException: The server failed to resume the transa.. 

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?=
>References: <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:
>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
>
>
>
>"joeNOSPAM@bea.com" wrote:
>
>> On Apr 6, 11:22 pm, Scott Berry
>> wrote:
>> > 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
>>
>
>
>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
This posting is provided “AS IS” with no warranties, and confers no rights.
 >> Stay informed about: SQLServerException: The server failed to resume the transa.. 

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

"Evan T. Basalik (MSFT)" wrote:

> 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?=
> >References: <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:
> >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
> >
> >
> >
> >"joeNOSPAM@bea.com" wrote:
> >
> >> On Apr 6, 11:22 pm, Scott Berry
> >> wrote:
> >> > 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
> >>
> >
> >
> >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
> This posting is provided “AS IS� with no warranties, and confers no rights.
>
>
>
 >> Stay informed about: SQLServerException: The server failed to resume the transa.. 

On Aug 15, 9:36 am, Pete Lefebvre <Pete
Lefeb....RemoveThis@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
 >> Stay informed about: SQLServerException: The server failed to resume the transa..