Linking to Oracle from SQL-Server 2

[MikeLacey]

Is there a way to access Oracle Data from a SQL-Server database - in the same sort of way that you can have remote databases in Oracle?<br>
<br>
-ml<br>
<p>Mike Lacey<br><a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[mark06]

Mike,<br>
<br>
I had a battle setting this up, but got there in the end. Haven't got the script in front of me, but ...<br>
<br>
a) sp_addlinkedserver @server='OracleLinkedDB' @srvproduct='Oracle' @provider='MSDASQL' @provstr='DSN=&lt;OracleODBCDSN&gt;;UID=;PWD=;'<br>
<br>
b) sp_addlinkedsrvlogin @rmtsrvname='OracleLinkedDB' @useself=false @rmtuser='&lt;OracleUser&gt;' @rmtpassword='&lt;OraclePassword&gt;'<br>
<br>
[I believe the Oracle user needs DBA privileges, but not certain!]<br>
<br>
Oracle select statements must be entirely in uppercase for this to work, so, to select current users (assuming synonym is set up) ...<br>
<br>
SELECT * FROM ORACLELINKEDDB..ORACLEUSER.V$SESSION<br>
<br>
should work. If you get an error message referring to incorrect property settings, the remote query timeout option is set too low. I just set mine to 0, as follows :<br>
<br>
sp_configure @configname='remote query timeout (s)' @configvalue=0<br>
go<br>
reconfigure<br>
go<br>
<br>
I'll double-check this info when I'm back in the office tomorrow.<br>
<br>
Mark.

 

[MikeLacey]

Mark - you are a georgeous person!<br>
<br>
What versions of Oracle/SQL-Server does this work with then?<br>
<br>
I'd appreciate the double check.<br>
<br>
Regards<br>
<br>
Mike<br>
<p>Mike Lacey<br><a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[calahans]

Excellent!!!!!!!!!!!

 

[mark06]

Only tried with Oracle 7.3.4 and SQL Server 7.0. I'm sure it'll work with Oracle 8 as well, since all the translation is done through ODBC. <br>
<br>
Mark.

 

[mark06]

Oops - remember to put commas between the SQL Server stored procedure parameters.

 

[MikeLacey]

thx Mark<br>
-ml <p>Mike Lacey<br><a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>