Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nr
SQLTeam MVY
12543 Posts |
 Posted - 2003-05-06 : 18:31:12
|
| We have to connect to oracle (yuk) from dts (yuk) and are having problems on one server.Both msdaora and MSDASQL fail (we have to use the odbc driver MSDASQL).Below I give the output from openrowsets which I guess have the same problems.The two servers seem the same except that the failing one doesn't have the oracle client installed. Anyone know if this is required for the odbc driver (suspect so) and what needs to be installed.Using openrowsetadoselect * from openrowset('msdaora','servername';'user';'pwd','select * from db.tbl where 1 = 0')givesOLE DB provider 'msdaora' reported an error. [OLE/DB provider 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.]OLE DB error trace [OLE/DB Provider 'msdaora' IDBInitialize::Initialize returned 0x80004005: ].odbcselect * from openrowset('MSDASQL','DRIVER={Microsoft ODBC for Oracle};SERVER=servername;UID=user;PWD=pwd','select * from db.tbl where 1 = 0')givesOLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed]OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].ThanksNigel==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-05-06 : 18:48:04
|
| Found thisIn order to connect to Oracle via odbc, you need a client "driver" installed on the local machine. This doesn't necessarily mean that you need the entire client tools collection installed, but you will need some sort of ODBC driver to interface with Oracle. SQL 2k has an Oracle connection dts object that you can use to get around this. Keep in mind that if you are installing the ODBC driver, it needs to be on the server from which the package executes, not the local machine. and thisThe problem for Oracle (even ODBC) is that the client installation requires many things, even the most basic client installation will put 100+ files on the system.This is the way Oracle has designed things, and most of the files implement the settings for the connection, as well as some abstraction and translation layer: date, currency and numerical values can be different on the client than on the server, and also the error messages can be produced in the local language instead of the language of the server.About to get the guys to install the oracle client.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-05-07 : 03:46:29
|
quote:
In order to connect to Oracle via odbc, you need a client "driver" installed on the local machine
Yup, its called the SQL*Net or something like that.OS |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-05-07 : 05:13:28
|
| Yep. And it's a major pain in the backside... I had to edit some registry keys manually to get mine up and running. Something about a file called TNSnames.ora. This was oracle 7.3 though. |
|
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2003-05-07 : 11:57:16
|
| Yes you need the have to install the Oracle client software, but it's not that bad. If you take sometime and looking into the installer options, you can just install the drivers you are looking for. I usually install SQLPlus as well, just so I have a native tool to connect to the Oracle server with and test my connection settings. You will need to configure the tnsnames.ora file, but the newer clients have a wizzard that walks you through this. Once you get it setup correctly on one server, you can just copy that file to the others.Jeff Banschbach, MCDBA |
|
|
|
|
|
|
|
|
Oracle odbc driver