December 15, 2006 at 8:45 am
Hello,
I am trying to use SQL Server 2005 Reporting Services to access an Oracle 8i database. I can access the DB from SQLPLUS and also as a linked server from SQL Server. I define the datasource type as Oracle and enter the SID in the servername field. I enter the userid and password for Oracle. When I click on 'Test Connection' I get the ORA-12154 error. I have tried this on two different servers and get the same results on both. I have checked the MSN articles and everything looks like it is set-up correctly. Any ideas on what else I can check?
Thanks,
Bill
December 15, 2006 at 1:48 pm
I have also gotten that Ora error. a few things to look into
1) make sure to install the 8i odbc driver
2) if you are on a 64 bit machine the 8i driver will not work. I tried everything. it has to be 32 bit
3) With RS 2005 I had to replicate the information from oracle using a SQL 2K DTS so that I could get to the information for reporting. I was not able to link directly to the tables.
Oracle says that you can use the 10g driver but I could not seem to get it to work
October 30, 2007 at 11:06 am
I agree, same prob, SSIS and Import Wizard connects fine on 32-bit server but not the 64-bit. This is a common problem I believe, and doesn't seem to be supported very well from Microsoft. Tried the Oracle V10 drivers (have to change the registry keys also) and although it greatly speeds up queries using the Linked Server (on both 32/64 bit) it doesn't fix the issue on the 64-bit SSIS/Import connection.
Aaaarrggghh! Has ANYONE made this work on a 64-bit? Why are they selling Win and SQL 64-bit and not supporting this basic setup?
October 31, 2007 at 7:29 am
Sometimes in Oracle the service name can be different from the SID. Find out the service name and try putting that in instead of the SID. For example, one of my Oracle databases has a SID = camp and a Servicename = campus. I have never run into the problem you are having but am just trying to offer other suggestions. If it works let me know.
October 31, 2007 at 3:29 pm
I don't have access to any 64-bit systems, but I recommend the following:
The Oracle Networking errors can be a real pain to troubleshoot.
1) Compare the SID_NAME value in the LISTENER.ORA file on the Oracle Server against the SERVICE_NAME value in the TNSNAMES.ORA file on the Client Machine.
2) Assuming the LISTENER.ORA and TNSNAMES.ORA entries match, use TNSPING.EXE (An Oracle Utility) to check client/server connectivity that includes reading the TNSNAMES.ORA file on the Client Machine.
DOS PROMPT:
TNSPING.EXE
EXAMPLE:
C:\>TNSPING PROD
TNS Ping Utility for 32-bit Windows: Version 9.2.0.7.0 - Production on 31-OCT-20
07 17:26:16
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
C:\oracle\ora92etwork\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = HHDTRAQ)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PROD)))
(OK) 40 ms
If TNSPING comes back with a TNS-XXXXX or a ORA-XXXXX error you should be able to troubleshoot further from there.
Hope this helps.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
November 1, 2007 at 8:40 am
Bill,
I took a second look at your original posting, and noticed that you are already connecting with SQL Plus. Shame on me for posting hastily!
There can be a huge "grey area" when working with Oracle and Microsoft products where support staff from each side will "finger point" as to why things are not working.
ORA-12154 errors are quite vague. Are you getting any other ORA- or TNS- errors after the ORA-12154 errors on the machine with the Oracle client installed?
You can try turning on the Client tracing to capture more detail about the ORA-12154 or other errors being thrown during the connection process.
It seems that you have exhausted your Microsoft resources. Have you done any research on the Oracle side to include Oracle Technology Network (OTN), or Metalink Forums?
You may also get some "not supported" flak from Oracle Support as Oracle 8i is no longer supported, if you open a TAR with Oracle Support.
Another thing to try is setting up a connection using "Microsoft ODBC for Oracle" driver, as opposed to the "Oracle ODBC Driver for Oracle". I am assuming that you have only tried connections with the Oracle provided drivers.
If you can get more details, I might be able to make other recommendations.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
November 1, 2007 at 11:55 am
Go with the 9i client rather than the 8i one, it's far more stable and reliable.
There were registry changes needed to ever get the 8i client to work properly, check out http://support.microsoft.com/kb/280106
November 1, 2007 at 11:42 pm
Bill -
You don't say whether you're on a 64 bit or 32 bit version of SQL Server... assuming you're on a 64 bit machine you may well need to install both the 32 & 64 bit versions of the Oracle driver. Don't ask me why but on an 64 bit machine certain parts of SQL Server run 64 bit (e.g. the core database engine) and other parts run 32 bit (e.g. SSIS and management studio). For example, linked servers on a 64 bit machine use the 64 bit oracle driver but SSIS use the 32 bit driver...
One other thought would be to create an ODBC DSN, again be aware of the difference, there are actually two versions of the ODBC admin on a 64 bit machine, one for 64 bit drivers, the other for 32 bit drivers...
November 2, 2007 at 12:20 pm
If I can add a few clarifications:
Most likely, you'll want to run 32 bit Report Services, even on x64 hardware. The reason is, that Report Services runs in IIS and IIS run in either 32 or 64 bit mode. (See the Microsoft references about switching back and forth.) Since you'll probably want to run other web programs, possibly 3rd party software, you'll want to wait until everything is guarenteed to be 64 bit. (Unless this is a single purpose machine.)
The good news, is that RS runs great in 32 bit and you probably wouldn't notice the difference.
For the Oracle drivers, expect some challenges getting them to work on a x64 platform. We went through quite a few cycles of down load this version, update that, etc, before Oracle properly patched their drivers. If possible, go to the latest, up to date drivers. They are working for us now. Previous version also had some huge performance problems, which we had a real trouble getting Oracle to admit to (event though the results were well documented). We're using 10g.
You'll need the 32 bit drivers for any 32 bit programs (i.e. RS) and 64 bit drivers for any 64 bit programs (i.e. custom application). They install fine side by side.
Commercial message: if you need high performance drivers, check out Data Direct. Their drivers are great and their support line is top notch.
Remember that most (all?) of the Microsoft Visual tools (like Visual Studio) are still 32 bit. So you need to have 32 bit in place to run the tools, but 64 bit in place if you're running directly from the services. BDP is to only run visual tools from development PCs, and just leave the server set up with 64 bit (no tools). (That said, of course I install the visual tools on the server. :P.)
BTW Phil, this is really about the Oracle driver archtecture, not Microsoft support. But I feel your pain. Took some investigation to figure it out.
Hope this helps.
November 2, 2007 at 12:28 pm
Part of the problem is that the 10g client will not connect to an 8i database, you have to use the 9i or 8i client.
December 9, 2008 at 7:45 pm
For viewers who are checking this post for ORA-12154 error,
Incase you are
1. able to connect to Oracle from SQLPLUS
2. tnsping also works
3. Linked Server also works
but but...
4. Connecting from Visual Studio for SSIS, SSRS (Reporting Service) is not working then you may working on a 64-bit OS so VS will be installed in C:\Program Files "(x64)" which is causing the problem in connecting to Oracle from Visual Studio... Take a look at my post at http://www.sqlserverdba.co.cc/2008/12/error-in-initializing-provider-ora.html which will guide you to find some solution....
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
December 10, 2008 at 1:12 pm
Problem: I was trying to use Reporting Services from my Report Server to connect to an Oracle instance.
I can:
- tnsping the Oracle instance
- Connect to Oracle instance via TOAD
- Connect to the Oracle instance from Visual Studio data source
- Connect to the Oracle instance from Visual Studio report preview
However, once I deployed the report to my report server, when I tried to access the report. I will get the ORA-12154 error
Solution:
After looking at multiple places online, and performing numerous testings, the following 3 components are essential to solve this problem. The ORA-12154 error will occur if any one of the following steps are missing
Step 1: Reapply Read Write and Execute permission to the Oracle_Home folder. e.g. Go to C:\oracle\product\10.2.0\client_1
Right Click on the client_1 folder
Select Properties
Click Security
Click Authenticated Users
Check Read, Write, Read & Execute
Click Apply
Wait for a minute for completion
Click OK
Step 2: In C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN, create/edit the sqlnet.ora file with the following:
NAMES.DEFAULT_DOMAIN = domain.windows.site.com
## for example wii.windows.microsoft.com
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
Step 3: In C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN, edit the tnsnames.ora, so that the instance name includes the above mentioned domain.windows.site.com
e.g.
instead of
OracleInstance =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbtest.microsoft.com)(PORT = 1234))
(CONNECT_DATA = (SERVICE_NAME = OracleInstance.microsoft.com))
)
The entry should be:
OracleInstance.WII.WINDOWS.MICROSOFT.cOM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbtest.microsoft.com)(PORT = 1234))
(CONNECT_DATA = (SERVICE_NAME = OracleInstand.Microsoft.com))
)
Also, verify from regedit that ORACLE_HOME is pointing to the correct place, e.g. C:\oracle\product\10.2.0\client_1
Hope This Helps
lbjohn99 at yahoo dot com
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply