December 7, 2009 at 12:21 pm
Hey all,
I am new to the Oracle world and somewhat at a loss at what I've been reading thus far. Here is my scenario. I have a SQL2000 server running DTS packages that connect directly to Oracle using “oracle provider for ole db”. I also see two Oracle services installed on this server OracleMTSRecoveryService, OracleOraHome92ClientCache. That second service is not running. I now need to migrate this environment to a SQL2008 environment and I need to figure out what to do about these packages.
So I have a couple questions
1) What are those services I have listed above? Is that where my oracle provider for ole db drivers came from?
2) What is the best way to connect to Oracle these days in DTS/SSIS? My goal is to not have to install additional software on the environment as it's a shared clustered environment.
Thanks in advance
December 7, 2009 at 12:33 pm
I am not an Oracle expert, but have recently migrated our DTS packages from 2000 to 2008 SSIS packages.
Thought sharing some info would be helpful to you. All I had in my new Environment (Windows 2008 with SQL Server 2008) was the Oracle Client installed.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 7, 2009 at 12:44 pm
Bru Medishetty (12/7/2009)
All I had in my new Environment (Windows 2008 with SQL Server 2008) was the Oracle Client installed.
Hello Bru,
I did notice that on my x86 SQL2008 environments that I do indeed have an Oracle driver ... yet on x64, I do not. So I believe that is where I am right now, I need to find a solution for x64 SQL2008.
December 7, 2009 at 12:49 pm
I just checked our Windows Server and it is 32 Bit OS.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 7, 2009 at 1:29 pm
So I just tried setting up a connection from an x86 environment and got an error
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "TEST".
OLE DB provider "MSDAORA" for linked server "TEST" returned message "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.". (Microsoft SQL Server, Error: 7303)
This article states that "You must install the Oracle client software on the computer that is running SQL Server where the linked server is set up." So why do I have the ability to use this oracle connector when I still have to install something? Do I actually need whatever client software they're talking about, or is there a driver I can install?
Thanks again
December 7, 2009 at 1:41 pm
I got the exact error message when we first setup the SQL server 2008 and I migrated the DTS Packages to SSIS.
You need to install Oracle Client component, I happened to see in another thread on SSC, that the user was using a 64 Bit Oracle Client components. I guess you too need 64 Bit version of the client components.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 7, 2009 at 2:49 pm
You can test connectivity by opening a Windows command line and typing 'sqlplus /nolog'. That will start Oracle's version of 'osql', giving you the ability to connect to your Oracle database. To connect, type:
connect <username>@<database name>
replacing your username and database name respectively. It should ask you for a password. If this test fails, you don't have the database connection setup correctly in your tns file.
If 'sqlplus' doesn't fire up, it's either not in your path, or the service isn't running. You can go directly to the 'sqlplus' binary by:
c:\oracle\product\10.2.0\client_1\bin (assuming a 10g install)
If 'sqlplus' loads but you can't connect, post your tnsnames.ora file located in:
c:\oracle\product\10.2.0etwork\admin
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
December 7, 2009 at 2:53 pm
Just re-read and saw that it's an Oracle 9 install. Your ORACLE_HOME might be in
c:\orant
Search for the 'sqlplus' binary and tnsnames.ora file there. Obviously, replace the drive letter with the correct drive.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
December 7, 2009 at 8:12 pm
Dan H. (12/7/2009)
You can test connectivity by opening a Windows command line and typing 'sqlplus /nolog'. That will start Oracle's version of 'osql', giving you the ability to connect to your Oracle database. To connect, type:connect <username>@<database name>
replacing your username and database name respectively. It should ask you for a password. If this test fails, you don't have the database connection setup correctly in your tns file.
If 'sqlplus' doesn't fire up, it's either not in your path, or the service isn't running. You can go directly to the 'sqlplus' binary by:
c:\oracle\product\10.2.0\client_1\bin (assuming a 10g install)
If 'sqlplus' loads but you can't connect, post your tnsnames.ora file located in:
c:\oracle\product\10.2.0etwork\admin
That error is not related to SQL Plus it is related to the authenticated user permissions to the Oracle Home folder check below for how to fix it. I also would not use Oracle 10 client to connect to Oracle 9i because Oracle 10g blocks 9i point of connection in most cases.
Solution Description:
Oracle 9.2 Client software requires that you give the Authenticated User privilege to the Oracle Home by following these steps:
1. Log on to Windows as a user with Administration privileges.
2. Launch Windows Explorer from the Start Menu and navigate to the ORACLE_HOME folder. This is typically the "Ora92" folder under the "Oracle" folder (i.e. D:\Oracle\Ora92).
3. Right-click on the ORACLE_HOME folder and choose the "Properties" option from the drop down list. A "Properties" window should appear.
4. Click on the "Security" tab of the "Properties" window.
5. Click on the "Authenticated Users" item in the "Name" or "Group or user names" list.
6. Uncheck the "Read and Execute" box in the "Permissions" or "Permissions for Authenticated Users" list under the "Allow" column.
7. Re-check the "Read and Execute" box under the "Allow" column (this is the box you just unchecked).
8. Click the "Advanced" button and in the "Permission Entries" list make sure you see the "Authenticated Users" listed there with:
Permission = Read & Execute
Apply To = This folder, subfolders and files
If this is NOT the case, edit that line and make sure the "Apply onto" drop-down box is set to "This folder, subfolders and files". This should already be set properly but it is important that you verify this.
9. I checked the "Replace permission entries on all child objects with entries shown here that apply to child objects" checkbox and clicked the "Apply" button. (This was not specified in the original instructions, but it worked.)
10. Click the "Ok" button until you close out all of the security property windows. The cursor may present the hourglass for a few seconds as it applies the permissions you just changed to all subfolders and files.
11. Reboot your computer to assure that these changes have taken effect.
12. Re-execute the application and it should now work
Kind regards,
Gift Peddie
December 7, 2009 at 9:38 pm
Hi Dan and Gift,
The error reported by the OP is occuring since the Oracle Client component is not installed on the machine from where he is trying to connect to Oracle.
I guess that is the issue in the first place not the version 10.2 or 9.2.
I do not see the OP posting what version of Oracle Client Component is installed.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 7, 2009 at 9:46 pm
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "TEST".
OLE DB provider "MSDAORA" for linked server "TEST" returned message "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.
This error is the permission of the Oracle authenticated user to Oracle home folder more to the point the permissions are Windows NTFS permissions to the home folder because Oracle installer does not add it during installation. And as Dan stated the Oracle version is Oracle 9i RC2.
The Oracle client is required but the permissions are also needed because that is the only way for none admin to use the Oracle installation.
Kind regards,
Gift Peddie
December 8, 2009 at 6:03 am
All, I have not yet installed any Oracle software at all. I guess that was what I was going for first ... do I HAVE to have something installed? Why do I have an Oracle option for a linked server If I still need to install the client? Regardless, in production, I will have to deal with x64 which doesn't appear to be supported out of the box. So at this point I'm trying to determine exactly what I need to make this work. As stated, I really do not want to install anything outside of SQL on my production cluster, but If I have to, I have to. Yet I'd like to know exactly what I need, the minimal requirements ...
Thanks again for all the help!
December 8, 2009 at 6:18 am
Hey Adam,
I already mentioned this in yesterday's reply, you need to see what is Oracle Version you have on the Oracle side and install the client components. (Only Client). After installing, it's better you seek the help from the racle DBA team to set up the path and TNSNames.ORA File.
This is the minimum required components you need to have.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 8, 2009 at 3:04 pm
I have a SQL2000 server running DTS packages that connect directly to Oracle using “oracle provider for ole db”. I also see two Oracle services installed on this server OracleMTSRecoveryService, OracleOraHome92ClientCache.
The above tells me your DTS is connecting to Oracle 9i RC2 and I don't understand why SSIS and Linked Server are related because Replication also move data but it is not related to SSIS. Oracle client is required for all Oracle development.
The client makes all Oracle installations local to you it is almost an instance without the ability to create a database. And as Bru said you need to add the TNSNAMES.ORA file in your Oracle home folder and make sure you add the relevant NTFS permissions.
Kind regards,
Gift Peddie
January 6, 2010 at 7:45 am
Hey all,
I am just now resuming this and I have another question ... Looks like I need the client software from here: http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html, yet I'm not sure which to download. As stated, I'm looking for the absolutely lightest impact that simply provides connectivity to Oracle from SQL.
I'm assuming I should go with the Basic Lite? Going to give it a shot on my desktop for now, but figured I'd post for any additional insight.
Thanks
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply