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;")