Tuesday, November 24, 2009

Connect to 64-bit Oracle 10g from Reporting Services 2008

Recently, I had to create couple of Reporting Services 2008 reports by using Oracle 10g as the data source. I had to use one machine as the developer machine and the server. Server was installed with 64-bit Windows 2008 and SQL Server 2008 64-bit. Oracle was installed in another machine and it was 64-bit too. Here are some of the problems, issues I faced, It may help you too. Connectivity problem with BIDS First problem I faced was connectivity with Oracle in BIDS. I tried to use Oracle provider that comes with SQL Server installation but it did not work. So the solution was, install Oracle full client (Administrator). What should be installed? 32-bit or 64-bit? In order to make the connection via BIDS, we need to install Oracle 32-bit client though the server is 64-bit. The main reason for this is, BIDS is a 32-bit application. Once 32-bit Oracle client installed, BIDS was able to connect to Oracle. Connectivity problem with Report Manager This was the second problem. Once the reports are publish, I got the same error when reports are viewed with Report Manager. This is because it requires Oracle 64-bit client. Once Oracle 64-bit client installed, it started working. OLEDB Provider for Oracle or Microsoft Oracle Provider? When I google, I saw some posts related this provider, it seems that Microsoft Oracle provider had not worked for many but it worked for me. I was able to make connection by using both providers. Passing Parameters to Oracle As usual, we can use "?" for represent parameters in the query if the provider is OLE DB (eg. WHERE column1 = ?). But remember, you need to use ":" for parameters if the provider is Microsoft Oracle provider (eg. WHERE column1 = :Parameter1) IN Clause with WHERE "IN" is not supported by OLE DB provider. If you have a requirment that needs to use "IN" with "WHERE", use Microsoft Oracle provider instead.

No comments: