Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using SQL Server to collect information from your Oracle server Expand / Collapse
Author
Message
Posted Sunday, June 5, 2011 11:20 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:24 AM
Points: 312, Visits: 1,107
Comments posted to this topic are about the item Using SQL Server to collect information from your Oracle server


Post #1120025
Posted Monday, June 6, 2011 2:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:45 AM
Points: 2,716, Visits: 2,457
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
Posted Monday, June 6, 2011 2:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 8, 2014 6:15 PM
Points: 2,278, Visits: 3,793
Thank you Rudy, nice article

Mohammed Moinudheen
Post #1120101
Posted Monday, June 6, 2011 5:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:24 AM
Points: 312, Visits: 1,107
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
Posted Monday, June 6, 2011 5:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:45 AM
Points: 2,716, Visits: 2,457
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
Posted Monday, June 6, 2011 5:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:24 AM
Points: 312, Visits: 1,107
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
Posted Monday, June 6, 2011 5:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:45 AM
Points: 2,716, Visits: 2,457
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
Posted Monday, June 6, 2011 5:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:24 AM
Points: 312, Visits: 1,107
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
Posted Monday, June 6, 2011 6:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 10, 2013 6:21 AM
Points: 18, Visits: 33
Great article. It's so much easier to gather Oracle information from SQL Server than Oracle itself.
Post #1120207
Posted Monday, June 6, 2011 9:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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 »

Add to briefcase 12»»

Permissions Expand / Collapse