>From google search I found the following describing a bug in jdbc..
I think your answer is exec sp_configure 'user options', '16384'
reconfigure
Article is posted below.
While troubleshooting a problem at one of our customers, we noticed the
following consistent erratic behaviour of our product with the 1.1.105 JDBC
driver (as well as the 1.2 CTP) when the XACT_ABORT user option has been set on
the SQL server:
Set the user option XACT_ABORT on a database server with the following SQL
query:
exec sp_configure 'user options', '16384'
reconfigure
Now run the following
Code Snippet
import java.sql.*;
import java.util.*;
public class Test {
public static void main(String[] args) {
try {
// Load the JDBC driver.
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// Establish the connection to the database.
String url = "jdbc:sqlserver://server:1433";
Properties props = new Properties() ;
props.setProperty("user","user");
props.setProperty("password","password");
props.setProperty("DatabaseName","databasename");
props.setProperty("selectMethod","cursor");
Connection conn = DriverManager.getConnection(url, props);
// Set up simple prepared Update statement
PreparedStatement pstmt = conn.prepareStatement("UPDATE table SET
field=0 where field=1", ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
Boolean isresultset = pstmt.execute();
System.out.println("IsUpdatecount? " + !(isresultset));
Integer updatedrecords=pstmt.getUpdateCount();
/*updatedrecords should be 0 or higher if isresultset is false
if updatedrecords=-1, the result should be a resultset, which it
cannot be, but seems to be*/
System.out.println(updatedrecords+ " row(s) affected");
} catch (Exception e) {
System.err.println("Got an exception! ");
System.err.println(e.getMessage());
}
}
}
You can revert the database server back to the correct behavior bij executing
the following SQL query
exec sp_configure 'user options', '0'
reconfigure
As you can see, when the SQL server user option XACT_ABORT is set, prepared
UPDATE statements through the SQL JDBC driver will fail consistently when
selectMethod=cursor and ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY are set, with the execute method indicating an
UpdateCount is returned, but the UpdateCount itself claiming a resultset has
been returned. On checking that, there is no resultset.
Somehow the option, which should do the following:
xact abort
Rolls back a transaction if a Transact-SQL statement raises a run-time error.
seems to roll back the results of an update statement in place ?
Is this a bug or something I seem to be doing wrong ?
Kind regards,
Niels
PS: With the 1.0 JDBC driver with this setting another problem occured, which
pointed to a rollback done within a transaction without any exception being
raised through JDBC, consistent with Angel Saenz-Badillos post on
http://www.mombu.com/microsoft/sql-server-jdbc-driver/t-behavior-of-connectioncommit-249105.html
We had hoped the 1.1 driver would at least raise the exception through JDBC,
but alas, the result mentioned above happened, which in the end we could track
back to the server setting.
--Update--
When an SQL Trace is active on the server the following two error messages
appear in the log, none of which are visible through the JDBC driver error
handler:
Exception Error: 156, Severity: 15, State: 1
Exception Error: 16954, Severity: 10, State: 1
Brgds
Monty Edwards
Developer Associate
Marketlinx.com
----- Original Message -----
From: Nall, Roger
Newsgroups: public.remedy.arsystem.general
To: [email protected]
Sent: Monday, March 02, 2009 4:26 PM
Subject: OT: SQL2008
**
All,
ARS 7.1 p4
WINXP
SQL2000
We are migrating all of our databases except ARSYSTEM to SQL2008. There are
external processes which we have view forms to other database table. I then
use these view forms to update and delete records. That table will be moving to
SQL2008
In order to keep this functionality we created a linked sever between the
Arsystem 2000 db server and the 2008 db server. We then created views on the
2000 server that looks at tables on the 2008 server. I then created view forms
to these views. I am trying to update data in these views which will then be
pushed to the actual tables in the 2008 database. The problem we are running in
to is this:
/UPDATE T533 SET C720001106='XXXCKelley3' WHERE C1 = '8928'
<SQL > <TID: 0000001968> <RPC ID: 0000084036> <Queue: Fast >
<Client-RPC: 390620 > <USER: rnall >
/* Mon Mar 02 2009 13:58:16.9760 */* WARNING * Unable to start a nested
transaction for OLE DB provider 'SQLOLEDB'. A nested transaction was required
because the XACT_ABORT option was set to OFF. (SQL Server 7395)OLE DB error
trace [OLE/DB Provider 'SQLOLEDB' ITransactionLocal::StartTransaction retur
<SQL > <TID: 0000001968> <RPC ID: 0000084036> <Queue: Fast >
<Client-RPC: 390620 > <USER: rnall >
/* Mon Mar 02 2009 13:58:16.9760 */ROLLBACK TRANSACTION
<SQL > <TID: 0000001968> <RPC ID: 0000084036> <Queue: Fast >
<Client-RPC: 390620 > <USER: rnall >
/* Mon Mar 02 2009 13:58:17.0540 */*** ERROR *** The ROLLBACK TRANSACTION
request has no corresponding BEGIN TRANSACTION. (SQL Server 3903)
<SQL > <TID: 0000001968> <RPC ID: 0000084036> <Queue: Fast >
<Client-RPC: 390620 > <USER: rnall >
/* Mon Mar 02 2009 13:58:17.0540 */OK
We are trying figure out how to turn the XACT_ABORT option to ON as Remedy is
trying to make its update/delete. I am wondering if anyone can give us some
suggestions on how to accomplish this.
Thanks,
Roger A. Nall
Manager, OSSNMS Remedy
T-Mobile, USA
Desk:972-464-3712 NEW
Cell: 973-652-6723
FAX:
sf49fanv AIM IM
RogerNall Yahoo IM
__Platinum Sponsor: RMI Solutions ARSlist: "Where the Answers Are" html___
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: RMI Solutions ARSlist: "Where the Answers Are"
