Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

OracleClient requires Oracle client software version 8.1.7 or greater

Posted on 2011-10-17
9
Medium Priority
?
3,508 Views
Last Modified: 2012-05-12
I developed a loading script in 32 bit MS visual studio and the script running fine in Visual Studio. The script is about loading oracle data into MS SQL database.

I have 64 bit Windows 2008 R2 server with 64 bit MSSQL 2008 server

I created a MS SQL job to attach the loading script but the script is failed to run with the following error in job history

I don't understand why the script running fine in Visual Studio but when it attached to the MSSQL job and it ran failed.


Message
Executed as user: domain\login name. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.4000.0 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  12:01:00 AM  Error: 2011-10-17 00:01:09.99     Code: 0xC0047062     Source: Load UnsubscribedEmails ADO NET Source [1]     Description: System.Exception: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.     at System.Data.OracleClient.OCI.DetermineClientVersion()     at System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integratedSecurity, Boolean unicode, Boolean omitOracleConnectionName)     at System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions)     at System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)     at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)     at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)     at System.Data.OracleClient.OracleConnection.Open()     at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)     at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)     at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)     at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)  End Error  Error: 2011-10-17 00:01:09.99     Code: 0xC0047017     Source: Load UnsubscribedEmails SSIS.Pipeline     Description: component "ADO NET Source" (1) failed validation and returned error code 0x80131500.  End Error  Error: 2011-10-17 00:01:09.99     Code: 0xC004700C     Source: Load UnsubscribedEmails SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2011-10-17 00:01:09.99     Code: 0xC0024107     Source: Load UnsubscribedEmails      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  12:01:00 AM  Finished: 12:01:10 AM  Elapsed:  9.39 seconds.  The package execution failed.  The step failed.


I spent a lot of time trying to fix this issue. But all failed. Please help. it's kind of urgent
0
Comment
Question by:wasabi3689
  • 4
  • 2
  • 2
  • +1
9 Comments
 

Accepted Solution

by:
KlausBarthels earned 300 total points
ID: 36985200
Do you use a ODBC-Connection to the ORA-DB?
If you do so, then you need also the ORA-Client.
Without ORA-Client you have no information about the connection (TNSNAMES.ORA and so on) and there are no driver to connect to database.

Regards

Klaus
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 600 total points
ID: 36985702
If you use ODBC to connect to an Oracle database, you need either the Microsoft ODBC driver for Oracle, or an Oracle client that includes an ODBC driver.

Please tell us how you connect to Oracle.  Also, do you know which Oracle database version you are trying to connect to?

(I may be an Oracle expert, but I'm not an expert on Visual Studio, or SQL Server, so I can't explain why a connection to Oracle worked from one of those but not from the other.)
0
 

Author Comment

by:wasabi3689
ID: 36986534
I use oracle client to connect to oracle database. oracle database is 8i. Microsoft ODBC driver for Oracle in server is not support from Microsoft, and cannot do this approach.
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 600 total points
ID: 36986568
OK, that should work. First, you need to configure the Oracle client to connect to the Oracle database.  Did you do that?  Then, do you have an ODBC data source set up in your WIndows Control Panel that uses the Oracle ODBC driver and the database connection you set up in the Oracle client?  Did you test that in Control Panel and does that ODBC data source work there?
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 600 total points
ID: 36987546
I know is a different direction and you worked hard already but its much easier (and faster with better control) in my opinion to use OLEDB driver and a ORACLE linked server in SQL then just use full name qualifiers like:
ORA_Server_name.ORA_db_name.ORA_schema_name.ORA_table_name to do your transfers in a plain T-SQL job step instead of VS code...

http://support.microsoft.com/kb/280106
0
 

Author Comment

by:wasabi3689
ID: 36987581
I ever use OLEDB connection, but it doesn't work with the same error message. There is no microsoft ODBC for oracle in 64 bit Windows 2008 server.
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 600 total points
ID: 36987814
I know as a fact that on Windows 64bit Windows you can use 32 bit ORACLE driver to connect to ORACLE databases but not viceversa.

Please see solution at link below and hope it will help you as well.
http://social.technet.microsoft.com/Forums/en-US/windowsserver2008r2general/thread/dae6f490-30c8-4799-a048-37f103f0088d/

0
 

Author Comment

by:wasabi3689
ID: 36988326
have anyone tried this approach?

http://www.devart.com/dotconnect/oracle/download.html

what is your feedback?
0
 

Author Closing Comment

by:wasabi3689
ID: 37065816
Answers provided useful info for resolving the problem.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question