Irfan's World

February 17, 2013

JDBC connectivity in SAP BI 4.0

Filed under: SAP BI 4.0 Administration — Tags: , , — Irfan @ 11:32 pm

If you want to connect IDT(Information Design Tool)  to reporting data sources using JDBC you need a .jar file for that relational database.  Here in this example, I am going to show you how to connect to ORACLE database 11g and SQL Server 2008 databases.  First make sure you have the middle ware /client software installed on the machine where you are using IDT. Also every database vendor will provide their own JDBC connectivity driver software to connect to their databases or you can write your own class definition and use it.

The most common errors that you will see are the following ones when you try to connect to a database using JDBC drivers using IDT:

 Java Class not found in classpath and No suitable driver found (Generic)
 java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver (for Oracle only)
oracle.JDBC.OracleDriver (for Oracle only)
com.microsoft.sqlserver.jdbc.SQLServerDriver (for SQLServer only)

Oracle database

1) Lets create a new relational connection in IDT to connect to Oracle using JDBC drivers. If you don’t find ojdbc14.jar(required for oracle 11g) then you can download directly from www.oracle.com for the version you need.
image
2) Give it a name and click Next, Select the database middleware driver, Oracle 11.
image
3) In the above screen select JDBC Drivers and click Next to go to next screen
image
4) In the above screen specify the user name, password, oracle server host name:port # and oracle service.  Click Test Connection, it may display the error discussed above.  To resolve this  error copy the ojdbc14.jar to the following
directory : <install folder>\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\oracle\ojdbc14.jar on the BI Server or a client machine.   If you don’t see oracle directory under drivers directory
in the said path then create it and save the .jar file under it.
5) Restart IDT and try creating the JDBC connection again, It should be successful and hence we resolved the error.

image



SQL Server 2008

Similarly trying to connect to SQL Server 2008  using IDT throws an error shown below.

image

To resolve the error, download the latest jdbc driver for SQL Server 2008 from Microsoft website (http://www.microsoft.com/en-us/download/details.aspx?id=11774) , As of this writing the file available from microsoft is “sqljdbc_4.0.2206.100_enu.exe”, extract this file to a temp folder on your machine and copy sqljdbc4.jar to the following folder: <install folder>\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\sqlsrv2008.

After saving the sqljdbc4.jar in the right folder, try to create a new JDBC connection to connect to SQL Server 2008 using IDT the connection was successful.

image

One last thing I would like to bring is that most of jdbc connectivity errors will be resolved if the classes or jar files are found in the path environment variable.  That is all for today.

Advertisements

January 24, 2011

ORA – 12514 TNS: listener does not currently know of service requested in connect descriptor

Filed under: Oracle 9i/10g/11g — Tags: , — Irfan @ 10:38 pm

When you connect using SQL Developer or any other third party tools that uses JDBC thin client would return this error, but remember it will not give you this error when you connect using SQL*PLUS.

    Error ORA - 12514 : TNS listener does not currently know of service requested in connect descriptor

As the error message suggests, the database server did not register itself with the database listener.  To troubleshoot this connectivity issue, we will start with looking at TNSNAMES.ORA and LISTENER.ORA files on the client machine from which you want to connect. In other words, I would say there is something like SERVICE NAME or HOST name is incorrect in the listerner.ora or tnsnames.ora file on the client machine.

Note : If you receive this error first contact your DBA to resolve it first.

The files that we are discussing here are Oracle systems files, take the backup of these files before you change any settings.  These files contain information regarding the HOST name or IP address of Oracle database server(s) in your network.  When a client machine tries to connect to the server using oracle client middle ware then you have to make sure that the client machine has correct information of the database server (HOST name or IP Address).  I repeat HOST or IP Address.

Follow these two steps to resolve this issue:

First of all, make sure that both of these files are in sync, you must use only one of the below options, it means either

   1.     Use HOST name in both the files (TNSNAMES.ORA and LISTENER.ORA)

       or

          Use IP Address of the Database Server in both the files (TNSNAMES.ORA and LISTENER.ORA)

   2. Register the database with the listener.



Step 1:

The following screen shots shows that I am using the HOST name as ‘irfan-PC’. Here is a sample of tnsnames.ora file from my client machine.

Here is a sample of listener.ora file from my client machine in that you will see the HOST name of the database server. 

Step 2:

Now connect to SQL*PLUS as DBA and issue the following command:

ALTER SYSTEM REGISTER

This command will register the database with the listener.

Now connect SQL Developer to oracle database :

Following screen shot shows some mandatory details when you connect using SQL Developer that will ensure to connect to an oracle database using SQL Developer.

Click ‘Test’ to check the connection and it should connect without issues. Hope you saved some time by resolving this error :).

Let me know if there are any other alternatives to resolve this error.

SQL Server Dates – Part 2 (Quarter)

In this post, I will be showing you how to generate Quarterly dates using getdate() database function.

Quarter Dates

The following code will generate First Day of the Previous Quarter, Last Day of the Previous Quarter, First Day of the Current Quarter, Last Day of the Current Quarter.

select ‘First Day of the Previous Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate())-1,0)
select ‘Last Day of the Previous Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate()),0)-1
select ‘First Day of the Current Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate()),0)
select ‘Last Day of the Current Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate())+1,0)-1
select ‘First Day of the Next Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate())+1,0)

Wait for my next post for more Quarterly dates. Click for Date Part 3

SQL Server Dates – Part 1 (Year)

To display today’s date in SQL Server 2008 select GETDATE().

Following are some of the dates that can be used in SQL Queries to fulfill the end user needs while retrieving data from the database or creating custom objects for delivering reports in BI tools.

select ‘First Day of the Previous Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate())-1,0)
select ‘Last Day of the Previous Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate()),0)-1
select ‘First Day of the Current Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate()),0)
select ‘Last Day of the Current Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate())+1,0)-1
select ‘First Day of the Next Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate())+1,0)

I will be continuing on how to generate Quarterly dates in my next post Quarter Dates

Create a free website or blog at WordPress.com.