Generating BIRT reports using Microsoft SQL Server

Marc is currently writing the complementary work for his apprenticeship as “Fachinformatiker Anwendungsentwicklung”. His work deals with generating BIRT reports using a Microsoft SQL Server 2005 Express as database backend.

Using the Microsoft JDBC is not as easy as it should be:
First you have to download the latest Microsoft SQL Server 2005 JDBC driver. Install the driver anywhere on your harddisk.

Now you must manually add the sqljdbc.jar to your CLASSPATH. (e.g. c:windowssystem32;c:program filesMicrosoft SQL Server JDBCsqljdbc.jar). If you do not so, BIRT Designer will throw an error “No suitable driver” while adding a new data source, although you have added the JDBC-JAR in the BIRT data source assistent.

The next step is enabling TCP/IP for Microsoft SQL Server 2005 Express: Load Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > Microsoft SQL Server Configuration Manager.

Enable TCP/IP for your database instance under “Protocol for “. Enable TCP/IP for your network adapter, disable dynamic ports and set the TCP port manually to 1433. You must disable the “Dynamic Port” field under “All” (set it to “”, not “0”!).

Restart your SQL Server instance and add a new data source in BIRT. The JDBC connection string has to be like this “jdbc:sqlserver://$HOST\SQLEXPRESS:1433;databaseName=$databaseName”. Consider the use of double-slashes and -backslashes!

The connection should be established now.