Gateways

Question

Gateways

pattyatt

Newbie

Points: 6
More actions
October 29, 2002 at 3:19 pm

#79593

How to I set up a gateway to run a SQLServer procedure from SQLServer to an Oracle DB?
What's the compatibility matrix?
anyone out there know?
November 1, 2002 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Describe what you mean by gateway, I have missed this somewhere and may not understand it as that terminology? Other than that sorry, not sure.

pattyatt Newbie Points: 6 More actions · Answer 2

What I meant was, what procedure would I use if I wanted to run a SQLServer procedure against an Oracle database.

To do the reverse, you would use Transparent Gateways feature of Oracle, which is where that term came from.

Antares686 SSC Guru Points: 125444 More actions · Answer 3

The query would have to run in the from space against either a linked server using OPENQUERY or there using OPENROWSET.

If you use a linked server then something like this is best.

SELECT * FROM OPENQUERY (LINKEDSERVER,'SELECT COl1, COL2, COL3 FROM Schema.Table WHERE')

If thru on the fly connect with opensopenrowset something like this.

OPENROWSET ( 'ole provider_name'

, { 'datasource' ; 'user_id' ; 'password'

| 'provider_string' }

, 'query'

)

I hope this is the information you are looking for.

These thread might also help a bit.

http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=2684&FORUM_ID=20&CAT_ID=2&Topic_Title=Updating%20SQLServer%20Table%20using%20remote%20Oracle%20DB&Forum_Title=Connecting

http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=4069&FORUM_ID=19&CAT_ID=3&Topic_Title=Linking%20to%20a%20Linked%20Server&Forum_Title=Data%20Transformation%20Services%20(DTS)

http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=2526&FORUM_ID=19&CAT_ID=3&Topic_Title=Linked%20Server%20-%20Poor%20performance?&Forum_Title=Data%20Transformation%20Services%20(DTS)

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

You'll probably want to look at making the Oracle database a linked server. If you have SQL code that can run entirely on the Oracle side, take a look at the OPENQUERY() command. It provides the means to do a pass-through query. If you need to retrieve all the data from a particular table or the like and process it on the SQL Server side, you can use the four-part naming convention as is typical with linked servers.

There's a whole host of information on linked servers in SQL Server's Books Online. You may also want to take a look at Brian Knight's primer on linked servers:

http://www.sqlservercentral.com/columnists/bknight/linkedserver.asp

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley