×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Linking to Oracle from SQL-Server
2

Linking to Oracle from SQL-Server

RE: Linking to Oracle from SQL-Server

2
Mike,

I had a battle setting this up, but got there in the end. Haven't got the script in front of me, but ...

a) sp_addlinkedserver @server='OracleLinkedDB' @srvproduct='Oracle' @provider='MSDASQL' @provstr='DSN=<OracleODBCDSN>;UID=;PWD=;'

b) sp_addlinkedsrvlogin @rmtsrvname='OracleLinkedDB' @useself=false @rmtuser='<OracleUser>' @rmtpassword='<OraclePassword>'

[I believe the Oracle user needs DBA privileges, but not certain!]

Oracle select statements must be entirely in uppercase for this to work, so, to select current users (assuming synonym is set up) ...

SELECT * FROM ORACLELINKEDDB..ORACLEUSER.V$SESSION

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 :

sp_configure @configname='remote query timeout (s)' @configvalue=0
go
reconfigure
go

I'll double-check this info when I'm back in the office tomorrow.

Mark.

RE: Linking to Oracle from SQL-Server

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

RE: Linking to Oracle from SQL-Server

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.

Mark.

RE: Linking to Oracle from SQL-Server

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close