TechnowledgeBase

You’re building a Java application that needs to connect to a Microsoft SQL 2005 database, and installing the JDBC driver on the database server isn’t an option for whatever reason. After some quick research, you stumble across the best, fastest, and easiest way to connect (barring the JDBC): the open-source JTDS package. You download it, install it, and start slinging code…

…only when you run the app, you get a mysterious message like: “unable to get information from sql server.” WTF!? Your first few minutes (hours?) of troubleshooting leads you people who insist that the database server isn’t configured to accept connections. Only it IS configured, and your connection URL is pristine. You’ve checked everything against the documentation and it still won’t run. Now what?

The Problem:

Your Jtds/Java/MSSQL2005 app won’t run. Java can’t “get information” from the server even though you’ve properly configured the server to accept the connection. Now what?

The Solution:

The real problem is your URL. Yeah, I know you’ve checked it already, but the JTDS documentation is full of dirty lies, not entirely accurate about how to connect to a MSSQL 2005 server.

The documentation says the URL looks like this:


jdbc:jtds:<server_type>://<server>[:<port>][/<database>][;<property>=<value>[;...]]

So far, so good. Only you’ve got a named instance on your server. Instead of connecting to SERVERNAME, you have to connect to SERVERNAME/INSTANCE. Well, a little further down you see how to connect to a named instance of SQL Server using the “instance paramater” as part of the URL:

instance -
Named instance to connect to. SQL Server can run multiple so-called “named instances” (i.e. different server instances, running on different TCP ports) on the same machine. When using Microsoft tools, selecting one of these instances is made by using
“<host_name><instance_name>” instead of the usual “<host_name>”. With jTDS you will have to split the two and use the instance name as a property.

Which might lead you to write the following code:

public void connect2(){
String url="jdbc:jtds:sqlserver://IMPMWASHINGTON/ir34;instance="SQLEXPRESS";
try{
dbConn=DriverManager.getConnection(url,dbUser,dbPassword);
}//end try
catch(SQLException sqle){
}//end catch;
}//end connect


According to the JTDS documentation, this SHOULD work. It doesn’t. If you check the JTDS FAQ, you see something that directly addresses your situation:

Why do I get a java.sql.SQLException:
trying to connect to an SQL Server instance?
The exception you are getting is usually caused by a timeout. When connecting to named instances jTDS needs to connect via UDP to port 1434 to get information about available SQL Server instances. While doing this it times out, throwing the exception you see (which means that jTDS was not able to get information about the running instances).

So off you go to add port 1434 to your URL, producing a string that looks something like this:

url="jdbc:jtds:sqlserver://IMPMWASHINGTON:1434/IR34;instance=SQLEXPRESS"; 
 

Did that work?

It didn’t work for me either. What the documentation and the FAQ left out is that MSSQL doesn’t play nice with the whole UDP port thing. The server “supposedly” listens on port 1434, and if you connect on that port and give it the name of your instance, it will return the port number that you need to connect to. But it doesn’t. If it did, you wouldn’t be here reading this. In order to get your code to work, you have to explicitly specify the port on which your named SQL instance is running. Find the port number, plug it into the code like so:

url="jdbc:jtds:sqlserver://IMPMWASHINGTON:1565/IR34";

Notice I put the port number (”1565″) after the server name (where “1434″ used to be) and I got rid of the instance name. The “instance” parameter isn’t needed if you’re going to specify the port number directly.

NOW your database connection code should work.

Popularity: 22% [?]

This entry was posted on Tuesday, June 5th, 2007 at 7:00 am and is filed under Java. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.