Working. Changed the authentication method to sqlserver using the supplied account and used the same account to create the dblink. Change 1 step in the directions.....bad dba. -----Original Message----- From: Taylor, Chris David [mailto:ChrisDavid.Taylor@xxxxxxxxxxxxxxx] Sent: Tuesday, May 01, 2012 12:01 PM To: 'merchanti@xxxxxxxxxxx'; Storey, Robert (DCSO) Cc: 'oracle-l@xxxxxxxxxxxxx' Subject: RE: DBLINK from Oracle to SQLserver 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Imtiaz Merchant Sent: Tuesday, May 01, 2012 11:58 AM To: RStorey@xxxxxxxxxxxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx 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@xxxxxxxxxxxxxxxxxx> 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@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 > > > > -- > //www.freelists.org/webpage/oracle-l > > > -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l