RE: DBLINK from Oracle to SQLserver

From: Storey, Robert (DCSO) <"Storey,>
Date: Tue, 1 May 2012 17:09:09 +0000
Message-ID: <33B09BA255BD7142B217C2C0A0D018340684A9_at_DCSOSVMS02.dcso.org>



Sorry, forgot to add other useful information.

Oracle 9iR2 9.2.0.8 on w2003
SqlSever 2008 on W2008.

I followed all of the steps I found in the white papers. The only difference was that I used NT authentication when I setup the ODBC connection (using my credentials) and then a supplied username and password from the Sqlserver side for the link creation.

I've not used the database owners credentials yet, but that may be the next setup. Gonna recreate the ODBC DNS entry with the same credentials as the dblink and see what happens.

I'll look at oracle gateway but don't think we are going to have the budget right now for another licensed option in Oracle...

-----Original Message-----
From: Taylor, Chris David [mailto:ChrisDavid.Taylor_at_ingrambarge.com] Sent: Tuesday, May 01, 2012 12:01 PM
To: 'merchanti_at_hotmail.com'; Storey, Robert (DCSO) Cc: 'oracle-l_at_freelists.org'
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_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-l
Received on Tue May 01 2012 - 12:09:09 CDT

Original text of this message