July 19, 2004 at 11:12 am
Is it possible to retrieve data from an Oracle database (using DTS) without installing an Oracle client at the SQL-Server database-server ?
It works fine whit the Oracle client installed; but we would like to avoid the installation of Oracle-stuff on our SQL-Server database-server.
July 20, 2004 at 2:56 am
Have you tried the Microsoft ODBC Driver for Oracle? I have to confess I don't know whether this requires SQL*Net installed (my suspicion is it will) but give it a go anyway and please post your findings back on here - I'd be interested to see what you come up with.
Regards
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
July 20, 2004 at 6:15 am
should work without having a client install of oracle afaik - there will be some limitations in the ODBC driver - could it be one of those tripping you up?
from ver 7, so may have changed:
The Microsoft ODBC and OLEDB drivers for Oracle support the Oracle 7.3 BLOB data types, not Oracle 8.0 data types (for example, BLOB, CLOB, NCLOB, and BFILE are not supported).
The Microsoft ODBC driver for Oracle does not support sending Unicode strings into an Oracle server. Oracle requires prefixing Unicode strings with the letter N.
The Microsoft ODBC driver for Oracle does not support negative scaling for the Oracle number data type.
The Microsoft ODBC driver for Oracle reports that an Oracle number data type without a specified precision has a size of 20 digits. When importing from Oracle (regardless of the destination), if there are more than 20 digits,
you may have to manually increase the precision if the destination table does not already exist.
Oracle supports only one LONG (BLOB) data column in a table.
You cannot import or export Oracle columns that have mixed or lower case names. You also cannot transform or copy data using Oracle column names that contain spaces using the DTS Import and DTS Export wizards. Oracle requires
case-sensitive column names to be precisely specified and quoted.
July 20, 2004 at 6:16 am
oh yeah, also try using the oledb driver instead of odbc!
July 20, 2004 at 6:18 am
Gerrit,
You need to have the Oracle client & networking components loaded on the server in order to get DTS to connect your Oracle database. Although there is a Microsoft OLE & ODBE Driver for Oracle listed among the data sources in DTS, they won't work without the Oracle components. Sorry dude, you gonna have to suck it up and load the Oracle client.
Ted Scott
July 20, 2004 at 6:39 am
Thanks anyway
July 20, 2004 at 8:04 am
Ted has it right, you need an Oracle client. However...
You might get by with the Oracle jdbc client which is very "thin" and do not require the full SQL*Net suite. From within the DTS, you could run a little Java class that would connect, get the data into a CSV file, and then import that file.
I'll admit that it is a hack, very non-Microsoft, and does load some Oracle drivers but it gets you access to Oracle with a minimum amount of Oracle stuff. It's not too bad if Java is ok in your shop.
Our system admins would not allow Oracle client software on our SQL Server machine, so I had to write SQL*Plus scripts with Windows batch files to get data into CSV files on a network share and then import those from a DTS. It would have been so much easier to use a linked server but... I have thought about the Java solution but Java is frowned upon here too.
It's too bad that the Oracle client isn't as simple as or even a part of MDAC. That would be a great situation.
July 20, 2004 at 9:40 am
One item to note: if you can get by with running the package with Oracle connections interactively from Enterprise Manager (vs. a scheduled job via SQL Server Agent), DTS will use the client PC's Oracle client install to support the OLE DB or ODBC connections.
Also, fwiw, we have had an Oracle 8.1.7 client install on a production SQL Server box for several years with no apparent ill effects. This allows us to connect to Oracle in DTS as well as via a linked server.
Hope this helps.
July 20, 2004 at 2:37 pm
I have a DTS package set up to connect to an Oracle database and import various fields into SQL Server tables. Many of these fields require a year to be specified in the SQL statement. How would I be able to use a global variable so I could change the year in only one place rather than changing the year in each SQL statement passed to Oracle.
I tried setting up a global variable and revising the SQL statement to use this global variable but I could not get it to work correctly with Oracle.
Any help would be greatly appreciated.
Sue
July 21, 2004 at 1:53 am
Coincidentally today's new article on sqlservercentral.com may be of great help to you.
http://www.sqlservercentral.com/columnists/hji/comparisonoforacledrivers.asp
Regards
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply