Using SQL Server to collect information from your Oracle server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Rudy Panigas » Using SQL Server to collect information from...

15 posts, Page 1 of 2

1 2 »»

Using SQL Server to collect information from your Oracle server

Rate Topic

Display Mode

Topic Options

Author

Message

Rudy Panigas

Posted Sunday, June 05, 2011 11:20 PM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 5:32 PM
Points: 306, Visits: 1,013

Comments posted to this topic are about the item Using SQL Server to collect information from your Oracle server

Post #1120025

kevriley

Posted Monday, June 06, 2011 2:49 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:01 PM
Points: 2,677, Visits: 2,273

In the definition of the linked server, is 'ORACLESP' the resolved name from tnsnames, or is it the actual server name? Or either?

Post #1120097

mohammed moinudheen

Posted Monday, June 06, 2011 2:50 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:34 PM
Points: 2,170, Visits: 3,582

Thank you Rudy, nice article

Mohammed Moinudheen

Post #1120101

Rudy Panigas

Posted Monday, June 06, 2011 5:02 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 5:32 PM
Points: 306, Visits: 1,013

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

Post #1120162

kevriley

Posted Monday, June 06, 2011 5:19 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:01 PM
Points: 2,677, Visits: 2,273

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.

Post #1120172

Rudy Panigas

Posted Monday, June 06, 2011 5:23 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 5:32 PM
Points: 306, Visits: 1,013

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

Post #1120174

kevriley

Posted Monday, June 06, 2011 5:28 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:01 PM
Points: 2,677, Visits: 2,273

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

Post #1120177

Rudy Panigas

Posted Monday, June 06, 2011 5:37 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 5:32 PM
Points: 306, Visits: 1,013

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

Post #1120187

JunkMail Victim

Posted Monday, June 06, 2011 6:13 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:54 PM
Points: 18, Visits: 31

Great article. It's so much easier to gather Oracle information from SQL Server than Oracle itself.

Post #1120207

daveriya

Posted Monday, June 06, 2011 9:41 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 11, 2011 11:22 AM
Points: 100, 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

Post #1120354

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions