Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using SQL Server to collect information from your Oracle server


Using SQL Server to collect information from your Oracle server

Author
Message
Rudy Panigas
Rudy Panigas
Mr or Mrs. 500
Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)

Group: General Forum Members
Points: 596 Visits: 1306
Comments posted to this topic are about the item Using SQL Server to collect information from your Oracle server



kevriley
kevriley
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2811 Visits: 2606
In the definition of the linked server, is 'ORACLESP' the resolved name from tnsnames, or is it the actual server name? Or either?
M&M
M&M
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2931 Visits: 3900
Thank you Rudy, nice article

M&M
Rudy Panigas
Rudy Panigas
Mr or Mrs. 500
Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)

Group: General Forum Members
Points: 596 Visits: 1306
kevriley (6/6/2011)
In the definition of the linked server, is 'ORACLESP' the resolved name from tnsnames, or is it the actual server name? Or either?


This is the serivce name which is located in the TNSNAMES.

Hope this helps,

Rudy



kevriley
kevriley
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2811 Visits: 2606
Thought it was, still doesn't help me.

I have the error

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLESRV1".
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLESRV1" returned message "ORA-12541: TNS:no listener". (.Net SqlClient Data Provider)

I think there must be something else preventing me from making the connection.

As an aside I can connect fine to this Oracle instance using TOAD/ Oracle SQL Developer, so I know that I am not 'blocked' as such.
Rudy Panigas
Rudy Panigas
Mr or Mrs. 500
Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)

Group: General Forum Members
Points: 596 Visits: 1306
You need to change the "ORACLESRV1" parameter to your server name. Verify all your setting and try again. If you have an Oracle DBA than ask them for some help.

Do you installl the Oracle client on your SQL server? I had lots of issues until I installed it. The installation must be on your server not your workstation.

Rudy



kevriley
kevriley
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2811 Visits: 2606
Rudy,

the ORACLESRV1 is just the name of the linked server, that you can then reference in SQL - that shouldn't have any effect?


Yes I have the Oracle client installed
Rudy Panigas
Rudy Panigas
Mr or Mrs. 500
Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)

Group: General Forum Members
Points: 596 Visits: 1306
In my article ORACLESRV1 is the server name. Maybe you should first try to setup the link connection with the GUI. Once you see it working then you can modify the script to fit your needs.

Please verify:

@server = your oracle server name
and
@datasrc = service name located in your tnsnames

One last thing, did you install the Oracle client onto the SQL server that you connected to?

If you are still having issues you may have to talk to your Oracle DBA as I'm not sure what else to say.

Thanks,

Rudy



JunkMail Victim
JunkMail Victim
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 33
Great article. It's so much easier to gather Oracle information from SQL Server than Oracle itself.
daveriya
daveriya
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 236
if you installed oracle client on sql server ,then what is the use of if.u need to create linked server to oracle client and then oracle client make connection to oracle server
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search