RE: DBLINK from Oracle to SQLserver
Date: Tue, 1 May 2012 12:00:39 -0500
Message-ID: <C5533BD628A9524496D63801704AE56D75B249203E_at_SPOBMEXC14.adprod.directory>
Oracle Gateway is a separately licensable option I think (I know it used to be).
Chris Taylor
"Quality is never an accident; it is always the result of intelligent effort." -- John Ruskin (English Writer 1819-1900)
Any views and/or opinions expressed herein are my own and do not necessarily reflect the views of Ingram Industries, its affiliates, its subsidiaries or its employees.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Imtiaz Merchant
Sent: Tuesday, May 01, 2012 11:58 AM
To: RStorey_at_DCSO.nashville.org
Cc: oracle-l_at_freelists.org
Subject: Re: DBLINK from Oracle to SQLserver
Robert,
Looks like you are using the generic ODBC driver. Install the Oracle gateway to SQL Server. I just did that a couple of weeks ago myself and it works like a charm. The documentation that comes along with the product is good too. It is the 5th CD in the 11g CD Pack.
Regards,
Imtiaz
On May 1, 2012, at 11:01 AM, "Storey, Robert (DCSO)" <RStorey_at_DCSO.nashville.org> wrote:
> Morning folks.
> This shouldn't be difficult, but as usual, it is. Creating a link between an Oracle DB and a Sql server DB.
>
> 1) I setup the System DSN and tested it. Works
> 2) Modified the TNSNAMES.ORA file - check
> 3) Modified the Listener file and reloaded - check
> 4) TNSping of the TNSnames entry successful - check
> 5) Created the inithsodbc.ora file - check
> 6) created a dblink putting the username/password in "" and the odbc
> service in ' ' - check
> 7) link created successfully - check
> 8) select * from table_at_linkname -ERROR
> Got the error back
> ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
> [Generic Connectivity Using ODBC][Microsoft][ODBC SQL Server
> Driver][SQL Server]Cannot open database "test" requested by the login. The login failed.
> (SQL State: 37000; SQL Code: 4060)
> ORA-02063: preceding 2 lines from BOBLINK
>
> Dropped the link and recreated it using just a junk username and password. Got the same error. Metalink has gone to sleep on me.
>
> I'm missing something simple here but not sure what it is. Do I have to use a username with DBA privs on the sql server?
>
> Thanks
> Bob
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue May 01 2012 - 12:00:39 CDT