RE: Querying MS Sql from Oracle
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, achoto@xxxxxxxxxxxx, JSweetser@xxxxxxxx
- Date: Mon, 4 Jan 2010 13:37:07 -0800 (PST)
I can vouche for this being fixed on the timestamp issue, (I *think* it's been
fixed on dates, too.) I just used SQL Loader with Proxy logs from our Unix
servers to Oracle and they are joining in SQL Server with no problem on the
timestamp(6) and timestamp(2) datatype from Oracle.
Thanks!
Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
"Go away before I replace you with a very small and efficient shell script..."
--- On Mon, 1/4/10, Sweetser, Joe <JSweetser@xxxxxxxx> wrote:
From: Sweetser, Joe <JSweetser@xxxxxxxx>
Subject: RE: Querying MS Sql from Oracle
To: kjped1313@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx, achoto@xxxxxxxxxxxx
Date: Monday, January 4, 2010, 1:04 PM
I would agree with this method and we use it successfully where I work. There
are some minor gotchas we found around dates and/or timestamps. One specific
one is that SQL 2000 (not sure about 2005 and higher L) does not like Oracle’s
timestamp datatype and will error out if a query includes a timestamp column.
We got around that by creating views with something like this for the timestamp
columns:
to_date(to_char(CREATED_TIME, 'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY
HH24:MI:SS') created_time
In essence, converting timestamp data to character and then to date. A little
convoluted but it gets the data visible to SQL.
hth,
-joe
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Kellyn Pedersen
Sent: Monday, January 04, 2010 12:47 PM
To: oracle-l@xxxxxxxxxxxxx; achoto@xxxxxxxxxxxx
Subject: Re: Querying MS Sql from Oracle
I may get slammed for this, but as a DBA in both platforms and utilizing both
products in the past, I have to ask- Is there anyway you can simply utilize
the Microsoft OLE DB Provider for Oracle, adding the Oracle database server as
a linked server on the SQL Server side and query from there?
I find this to be more stable and require less DBA upkeep....
Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
"Go away before I replace you with a very small and efficient shell script..."
--- On Mon, 1/4/10, Ana Choto <achoto@xxxxxxxxxxxx> wrote:
From: Ana Choto <achoto@xxxxxxxxxxxx>
Subject: Querying MS Sql from Oracle
To: oracle-l@xxxxxxxxxxxxx
Date: Monday, January 4, 2010, 9:05 AM
We have some MS Sql databases and need to run some queries from Oracle. Our
Oracle database is at 9.2.0.8 version running on Solaris. According to Oracle
we need to set up a Database Gateway for ODBC either on a Windows platform,
Linux or Solaris.
Our MS Sql server is 64 bit, and apparently DG4ODBC is only certified for 32
bit on Windows and Linux. If on Solaris a third party like the ODBC driver from
Data Direct Technologies is needed, and the 9i database needs to be patched.
After installing DG4ODBC a listener and tnsnames.ora need to be configured.
Then a database link needs to be created on Oracle.
The 9i database has something called hsodbc but Oracle says that is not
certified anymore.
Has someone in the list done something like this?
Thank you
Ana
---------------------------------------------------------
Ana E. Choto
Oracle Database Administrator
American University
Office of Information Technology
Phone (202) 885-2275
Fax (202) 885-2224
** AU IT will never ask for your password via e-mail.
Don't share your password with anyone!
Other related posts:
