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
- 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).
- Run the setup.exe file you just downloaded or download UNIX version, and follow the on-screen instructions to complete installation.
- 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:
- <installed-drive>:\pentaho\report-designer\lib\jdbc
- <installed-drive>:\pentaho\bi-server\tomcat\webapps\pentaho\WEB-INF\lib
- <installed-drive>:\pentaho\metadata-editor\libext\JDBC\
- Stop the Tomcat server.
- 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).
- Scroll down to the bottom of the file.
- Duplicate the last Resource name line in the list by selecting the text, then using the copy and paste functions.
- 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.
- Start the Tomcat server.
- Open the jdbc.properties file for the Pentaho Metadata Editor in the <installed-drive>:\pentaho\metadata-editor\simple-jndi\ directory.
- Add the block of text from the jdbc.properties example below to the end of the file, making changes appropriate to your situation.
- 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