Can't use SQL Server db as data source: ClassNotFoundException: com.microsoft.sqlserver.jdbc...

[jdlh]

Hi,

I've been trying to use the JasperServer 4.7 eval edition, and connect to our existing SQL Server database.  It hasn't worked for me, despite trying several workarounds mentioned in various forum posts. Perhaps some of you can point out what I'm missing.

I created a data source in JasperServer as the user jasperadmin.  The parameters are:

JDBC Driver: MS SQLServer (2005) (com.microsoft.sqlserver.jdbc.SQLServerDriver)

JDBC URL: jdbc:sqlserver://myserver\myinstance:1433;databaseName=mydatabase

When I push the Test Connection button in the JasperServer "Edit Data Source" page, I get a brief glimpse of a yellow bar saying, "Connection Failed", with no visible way to diagnose the problem.

When I run iReport 4.7, I see this data source in a pulldown menu of datasources at centre of the top toolbar. When I push the "Report Datasources" button, and then select my data source from the dialog "Connections / Datasources", and push the "Modify" button, I get a "Database JDBC Connection" dialog.  When I press the "Test" button in the JDBC connection dialog, I get the following error message:

ExceptionMessage:    java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriverLevel:    SEVEREStack Trace:com.microsoft.sqlserver.jdbc.SQLServerDriver    com.jaspersoft.ireport.designer.connection.JDBCConnection.loadDriver(JDBCConnection.java:63)    com.jaspersoft.ireport.designer.connection.JDBCConnection.getConnection(JDBCConnection.java:126)    com.jaspersoft.ireport.designer.connection.JDBCConnection.test(JDBCConnection.java:447)...[snip]...

This seems to be saying that iReport can't find the JDBC driver for SQL Server. Or more precisely, the class com.microsoft.sqlserver.jdbc.SQLServerDriver which is provided by the driver.

Is SQL Server installed with the JasperReports 4.7 eval edition for Windows, by default?  The installation guide doesn't explicitly say. Given that there's no instructions on "how to add SQL Server support to the default installation", the implicit message is that such support is part of the default install. The sentence, "The installer provides the JDBC drivers for all databases listed in the following table except MySQL." (JasperReports Server Install Guide 4.7 p.59) also implies this.

But maybe not.  So, following hints in a couple of forum posts, I copied <js-install>/buildomatic/conf_source/db/sqlserver/jdbc/sqljdbc-1.6.jar to <js-install>/apache-tomcat/webapps/jasperserver-pro/WEB-INF/lib/ and to <js-install>/apache-tomcat/lib/ . I stopped and restarted the JasperServer service and the iReports application. No change to the symptoms.

I downloaded the latest Microsoft JDBC driver from http://msdn.microsoft.com/en-us/data/aa937724.aspx , and copied their sqljdbc.jar and sqljdbc4.jar to the same two lib directories. Stopped and restarted. No change.

I see that, after installing Java 1.7 from Oracle, and JasperServer 4.7, I still had no classpath set. I added an environment variable CLASSPATH to Windows, with a value of C:\Program Files\jasperreports-server-4.7\buildomatic\conf_source\db\sqlserver\j
dbc . No change.

According to "jar tf", the archive sqljdbc-1.6.jar has a file named com\microsoft\sqlserver\jdbc\SQLServerDriver.class . 

So, after providing Tomcat and JasperServer all these different files which appear to have class SQLServerDriver, how come iReport is giving a java.lang.ClassNotFoundException of com.microsoft.sqlserver.jdbc.SQLServerDriver?

And, to get to my underlying question, how can I use my existing SQL Server database in the JasperServer 4.7 eval edition?

I'm running Microsoft Windows XP SP3, if that makes a difference.

Thanks in advance for any insight you can offer.

[oesina]

Hi jdlh,
iReport is a separate java desktop application, and it doesn't look up to Tomcat classpath or Applications deployed on Tomcat. The installation Guide covers server document but doesn't talk much about iReport configuration.
WEB-INF/lib will allow you to create MSSQL datasource on Jasperserver repository and deploy your reports to the server using that jasperserver data connection.

I believe when you added a new datasource in iReport the driver name appeared in RED, that means driver was not found. Once we have the driver, it will appear in BLACK.

To add the driver to iReport classpath, follow below in iReport:
1. Use top menu Tools-Options
2. in Ireport section open Classpath tab
3. Add Jar and find location of your driver sqljdbc-1.6.jar
4. Validate that when you create new connection the MS SQL Server 2005 appears in BLACK.

You don't need to restart iReport after you added new classpath.
You don't need to re-create your MS SQL connection if all attributes are correct, it should start working.

Good luck from there!

Post Edited by oesina at 08/03/2012 03:10

[jdlh]

> I'm running Microsoft Windows XP SP3, if that makes a difference.

FYI, I've also reproduced the problem with JasperServer 4.7 eval version, Windows x64, running on a VM with Windows Server 2008 R2 as guest OS.

The only Java VM I see on this system is the one installed with JasperServer:

C:\Users\Administrator>"c:\Program Files\jasperreports-server-4.7\java\bin\java.exe" -versionjava version "1.6.0_18"Java(TM) SE Runtime Environment (build 1.6.0_18-b07)Java HotSpot(TM) 64-Bit Server VM (build 16.0-b13, mixed mode)

[jdlh]

Thank you, oesina, for this insight. I agree, the Installation Guide doesn't say much about iReport. It would be nice if it did.

 

When I use Tools... Options... iReport... Classpath... Add jar to point to sqljdbc-1.6.jar, then sure enough, the connection name MS SQL Server 2005 appears in black, and I no longer get the ClassNotFoundException. Progress!

 

Now I'm getting connection refused and timeout errors from iReport, and I'll talk to my administrator to be sure I have the system coordinates correct.

 

All this helps iReport only; JasperServer is still not connecting to my SQL Server database.

 

Thanks again for your help.

[oesina]

Hi jdlh,

Do you still see ClassNotFound in the Tomcat log?

Guess your JasperServer will also have connection refused/timeout error. Check Tomcat logs for more details.

Cheers!

[jdlh]

Oesina, thank you for your suggestions. I have successfully connected from JasperReports Server and from iReport.

The last step for iReport was to find out from my admin that we are using a non-standard port number on our SQL Server database server. I added the instance name and corrected the port, and iReport was able to connect.

To make JasperReports Server work, I first fixed the port number in the JDBC URL. Then, I found the Tomcat error logs in the directory: <js-install>\apache-tomcat\logs\ . The file that helped me was the most recent one, named in my case stderr_20120813.log. The error message I found there was:

Aug 13, 2012 12:40:59 PM com.microsoft.sqlserver.jdbc.SQLServerConnection <init>SEVERE: Java Runtime Environment (JRE) version 1.6 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.

By this point I had four sqljdbc*.jar files each in my apache-tomcat\lib and apache-tomcat\webapps\jasperserver-pro\WEB-INF\lib directories: sqljdbc.jar, sqljdbc4.jar, sqljdbc-1.5.jar, sqljdbc-1.6.jar. I got rid of all except sqldbc.jar, from each lib directory.

On stopping and restarting the JasperReports Server service, I found that my SQL Server data source, now passed when I used the "Test Connection" button in the connection definition dialog box.

I wonder why I didn't see this error message before. I speculate that this message comes from the Microsoft-developed sqljdbc.jar, and that there was some contention between sqljdbc4.jar (which seems to work) and the other sqljdbc*.jar files. So, I speculate that it's better to put in just one driver file, and to be sure it's the right one.