Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

 
 

Connecting Oracle BI Publisher to SQL Server

Expert Oracle Tips by Steve Callan

March 25, 2009

Connecting Oracle BI Publisher to SQL Server

By Steve Callan

One of the main features of Oracle Business Intelligence Publisher (BIP) is its ability to connect to pretty much every major RDBMS on the market. By default, the configuration for connecting to the Oracle RDBMS is present out of the box. Being a Java-based application at heart, the connection setup to Oracle uses JDBC. Letís venture out a bit and establish a connection to SQL Server.

Overall, what weíre trying to do is create another data source. The types of data sources appear under the Admin tab.

The types of data sources appear under the Admin tab

Clicking the JDBC Connection link and then the Add Data Source link brings up the interface below.

As far as the default Driver Type is concerned with respect to Oracle, been there done that. Expand the drop down list to see what the other currently support database sources are.

Expand the drop down list to see what the other currently support database sources are

Select the Microsoft SQL Server 2005 option and note the similarity as far as the JDBC connection string is concerned.

NOTE: In some references at My Oracle Support and OTN, you may see the driver class written as com.microsoft.jdbc.sqlserver.SQLServerDriver. Accept the default string BIP provides, which uses com.microsoft.sqlserver.jdbc.SQLServerDriver.

Accept the default string BIP provides, which uses com.microsoft.sqlserver.jdbc.SQLServerDriver

So far, this looks to be trivial. Iíve installed SQL Server 2005 on the same PC hosting the BIP installation, and have gone a bit further by installing some sample databases. The database of interest here will be AdventureWorks, which you can obtain from MSDN. Iíve also created the highly original username/password combination of scott/tiger as a Login. Scott has also been added to the Users folder under the Security folder for the database, and has the appropriate roles to see tables in the database (select db_owner if you donít know what else to pick).

Login New

At this point, the completed fields look as shown below, and weíre ready to click Test Connection.

NOTE: To avoid wasting hours looking up error messages related to output such as...

com.microsoft.sqlserver.jdbc.SQLServerException: The port number [1433] is not valid.

...or

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "[AdventureWorks]"

...be sure to NOT use the square brackets in the boilerplate text provided by BIP (in the Connection String field).

A good, final example is shown below.

final example

I named the data source as MSSQL5 to help identify the source in an expedient manner. The MSSQL part should be obvious, and the 5 relates to 2005 so as to separate it from the 2000, 2005 and 2008 named versions of SQL Server. The port number can be found by examining the output from netstat, logs within MSSQL, and also knowing that 1433 is the standard port number similar to 1521 or 1526 in Oracle. You can also go into the configuration manager and examine the TCP information.

Upon clicking Test Connection, we get the highly informative message shown below.

JDBC > Add Data Source error

What went wrong with the connection setup? Actually, nothing went wrong; itís just that we havenít done everything necessary yet. Going back to the Add Data Source picture, note the tip at the top of the frame: ďPlease make sure to install the required JDBC driver classes.Ē Such a simple tip, and as you may already be guessing, it implies a good bit of work or research to get those classes, and the path (the work needing to be done) is not entirely clear either.

The short and sweet of this is that three jar files (msbase, mssqlserver, and msutil) need to be acquired and placed into the ORACLE_HOME (for BIP) and under the path below:

<start>/oc4j_bi/j2ee/home/applib

Once these files are in place (where to get them is coming up) and the OC4J instance is started, enter the connection information as shown, modified for your particulars, and test the connection. This is a go/no-go situation; you either get the error message just shown, or a success message.

connection established successfully

Donít forget to assign a role (BI_USER will likely be the only role available if you havenít created non-default roles yet) and then click Apply at the top right of the page to finalize the setup. If all goes well, youíll see the new data source in the available list.

add data source

Where to get the MSSQL jar files

A quick and easy way to get the requisite files is to download them from MSDN. The SQL Server 2000 JDBC files work; you donít need the single 2005 version named sqljdbc.jar (at least as far as BIP is concerned, and even though we did the setup using a 2005 database). The tar or exe file as appropriate for your platform is available from Microsoft, as well as the JDBC installation for 2000.  

Within this folder, you will find the three jar files. Copy them to the applib folder as mentioned earlier. A note on My Oracle Support (Doc ID: 445157.1, How to Install JDBC Connection for MS SQL Server in BI Publisher Enterprise) makes reference to a how-to note at OTN (SQL Server Walkthrough)   Within this walkthrough, there are instructions on how to configure OC4J to work with foreign datasources.

This section discusses editing the application.xml file in the <OracleHome>/j2ee/home/config directory. You simply add in three library path tags with the path and file name of each jar file. To be a bit more precise, the example shown at OTN (shown below) has had the closing tags corrected to use ď/>Ē instead of just ď>Ē (just like strict HTML with self-closing tags).

<library path="C:\Program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msbase.jar" />
<library path="C:\Program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msutil.jar" />
<library path="C:\Program files\Microsoft SQL Server 2000 Driver for JDBC\lib\mssqlserver.jar" />

You can go this route if you want (it works just as well, and if you test this, be sure to move or rename the files in the applib folder so you know the jar files are only being referenced via the application.xml file). The edited file also has each tag on one line; the line returns above are for formatting in this article.

NOTE: The link to get the JDBC files in the walkthrough document at OTN is broken or outdated, so use one shown earlier, or do a search at MSDN.

From this point forward, you should be able to create folders and reports in a normal fashion.

In Closing

Once some of the gotchaís were taken care of in setting up a data source, getting BI Publisher to connect to SQL Server was fairly straightforward, in fact, it was no more difficult than creating a new data source within Oracle. Once the jar files were placed and connection details were identified for the MSSQL database, the setup was trivial.

Can you find this information in the installation guide or release notes? Unfortunately, no, and even the notes on My Oracle Support are a bit lacking. The product as a whole is improving by leaps and bounds, but some time needs to be spent on administration and documentation. Oracle recently released a patch (8284524) for version 10.1.3.4. The patch apply or upgrade process will be the focus of a future article. You may find it worthwhile to apply the patch forthwith as it has two major enhancements (numbers to words, and better support for Single Sign-On) and lots of bug fixes.

 

 
 
 
Get the Complete
Oracle Tuning Details 

The landmark book "Oracle Tuning: The Definitive Reference Second Edition" has been updated with over 1,500 pages of expert performance tuning tips. It's packed with scripts and tools to hypercharge Oracle 11g performance and you can buy it for 40% off directly from the publisher.
 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

 

 

Burleson Consulting Remote DB Administration