RE: "The operation was cancelled at the user's request" error during mass insertions
From: Carb Simien [MSFT] (CarbinoS_at_online.microsoft.com)
Date: 11/22/04
- Previous message: Carb Simien [MSFT]: "Re: why excute query through JDBC much slow than query analyzer?"
- In reply to: Jeeji: ""The operation was cancelled at the user's request" error during mass insertions"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 22 Nov 2004 20:37:06 GMT
--------------------
| From: jeejim@india.hp.com (Jeeji)
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: "The operation was cancelled at the user's request" error during
mass insertions
| Date: 16 Nov 2004 22:11:17 -0800
| Organization: http://groups.google.com
| Lines: 111
| Message-ID: <8b00e6ae.0411162211.12615047@posting.google.com>
| NNTP-Posting-Host: 128.88.255.123
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1100671877 17407 127.0.0.1 (17 Nov 2004
06:11:17 GMT)
| X-Complaints-To: groups-abuse@google.com
| NNTP-Posting-Date: Wed, 17 Nov 2004 06:11:17 +0000 (UTC)
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwi
x.com!newsfeed.cwix.com!newsfeed.yul.equant.net!newsread.com!newsprint.newsr
ead.com!postnews.google.com!not-for-mail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6479
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi,
|
| I am facing a strange problem using SQL server 2000 version with JDBC
| driver for SQL server 2000 version SP1/SP3. I have a table with
| name-value pairs and I populate the table from a properties file. When
| I am doing the insertions in a for loop, it is failing intermittently
| with the error with SP1 version:
|
| java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]The
| operation
| was cancelled at the user's request.
| at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown
| Source
| )
| at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown
| Source)
| at
com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown
| Source)
| at
com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unkn
| own Source)
| at
com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown
| Sour
| ce)
| at
com.microsoft.jdbc.sqlserver.tds.TDSRequest.processOutstandingCancelA
| cknowledgements(Unknown Source)
| at
com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown
| Sour
| ce)
| at
com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType
| (Unknown Source)
| at com.microsoft.jdbc.base.BaseStatement.getNextResultType(Unknown
| Sourc
| e)
| at
com.microsoft.jdbc.base.BaseStatement.executeUpdateInternal(Unknown
| S
| ource)
| at
com.microsoft.jdbc.base.BasePreparedStatement.executeUpdate(Unknown
| S
| ource)
|
| The error with SP3 version:
|
| java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
| JDBC]Execution time
| out expired.
| at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown
| Source
| )
| at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown
| Source)
| at
com.microsoft.jdbc.base.BaseStatement.preprocessExceptionFromCommonEx
| ecute(Unknown Source)
| at
com.microsoft.jdbc.base.BaseStatement.executeUpdateInternal(Unknown
| S
| ource)
| at
com.microsoft.jdbc.base.BasePreparedStatement.executeUpdate(Unknown
| S
| ource)
|
|
| The source code is given below:
|
| PreparedStatement pstmt = null;
| pstmt = con.prepareStatement("INSERT INTO Configuration(uid, name,
| value) VALUES (?,?,?);");
| FileInputStream fileSettings = new FileInputStream("test.properties");
| Properties props = new Properties();
| props.load(fileSettings);
|
| // get the name and value for each property in the file
| for(Enumeration en = props.propertyNames();en.hasMoreElements();)
| {
| String name = (String) en.nextElement();
| String value = props.getProperty(name);
|
| boolean exist = isAlreadyPresent(name);
| if(!exist)
| {
| nextUID++;
| setNextUid(nextUID); //update nextUid in the table Uid
| pstmt.setLong(1,nextUID); // update new uid
| pstmt.setString(2,name);
| pstmt.setString(3,value);
| pstmt.executeUpdate();
| }
| }
| con.commit();
| }
| catch(SQLException ex)
| {
| ex.printStackTrace();
| }
| finally
| {
|
| pstmt.close();
| }
| }
|
| The problem was consistently reproduced after I commented the line
| setNextUid which is in between the consecutive insertions to the same
| table. Please also note that the problem vanished completely when I
| gave Thread.sleep(100) in between each of the inserts. I am suspecting
| this is a driver related bug though I may be completely wrong. Please
| advice on any slution. Is there a driver version where this bug has
| been fixed ? Any pointers on this would be helpful.
|
| IMPORTANT: This is a single-threaded code.
|
| Thanks a lot.
| -Jeeji
|
The error message suggests that the query being executed is simply taking
too long to complete. Can you reproduce this problem using an empty
Configuration table and one or two properties in test.properties? I can
run this code (after mocking up the functions isAlreadyPresent and
setNextUid) with two sample tables Configuration and Uid and not see any
errors.
test.properties file:
prop1 1
prop2 2
prop3 3
T-SQL code:
USE jdbc
GO
DROP TABLE Configuration
DROP TABLE Uid
GO
CREATE TABLE Configuration(uid int, name varchar(50), value varchar(50))
CREATE TABLE Uid(uid int)
INSERT INTO Uid(uid) VALUES(1)
GO
Java code:
import java.sql.*;
import java.text.*;
import java.io.*;
import java.util.*;
public class query
{
public static void main(String[] args) throws Exception
{
// Load the JDBC driver
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
// Connect to SQL Server
Connection conn = null;
String url =
"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=JDBC;";
conn = DriverManager.getConnection(url, "sa", "password");
int nextUID = 0;
conn.setAutoCommit(false);
PreparedStatement pstmt = null;
try
{
// Prepare an adhoc query
pstmt = conn.prepareStatement("INSERT INTO Configuration(uid, name,
value) VALUES (?,?,?)");
// Read the properties file
FileInputStream fileSettings = new FileInputStream("test.properties");
Properties props = new Properties();
props.load(fileSettings);
// get the name and value for each property in the file
for(Enumeration en = props.propertyNames(); en.hasMoreElements(); )
{
String name = (String)en.nextElement();
String value = props.getProperty(name);
boolean exist = isAlreadyPresent(name);
if(!exist)
{
nextUID++;
// Workaround #1 -> comment out the line below
setNextUid(nextUID); // update nextUid in the table Uid
pstmt.setLong(1, nextUID); // update new uid
pstmt.setString(2, name);
pstmt.setString(3, value);
pstmt.executeUpdate();
}
// Workaround #2 -> uncomment the line below
//Thread.sleep(100);
}
conn.commit();
}
catch (SQLException sqlEx)
{
while (sqlEx != null)
{
System.out.println("SQLState: " + sqlEx.getSQLState());
System.out.println("Message: " + sqlEx.getMessage());
System.out.println("Error code: " + sqlEx.getErrorCode());
sqlEx = sqlEx.getNextException();
System.out.println();
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
// Cleanup
if (pstmt != null)
pstmt.close();
pstmt = null;
if (conn != null)
conn.close();
conn = null;
}
}
public static void setNextUid(int nextUID) throws Exception
{
Connection con = null;
PreparedStatement pstmt = null;
try
{
con =
DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;Datab
aseName=JDBC;", "sa", "password");
pstmt = con.prepareStatement("UPDATE Uid SET uid=?;");
pstmt.setLong(1, nextUID);
pstmt.executeUpdate();
}
catch (SQLException sqlEx)
{
while (sqlEx != null)
{
System.out.println("SQLState: " + sqlEx.getSQLState());
System.out.println("Message: " + sqlEx.getMessage());
System.out.println("Error code: " + sqlEx.getErrorCode());
sqlEx = sqlEx.getNextException();
System.out.println();
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
// Cleanup
if (pstmt != null)
pstmt.close();
pstmt = null;
if (con != null)
con.close();
con = null;
}
}
public static boolean isAlreadyPresent(String name) throws Exception
{
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
boolean ret = false;
try
{
con =
DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;Datab
aseName=JDBC;", "sa", "password");
pstmt = con.prepareStatement("SELECT * FROM Configuration (NOLOCK) WHERE
name = ?");
pstmt.setString(1, name);
rs = pstmt.executeQuery();
if(rs.next())
ret = true;
else
ret = false;
}
catch (SQLException sqlEx)
{
while (sqlEx != null)
{
System.out.println("SQLState: " + sqlEx.getSQLState());
System.out.println("Message: " + sqlEx.getMessage());
System.out.println("Error code: " + sqlEx.getErrorCode());
sqlEx = sqlEx.getNextException();
System.out.println();
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
// Cleanup
if (rs != null)
rs.close();
rs = null;
if (pstmt != null)
pstmt.close();
pstmt = null;
if (con != null)
con.close();
con = null;
}
return ret;
}
}
Be sure that you are explicitly closing all ResultSet, Statement, and
Connection objects in all of your functions to ensure that you are not
holding locks unnecessarily long on the tables. You can run "sp_who2" and
"sp_lock" in Query Analyzer while reproducing the problem to observe the
locks in effect during this time. If you see a WAIT status for any of the
locks related to your Java code (correlate the SPID from sp_lock with the
SPID from sp_who2), it could indicate standard blocking.
Are you setting the query timeout explicitly in your code? How many
seconds elapse from when you execute the query until you see the errors?
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
- Previous message: Carb Simien [MSFT]: "Re: why excute query through JDBC much slow than query analyzer?"
- In reply to: Jeeji: ""The operation was cancelled at the user's request" error during mass insertions"
- Messages sorted by: [ date ] [ thread ]
