Connecting to SQL Server with XMLP Enterprise

We have had a few enquiries recently asking how to hook up XMLP Enterprise with a SQL Server db, heres the steps you need to follow to get it working.



  • Download the SQL Server 2000 Driver for JDBC Service Pack 3 - we had some issues with the 2005 jdbc driver we are investigating whether this is an XMLP or MS issue ... you can still access the SQL Server 2005 edition with the 2000 driver sucessfully.
  • Once installed look in the <<INSTALL DIR>>/lib directory there will be 3 jars
    mssqlserver.jar
    msbase.jar
    msutil.jar
    Copy them into your $OC4J_HOME/j2ee/home/applib if you have installed XMLP under OC4J otherwise put them in the common apps libraries directory of your J2EE container.

  • Now bounce your server, the jdbc libraries will be loaded automatically for all applications on the instance. If you only want them loaded for the xmlpserver application then copy the jars to OC4J_HOME/j2ee/home/applications/xmlpserver/xmlpserver/WEB-INF/lib and bounce.
  • Now login as Administrator in the Enterprise server and go to the Admin -> JDBC page and enter the details required for the data connection:


    • Data Source Name - give the connection a name 
    • URL the url to connect to the db, standard MSSQL stuff, basically   jdbc:sqlserver://server:port;databaseName=dbname e.g. jdbc:sqlserver://111.11.1.111:1133;databaseName=AdventureWorks
    • Username  - simple stuff 
    • Password - even simpler  
    • Database Driver Class - this needs to be com.microsoft.jdbc.sqlserver.SQLServerDriver
    • Now hit the test button to check the connection works. If it fails check your connection string is valid and the class definition above.
    • The data source will now be available in the report builder to builder queries against.

You're now ready to start writing reports against SQL Server!

Comments:

Hi Tim, Thanks for the detailed steps on configuring Sql server for Publisher. However, when trying to follow the step below I cannot see any of the jar files mentioned in the installation I chose. (installation version SQLjdbc 1.2) Instead I see only 1 jar file called sqljdbc.jar. i tried copying that file to the applib directory and restarted oc4j. I am still getting connection failed error. ************* Once installed look in the <>/lib directory there will be 3 jars mssqlserver.jar msbase.jar msutil.jar ************** I am on Windows XP pro sp 2 MS sql server 2005 and and OBIEE 10.1.3.4 I have integrated OBIEE and Publisher. I am trying to use Sql Server as another data source. I am not sure what I am doing wrong. This is where I am downloading the jdbc driver from http://www.microsoft.com/downloads/details.aspx?FamilyId=C47053EB-3B64-4794-950D-81E1EC91C1BA&displaylang=en Any help is greatly appreciated. thanks Sethu

Posted by Sethu Kambham on September 16, 2009 at 03:37 PM MDT #

Usually I do not submit on articles, but I would like to say that this article really forced me to do so! Thanks, very nice article. http://www.entrainement-poker.com

Posted by Pierrus Piruoliat on December 09, 2009 at 03:43 AM MST #

Excellent post. Just found it on Yahoo. Thank u for the useful information. Keep up the nice work :)

Posted by Shiela Simo on June 19, 2010 at 12:41 PM MDT #

Hi, Can anybody tell me if we can call SQL Server packages that is written to update a table into after trigger in a data template.

Posted by sri on August 06, 2010 at 02:13 AM MDT #

What a writeup!! Very informative also easy to understand. Looking for more such writeups!! Do you have a twitter or a facebook? I recommended it on digg. The only thing that it's missing is a bit of speed, the pictures are appearing slowly. However thank you for this information.

Posted by Lori Serrao on August 30, 2010 at 03:00 PM MDT #

Hey Pam - yes - I got caught out a few times when I was starting out

Posted by Gracie Thiesfeld on October 15, 2010 at 01:58 PM MDT #

the files mention above (mssqlserver.jar,msbase.jar,msutil.jar) are no longer available. what do you use instead?

Posted by guest on October 06, 2011 at 04:43 AM MDT #

I have the same query ...

the files mention above (mssqlserver.jar,msbase.jar,msutil.jar) are no longer available in that downloaded files even after we unzip and execute it . So tell me the way out.

Posted by Sandip Bhowmick on January 11, 2012 at 12:34 AM MST #

I have the same query ...

the files mention above (mssqlserver.jar,msbase.jar,msutil.jar) are no longer available in that downloaded files even after we unzip and execute it . So tell me the way out.

Posted by guest on January 11, 2012 at 12:36 AM MST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today