To access data on Microsoft SQL Server 2000, you must first install and configure the SQL Server 2000 Driver for JDBC, a Type 4 JDBC driver that provides connectivity for an enterprise Java environment. This driver provides JDBC access to both the 32- and 64-bit versions of SQL Server 2000 through any Java-enabled applet, application, or application server.

Prerequisites

This article only applies to Microsoft SQL Server 2000. Each different SQL Server service pack has a unique driver, all of which are freely available to all licensed SQL Server 2000 customers through the downloads area of the Microsoft Web site.

Note: When creating metadata for use with the Web Ad hoc Query Interface, be sure to use JNDI as your method of access.

Instructions

  1. Download the MS SQL JDBC driver from Microsoft.com. Make sure you select the version appropriate for your version of SQL Server 2000 (the original release, or SP1, SP2, or SP3).
     

 

  1. Run the setup.exe file you just downloaded or download UNIX version, and follow the on-screen instructions to complete installation.
     
  2. To connect the Report Designer, Pentaho Server, and Pentaho Metadata Editor to an SQL Server Database, copy the msbase.jar, mssqlserver.jar, and msutil.jar (just sqljdbc.jar in SQL Server 2005) files from <installed-drive>:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib to all three of these directories:
     
  3. Stop the Tomcat server.
     
  4. Go to the <installed-drive>:\pentaho\bi-server\tomcat\webapps\pentaho\META-INF directory and open the context.xml file with a plain text or XML editor (in BI server versions previous to 2.x change this file: <installed-drive>:\pentaho\bi-server\tomcat\conf\server.xml).
     
  5. Scroll down to the bottom of the file.
     
  6. Duplicate the last Resource name line in the list by selecting the text, then using the copy and paste functions.
     
  7. In the duplicated line, change the user name, password, and url parameters to your specifications. Also change the driverClassName value to com.microsoft.jdbc.sqlserver.SQLServerDriver.
     
  8. Start the Tomcat server.
     
  9. Open the jdbc.properties file for the Pentaho Metadata Editor in the <installed-drive>:\pentaho\metadata-editor\simple-jndi\ directory.
     
  10. Add the block of text from the jdbc.properties example below to the end of the file, making changes appropriate to your situation.
     
  11. Save all files and close your text editor.
     

Results

You should now have access to your MS SQL database.

Example code for the context.xml file


<Resource name="jdbc/Microsoft" auth="Container" type="javax.sql.DataSource"
		factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
		maxWait="10000" username="reporting" password="xxx"
		driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" url="jdbc:sqlserver://servername;DatabaseName=databaseame"
		validationQuery="select count(*) from sys.indexes"/>


Example code for the server.xml file (note this only applys to version 1.7 of the BI server)


<Resource name="jdbc/NPDemoData" auth="Container" type="javax.sql.DataSource" maxActive="20"
    maxIdle="5" maxWait="10000" username="pentaho" password="pentaho"
    factory="org.apache.commons.dbcp.BasicDataSourceFactory" driverClassName="com.microsoft.jdbc.sqlserver.SQLServerDriver"
    url="jdbc:microsoft:sqlserver://localhost:1433" />

If using Pentaho BI Server version 2.x you can also add the data source using the Administration Console.

See this link: http://wiki.pentaho.com/display/PentahoDoc/.04 Configuring Data Sources


Example Code for Editing the jdbc.properties File for the Pentaho Metadata Editor


NPDemoData/type=javax.sql.DataSource
NPDemoData/driver=com.microsoft.jdbc.sqlserver.SQLServerDriver
NPDemoData/url=jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=NPDemoData
NPDemoData/user=pentaho
NPDemoData/password=pentaho


Related Items