Rhyous

Ok, so at work I installed JasperServer and spent quite a lot of time in their documentation and could find the basic information of how to connect to a Microsoft SQL 2008 server.

JasperSoft has a nice installer that installs everything for you: JasperServer, Tomcat, Java, MySQL, etc…

The first problem was I had a 2008 R2 64 bit VM that I thought I would use that OS and their installer just crashed with MySQL errors. So I installed MySQL myself, and then reinstalled JasperServer and pointed to my MySQL install and it appeared to work better but there were still a lot of errors in normal use that I couldn’t find documentation on. So I am now using a 2003 R2 SP2 server and everything installed just fine, first time.

I thought Microsoft was really friendly about deploying their JDBC driver for SQL 2005/2008 and that anyone could distribute it as long as they just told Microsoft first, (maybe I am wrong and maybe MS charges to distribute it). Anyway, I expected JasperServer to install the JDBC driver for me (included in their all-encompassing package) but to my dismay, the driver wasn’t there. So that is fine, I was certain there would be a quick and easy document on how add SQL 2008 as a database. Yeah, three days later, still no documentation found….

Finally, with almost no help from the JasperServer documentation, I figured out that really it was Tomcat that needed the JDBC driver, not JasperServer. Well, it turns out that Tomcat no longer uses the CLASSPATH environment variable, so I just had to copy the SQL 2008 JDBC .jar file to a directory Tomcat did look at and I was done. It was simple.

1. Download the Microsoft JDBC driver: Microsoft SQL Server JDBC Driver
2. Extract to a good location. I chose c:\program files to extract to.
3. Copy the sqljdbc4.jar to your installation directory which by default is:
   c:\program files\jasperserver-pro-3.5.1\apache-tomcat\webapps\jasperserver-pro\WEB-INF\lib

4. Restart JasperServer (there is a shortcut in the start menu to restart it).
5. Log into JasperServer’s web site. http://YourServer:8080/jasperserver-pro
6. Go to View | Repository.
7. Click the second icon called Add Resource and select Data Source.
8. Choose JDBC Data Source and click next.
9. Provide any Name, Label, and Description.
10. For the driver put this:
    com.microsoft.sqlserver.jdbc.SQLServerDriver

11. For the URL put this:
    jdbc:sqlserver://ServerName:1433:databaseName=MyDatabase;

12. Enter the username and password.
13. Select a time zone.
14. Click Test and it should succeed.

Troubleshooting: If it doesn’t succeed, save anyway and restart the JasperServer and watch for any exceptions.

Wow that was easy once I figured it out. Too bad that the lack of good documentation resulted in me spending three days to figure out that I just needed to spend three seconds to copy a file.

JasperServer score card

Install = 8 (Minus two points for not working Server 2008 R2 64 bit)
Documentation = 0 (no points as so far I have not found a document that has been helpful yet.

Also I note that the JasperForge (the opensource part of JasperSoft) has a wiki, but it is nothing but headers and the data is pretty much blank.

I am not trying to knock JasperSoft with this post, but I am just trying to make sure that someone who tries to do the same thing ends up finding my post and getting their SQL 2008 server added as a data source rather quickly, instead of wading through the lack of documentation for three days.

Even to show my good will towards open source projects, I documented my steps on their wiki for them.
http://jasperforge.org/plugins/mwiki/index.php/Jasperserver/DataSources#Adding_a_Microsoft_SQL_Server_2008_Database_as_a_Data_Source