|
|
|
date: Sun, 6 Apr 2008 23:22:01 -0700,
group: microsoft.public.sqlserver.jdbcdriver
back
Re: SQLServerException: The server failed to resume the transactio
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 Grant@discussions.microsoft.com>
>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.
date: Tue, 17 Jun 2008 20:43:21 GMT
author: (Evan T. Basalik (MSFT))
Re: SQLServerException: The server failed to resume the transaction
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?= <Tom Grant@discussions.microsoft.com>
> >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.
>
>
>
date: Fri, 15 Aug 2008 09:36:04 -0700
author: Pete Lefebvre Pete
Re: SQLServerException: The server failed to resume the transaction
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
date: Fri, 15 Aug 2008 12:37:25 -0700 (PDT)
author: unknown
|
|
