How can I use the MS JDBC driver with MS SQL Server 2008 Express?

Question

My configuration:

• windows XP SP3
• JDBC 2005
• MS SQL Server 2008 Express, exposed via tcp/ip on port 1433
• sqljdbc.jar in class path

I tried:

try {
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
    con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433/SQLEXPRESS2008;databaseName=Test;selectMethod=cursor", "sa", "");
}
catch (Exception e) {
    e.printStackTrace();
}

But it always throws an exception:

java.sql.SQLException: No suitable driver

I also tried the following urls:

localhost:1433/SQLEXPRESS2008

localhost/SQLEXPRESS2008

localhost

Same results. Any help?

Answer 1

You have the wrong URL.

I don't know what you mean by "JDBC 2005". When I looked on the microsoft site, I found something called the Microsoft SQL Server JDBC Driver 2.0. You're going to want that one - it includes lots of fixes and some perf improvements. [edit: you're probably going to want the latest driver. As of March 2012, the latest JDBC driver from Microsoft is JDBC 4.0]

Check the release notes. For this driver, you want:

URL:  jdbc:sqlserver://server:port;DatabaseName=dbname
Class name: com.microsoft.sqlserver.jdbc.SQLServerDriver

It seems you have the class name correct, but the URL wrong.

Microsoft changed the class name and the URL after its initial release of a JDBC driver. The URL you are using goes with the original JDBC driver from Microsoft, the one MS calls the "SQL Server 2000 version". But that driver uses a different classname.

For all subsequent drivers, the URL changed to the form I have here.

This is in the release notes for the JDBC driver.

Answer 2

1. Download the latest JDBC Driver (i.e. sqljdbc4.0) from Microsoft's web site

2. Write the program as follows:

   import java.sql.*;
   class testmssql
   {
       public static void main(String args[]) throws Exception
       {
           Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
           Connection      con=DriverManager.getConnection("jdbc:sqlserver://localhost:1433;
                   databaseName=chapter16","sa","123");//repalce your databse name and user name
               Statement st=con.createStatement();
           ResultSet rs=st.executeQuery("Select * from login");//replace your table name
           while(rs.next())
           {
               String s1=rs.getString(1);
               String s2=rs.getString(2);
               System.out.println("UserID:"+s1+"Password:"+s2);
           }
           con.close();
       } 
   }
   

3. Compile the program and set the jar classpath viz: set classpath=C:\jdbc\sqljdbc4.jar;.; If you have saved your jar file in C:\jdbc after downloading and extracting.

4. Run the program and make sure your TCP/IP service is enabled. If not enabled, then follow these steps:
   1. Go to Start -> All Programs -> Microsoft SQL Server 2008 -> Configuration tools -> SQL Server Configuration Manager
   2. Expand Sql Server Network Configuration: choose your MS SQL Server Instance viz. MSQSLSERVER and enable TCP/IP.
   3. Restart your MS SQL Server Instance. This can be done also from the right click menu of Microsoft SQL Server Management Studio at the root level of your MS SQL server instance

Answer 3

If your databaseName value is correct, then use this: DriverManger.getconnection("jdbc:sqlserver://ServerIp:1433;user=myuser;password=mypassword;databaseName=databaseName;")

Answer 4

The latest JDBC MSSQL connectivity driver can be found on JDBC 4.0

The class file should be in the classpath. If you are using eclipse you can easily do the same by doing the following -->

Right Click Project Name --> Properties --> Java Build Path --> Libraries --> Add External Jars

Also as already been pointed out by @Cheeso the correct way to access is jdbc:sqlserver://server:port;DatabaseName=dbname

Meanwhile please find a sample class for accessing MSSQL DB (2008 in my case).

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class ConnectMSSQLServer
{
   public void dbConnect(String db_connect_string,
            String db_userid,
            String db_password)
   {
      try {
         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
         Connection conn = DriverManager.getConnection(db_connect_string,
                  db_userid, db_password);
         System.out.println("connected");
         Statement statement = conn.createStatement();
         String queryString = "select * from SampleTable";
         ResultSet rs = statement.executeQuery(queryString);
         while (rs.next()) {
            System.out.println(rs.getString(1));
         }
         conn.close();
      } catch (Exception e) {
         e.printStackTrace();
      }
   }

   public static void main(String[] args)
   {
      ConnectMSSQLServer connServer = new ConnectMSSQLServer();
      connServer.dbConnect("jdbc:sqlserver://xx.xx.xx.xxxx:1433;databaseName=MyDBName", "DB_USER","DB_PASSWORD");
   }
}

Hope this helps.

Answer 5

Named instances?

URL: jdbc:sqlserver://[serverName][\instanceName][:portNumber][;property=value]

Note: backward slash

Answer 6

You can try the following. Works fine in my case:

1. Download the current jTDS JDBC Driver
2. Put jtds-x.x.x.jar in your classpath.
3. Copy ntlmauth.dll to windows/system32. Choose the dll based on your hardware x86,x64...
4. The connection url is: 'jdbc:jtds:sqlserver://localhost:1433/YourDB' , you don't have to provide username and password.

Hope that helps.