J2EE

SQL Server 2000 Driver for JDBC: Connection reset by peer

23 Comments

If you’re using the SQL Server 2000 Driver for JDBC and Commons DBCP and you either you see this error in your logs:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: socket write error

and / or your application stops functioning after your SQL Server is restarted or the connection between the your application and the SQL Server is terminated, then you need to add this to your JDNI setup:

<parameter>
  <name>validationQuery</name>
  <value>select $somecolumn FROM $sometable</value>
</parameter>

where the value is a query that returns at least one row from your database. According to the documentation, the validation query is:

“… invoked in an implementation-specific fashion to determine if an instance is still valid to be returned by the pool. It will only be invoked on an “activated” instance.”

In my testing, if I add the validationQuery parameter to my JNDI setup, restart the servlet container (in this case Tomcat), request a page and then stop SQL Server, an error is returned (as expected). After starting SQL Server and requesting another page, the application returns to normal usage (which doesn’t happen if the validationQuery parameter is not present).

23 thoughts on “SQL Server 2000 Driver for JDBC: Connection reset by peer”

  4. Hi,

    We are using JBOSS application server with
    MS SQL server JDBC driver. We are getting the Connection Reset by peer problems. Can you please guide me how to include the

    validationQuery

    We are dependent on JBOSS datasource. JBOSS provides one xml file where we need to specify the datasourc name , jdbc driver,url, user id and password.
    Thanks for help
    Sarveswara Rao

    Reply

  5. Connection reset errors are caused by a number of things.
    We moved from the MS SQL Server driver to jTds and it helped but there were two other issues of greater importance to remove the error.
    First are the network interface cards in your windows server. Most of them have the transmit and receive buffers set to 128 or 256.
    You can set them to 1024 (either in the registry or in the properties of the network card from control panel)
    and this will eliminate about 1/3rd of the errors.
    the next thing you can do is to optimize the TCP-IP settings for Windows. Be default you are lucky to get a NIC that can sustain 1.5Mbps – even in a server platform like Dell or SuperMicro.
    This link will give you the details on what to set, and where:
    http://rdweb.cns.vt.edu/public/notes/win2k-tcpip.htm
    Once we set the TCPIP values and increased the buffers on our DB server and our app/web servers our throughput went up over 4 times and the errors went away.
    Lastly, you need to check your Java code.
    App servers run servlets on different threads, but the DBCP pool is a singleton.
    After you close your DB connection it gets returned to the pool (critical that you call ResultSet.close() and Connection.close() )
    and you need to set the connection object to NULL immediately.
    There is a doc in the Apache DBCP docs about why this is the case. I have found this is needed for any database pool using JDBC.
    With the TCP mods, and the change in our Java code, we have no more connection issues no
    matter what JDBC driver we have tried (and have tried 3 now!)

    Reply

  6. I have the same problem

    java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: socket write error

    I could solve my problem using the check-valid-connection-sql tag, addinng the next line to the *-ds.xml file

    %check-valid-connection-sql>select 1 %/check-valid-connection-sql>

    I’m using the MS SQL Server JDBC driver

    Reply

  7. Hi,
    Just now freshly install the tomcat 5. i don’t the connection pooling for sql server. how can i get the datasource in the jsp. let me know please.

    Reply

  8. Is it Okay, by switching from “Ms-sql Native Driver” to “jtds ” in point of Performance…

    plz.., feed me back…….

    Reply

  9. thanks so much for this. we have been looking for a fix to this problem for months now and this worked perfectly. thanks!

    rj

    Reply

  10. I use Tomcat 5.0 application server , database MS SQl server 2000 using Sql Server driver for JDBC.

    Before I was using ODBC Driver.But after changing to JDBS sql server driver, Some of the queries are not running properly.They are shouwing for Syntax errors.

    the error is like, “syntax near the word ‘as'”.(the query is has a subquery getting the value using ‘as’ clause in MSSQL Server)

    plz give me the solution.asap,

    rgds,
    nagaraj.

    Reply

  12. I am facing the JDBC Connection reset by peer: Socket write error,

    Sometimes module working fine, yesterday module tested in production server twice within 1 hr gap,

    First test successfully finished, but second one was not.

    The data insertion is only the problem, module may insert any number of records at single insertion.

    I am using Sql server 2000 server with Microsoft jdbc driver and
    JBoss server.

    Our db server and jboss server (Hardware) are highly configured.

    Reply

  14. Hi,

    The below error occurring with jboss 4 and sql server 2005 combination but perfectly working with sql server 2000.

    com.microsoft.sqlserver.jdbc.SQLServerException: equal .
    12:59:04,079 INFO [STDOUT]

    message : equal .
    State : S0001
    err code : 50000

    My java code is below :

    public Object cancelChargeRun( String chargerun , String type){
    Connection con = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    Boolean ret = Boolean.FALSE;
    int i1 = -1;
    int i2 = -1;
    int level = 0;
    try{
    System.out.println(“Cancelling the chargerun.”);
    System.out.println(“charge run : “+ chargerun);
    System.out.println(“type : “+ type );
    // String query1 = “UPDATE CNTRMOVEMENTDETAIL SET RUN = 0, CHARGERUN = ” WHERE CHARGERUN = ? “;
    String query2 = “UPDATE DETADVCHARGERUN SET STATUS = ‘CANCELED’ WHERE CHARGERUN = ? “;//C for canceled
    String query3 = “DELETE FROM LRPDEMURRAGEEDINOS WHERE CHARGERUN = ? “;
    String query4 = “DELETE FROM LINEDETENTIONADVANCEMENT WHERE CHARGERUN = ? “;

    try{
    level = 1;
    con = new lrp.serverutils.refs.ServerObject().getConnection();

    level = 2;
    pst = con.prepareStatement( query2 , ResultSet.TYPE_SCROLL_INSENSITIVE , ResultSet.CONCUR_READ_ONLY );
    pst.setString( 1 , chargerun );

    level = 3;
    i1 = pst.executeUpdate();
    level = 4;
    if( i1 >= 0 ){
    pst.close();
    pst = con.prepareStatement( query3 , ResultSet.TYPE_SCROLL_INSENSITIVE , ResultSet.CONCUR_READ_ONLY );
    pst.setString( 1 , chargerun );

    level = 5;
    i2 = pst.executeUpdate();
    pst.close();

    level = 6;
    if( i2 >= 0 ){
    pst = con.prepareStatement( query4 , ResultSet.TYPE_SCROLL_INSENSITIVE , ResultSet.CONCUR_READ_ONLY );
    pst.setString( 1 , chargerun );

    level = 7;
    i2 = pst.executeUpdate();
    pst.close();
    }
    }
    System.out.println(“Line Container Detention: Charge Run Canceled.”);
    }catch( SQLException se ){
    System.out.println(“Level Reached : “+ level);
    System.out.println(“level is : “+level+”\nSqlException cancelchargerun in Container DetetnionSessionBean.\n”+se);
    this.mailException( “advancement”,chargerun, “level is : “+level+”\nSqlException cancelchargerun in Container DetetnionSessionBean.\n “, se );
    }

    if( i2 >= 0 )
    ret = Boolean.TRUE;

    return ret;
    } catch( Exception exp ){
    System.out.println(“Exception CancelChargeRun ContainerDetentionSessionBean.\n”+exp);
    return ret;
    }

    error occurring at level = 3 assignment.

    Hope any one help.

    Thanks & regards,
    Santhakumar S

    Reply

  15. I also faced the same problem and did the following :

    1. Rebooted the machine.
    2. open mssql-ds.xml and uncomment the tag..some arbitrary sql
    and give an sql which returns atleast one row from your database.
    It should work.

    Reply

  16. Hello every body
    when i m connecting throught “com.microsoft.jdbc.sqlserver.SQLServerDriver”
    then this Exception —–
    [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.

    but if i use “odbc” then it works properly

    if any one of u have a solution for this prob then plz help me

    ThanKs

    Reply

  18. i have created an applet already i signed that applet that applet is retriveing data from data base and showing result in Appletviewer through,it is retriveing data from ms acees.after signing the applet now it it is displaing at internet explorer but when i want to connect that applet MS SQL server 2000 it is only coming i appletviewer but not in internet explorer it is giving
    ava.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
    Error ,i SQl server is in separate machine that machine port no 1433 is disable and everything is right why it is giviing this type of error plz tell me

    Reply

  19. Hai,
    I have got the error during doing jdbc programe
    the error is java.sql.SQLException[Microsoft][SQLServer 2000]Driver error establishing scoket

    and i was checked the tcp/ip proctol enabled it was working fine but i don’t know why i got this error plz help me

    Reply

  21. i am configuring my appServer and this error stop my connection pool from pinging.

    An error has occurred.
    Operation ‘pingConnectionPool’ failed in ‘resources’ Config Mbean. Target exception message: Connection could not be allocated because: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *