September 9, 2009 at 2:23 pm
Hi,
I have a SQL server 2005 (64-bit) installed on a windows 64-bit machine.
I am trying to create reports using Oracle as the datasource.
For this, I have installed Oracle 64-bit client on the machine, configured all the ODBC connections required.
When I opened Visual Studio to create a new report, in the report wizard
I am selecting the Oracle datasource and gave the oracle server name and login details.
when I tired testing the connection, the connection is giving me an error message like :
Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit oracle client components installed.
I tried to search for some solutions but no luck.
Can anyone help me to resolve this.
This is the first time I am using Oracle database as datasource in creating reports and I am not sure whether I am right or doing some mistake in creating the datasource.
Thanks,
Sree
September 9, 2009 at 8:53 pm
On 64-bit servers, you'll need to install the 32-bit and 64-bit Oracle client. The reason is that the Visual Studio components still run in the 32-bit runtime.
Also, create a D:\TNS\ folder and place your tnsnames.ora file there. Create an environment variable called "TNS_ADMIN" with the value "D:\TNS". Doing this will ensure both the 32-bit and 64-bit oracle clients read from the same tnsnames.ora file, and you'll only have to update one place when you need to add entries.
We had a case open with MS and eventually determined you needed both clients.
When a scheduled job runs (ie from SSIS or a linked server), the 64-bit client is required as the SQL database engine looks for the 64-bit client, but the client tools (BIDS) look for 32.
Hope this gets you up and running.
Steve
September 10, 2009 at 12:12 pm
Thank You very much for the explanation Steve !!!
I have installed both the 64 and 32 bit oracle clients.
I have created a directory D:\TNS and put the tnsnames.org file there.
After configuring everything , I tried to create a sample report with Oracle as datasource, now it gives me this error :
ORA-12154:TNS:could not resolve the connect identifier specified
When I did some research on msdn site, i got the following forum which says the best solution is to use oracle 11g client, which right now we cannot do.
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/7cedb262-380d-4919-854a-00ea9a3c372c
Steve, can you give me anymore suggestions on how to fix this issue.
Thanks Once Again
Sree
September 10, 2009 at 12:21 pm
Some suggestions are here:
http://ora-12154.ora-code.com/
It is more of a connectivity issue based on an incorrect TNS entry or some other reason. try to connect to your Oracle instance via sql plus from the system and see if it connects.
If it gives you the same error, then you know it doesn't have to do with BIDS.
Also be sure to restart after you added the environment variable and completed the installs. I've seen some weird things happen by not rebooting after getting them installed.
You're in a better direction than yesterday though...
Best of luck,
Steve
September 10, 2009 at 1:18 pm
Hi Sree,
Check that the TNS Ora file is correct i.e the Server names in the file.
Also use 32-bit BIDS to connect to the Oracle data source.
Start 32-BIDS by creating a Batch file using the following command--
start /B "C:\Progra~2\Microsoft Visual Studio 8\Common7\IDE" "C:\Progra~2\Microsoft Visual Studio 8\Common7\IDE\devenv.exe"
Thank You,
Best Regards,
SQLBuddy.
September 10, 2009 at 1:21 pm
I am able to connect to the oracle instance through sqlplus also from command prompt without any error messages using both the oracle clients.
I dont think its an issue with the TNS, but still I didnt reboot the machine which I am thinking as the last option I can try from my side.
One quick question, even after rebooting the server if its not fixed, is there anyway I can fix it or do i need to look for a 32-bit server ?
Thanks for your valuable suggestions
Sree
September 10, 2009 at 6:31 pm
It should work on a 64-bit server since you can connect through SQL PLus.
Here is some additional information I have saved off various pages online (unfortunately don't have the URL reference) - See the attachment.
Regards,
Steve
September 11, 2009 at 7:44 am
Hi Steve,
Thank You for providing me the document.
We have a senior Oracle DBA in our company who has seen this and is helping me to solve this.
He did the uninstalling the same way as in ur document and everything he has done what he can do from his side.
Few more details about our 64-server :
We have actually installed first the 64-bit oracle client( Runtime install )
Then, in order to install the windows connectivity, we installed a custom oracle 64-bit client where we have installed the ODBC and other drivers.
Next, we have installed the 32-bit oracle client ( Runtime only )
We didnt install the ODBC or any other connectivity for 32-bit, just the runtime installation of 32-oracle client.
Once we finished installation we have moved the tnsnames.org and another file to the D:\TNS folder and configured an environmental variable for it.
What I have seen is that both the oracle clients has been installed on the D-drive of our machine, but in the C-drive I have seen one oracle folder in the programfiles and another in the programfiles (x86).
The 64-bit is on programfiles folder and
the 32-bit is on programfiles(x86) folder
I was wondering is this is one which is causing all the problems ?
Also, another question :
when we are trying to create an oracle datasource in BIDS, how we know whether it has selected the 32-bit oracle client ?
Because the machine has both 64 and 32 -bit clients installed, so which one will it select ?
Many Thanks for replying for all my questions
Sree
September 11, 2009 at 8:54 am
The call we had open with MS said that the BIDS runs with the 32-bit oracle client, and when scheduled ssis packages run, it runs through the DB engine which is 64-bit, in which case it looks for the 64-bit client. the x86 folder would be where the bids runtimes would be hosted.
If you have the option, i would rebuild the server and try to start fresh.
Uninstalling Oracle never seems to go clean for me.
September 11, 2009 at 10:37 am
You can schedule the SSIS packages that connect to oracle datasource on a 64-bit machine by creating a job step that calls the 32-bit DTExec.exe. The job step should be of the type Operation system and the connection should be made using package configuration
Thank You,
Best Regards,
SQLBuddy
September 11, 2009 at 11:50 am
Thanks for the reply.
The issue is not with scheduling SSIS packages on 64or 32 bit environment.Its all about how to use Oracle as a datasource while creating reports in BIDS where the server is 64-bit.
You can find more details from the forum where I have posted the error messages too.
Regards,
Sree
September 11, 2009 at 1:12 pm
The same thing applies for SSRS too. Because this is all about 32-bit BIDS and 32-bit Oracle on a 64-bit machine. The solution has already been provided. A little bit of waryness would have solved it.
Thank You,
Best Regards
SQLBuddy
September 11, 2009 at 1:36 pm
This is where I am still struck, I am still getting the same error even after twicking here and there :
I have installed both the 64 and 32 bit oracle clients.
I have created a directory D:\TNS and put the tnsnames.org file there.
After configuring everything , I tried to create a sample report with Oracle as datasource, now it gives me this error :
ORA-12154:TNS:could not resolve the connect identifier specified
When I did some research on msdn site, i got the following forum which says the best solution is to use oracle 11g client, which right now we cannot do.
Thanks Once Again
Sree
September 11, 2009 at 2:10 pm
We had a similar problem on a 64-bit machine. That was due to incorrect TNSNAMES.ora file. Our Oracle DBA recreated that file with the Oracle Datasource connection information. Once he created that new .ora file everything worked fine. So ask you Oracle DBA to check that .ora file and create a new one if possible. That is usually a very simple process.
Also you should use 32-bit BIDS to create your your reports which uses 32-bit Oracle data access components.
Also use OLEDB for Oracle driver to connect to the datasource.
Thank You,
Best Regards,
SQLBuddy.
September 12, 2009 at 10:34 am
Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit oracle client components installed.
That is platform related error VS2005 is x86 that is 32bits so you have downloaded and installed the x86 Oracle client which is what you need.
This is where I am still struck, I am still getting the same error even after twicking here and there :
I have installed both the 64 and 32 bit oracle clients.
I have created a directory D:\TNS and put the tnsnames.org file there.
After configuring everything , I tried to create a sample report with Oracle as datasource, now it gives me this error :
ORA-12154:TNS:could not resolved the connect identifier specified
The above error means the Oracle authenticated user does not have permissions to the Oracle home directory, make sure the TNSNAMES.ORA file contains the permission that will resolve all your SSRS users. Some times delete and add the file again removes the error. Check the thread below I helped another user.
Kind regards,
Gift Peddie
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply