RE: Re: Querying MS Sql from Oracle

If memory is running correctly, Oracle still has a TRANSPARENT GATEWAY
to MS Sql that does an extremely fine job at handling the datatypes and
needs very little care & feeding.
 

Dick Goulet 
Senior Oracle DBA/NA Team Lead 
PAREXEL International 

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Kellyn Pedersen
Sent: Wednesday, January 06, 2010 12:59 AM
To: niall.litchfield@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx; achoto@xxxxxxxxxxxx
Subject: Re: Re: Querying MS Sql from Oracle


Niall has a good point here...  This was actually a requirement,
(limiting datatypes) to keep some developers from getting "too fancy" on
the Oracle side in one environment, another has to do with some of the
issues I've seen with certain applications using the new Oracle version
through Reporting Services, (anyone with .Net apps out there, anyone,
Bueller, Bueller? "))  It can be a bit buggy still, where the old
standard of OLEDB, even though they do complain about it, has been
stable in any environment I've used it in...

There is something to be said for these two points though-  Do you have
a requirement to support a wide variety of datatypes and do you have the
luxury of installing the Oracle client on the SQL Server.
 
Good points, Niall...:)

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 Tue, 1/5/10, niall.litchfield@xxxxxxxxx
<niall.litchfield@xxxxxxxxx> wrote:



        From: niall.litchfield@xxxxxxxxx <niall.litchfield@xxxxxxxxx>
        Subject: Re: Re: Querying MS Sql from Oracle
        To: "Kellyn Pedersen" <kjped1313@xxxxxxxxx>
        Cc: oracle-l@xxxxxxxxxxxxx, achoto@xxxxxxxxxxxx
        Date: Tuesday, January 5, 2010, 4:09 AM
        
        
        I don't see why you'd be slammed for that, it's a perfectly
sensible approach. I'm interested that you recommend the MS OLEDB
provider though rather than a current Oracle one. The former doesn't
support all Oracle datatypes particularly well (or in some cases at
all). It would of course require a client install on the sql server
which may be beyond acceptability :( 
        
        Niall 
        
        On 4 Jan 2010 19:46, Kellyn Pedersen <kjped1313@xxxxxxxxx>
wrote: 
        > 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: