Oracle BI Publisher Blog

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!