| Author |
Topic |
|
fqiao70
Yak Posting Veteran
52 Posts |
 Posted - 2003-08-22 : 11:57:37
|
| Hi everyoneI want to know how to link a oracle database through SQL server, I want to use SQL server Agent to monitor some oralce database.Please suggest! Thank you! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-22 : 12:30:15
|
| To link Oracle, you'll need to setup a linked server. This has been discussed a few times here, so just do a forum search on Oracle linked server and you should find the information that you need.Tara |
 |
|
|
fqiao70
Yak Posting Veteran
52 Posts |
Posted - 2003-08-22 : 12:48:58
|
| I used configured ODBC database source and used OLEDB provider for ODBC on SQL server box, now it's working. I wonder how do I select a table in Query Analyzer?? and when I right click the table I only copy option, how can I open the table? Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-22 : 12:52:43
|
| Just use the four part naming convention of the table:SELECT Column1, Column2FROM LinkedServerName.DBName.OwnerName.TableNameWHERE...Tara |
|
|
|
fqiao70
Yak Posting Veteran
52 Posts |
Posted - 2003-08-22 : 13:03:28
|
| but it doesn't have a database showing in the database folder, when you open Query anlyzer the default database is Master and I typed " select * from LinkedServerName.DBName.OwnerName.TableName" it says "invalid object"??????????????? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-22 : 13:12:57
|
| You don't have to select a database in Query Analyzer in order to be able to pull data from it. You can always use the 3 or 4 part naming convention to select the database for you. The query that I gave you would definitely work for a SQL Server linked server, I just don't know what query you would need for Oracle. Do a forum search here as this has been discussed before.Tara |
|
|
|
fqiao70
Yak Posting Veteran
52 Posts |
Posted - 2003-08-22 : 14:20:18
|
| I think here should be two dots after LinkedServerName like:select * from LinkedServerName..DBName.OwnerName.TableNamebut it gives me another error:OLE DB provider 'dip1' does not contain table '"tops"."actions"'. The table either does not exist or the current user does not have permissions on that table.but I can see the table from the linked server just can't open it???I use sys in oracle I should have the full permission on that database????Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-22 : 14:26:06
|
| But what account are you using for the linked server? What option did you select when you set it up?Tara |
|
|
|
fqiao70
Yak Posting Veteran
52 Posts |
Posted - 2003-08-22 : 14:35:05
|
| in linked server, under security tab, the option is "be made using this security context" then I typed the sys and pwd( which has full permission) for my oracle database. it linked through, but just table and view, and I can see all tables' name but cannot see their content. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-22 : 14:42:59
|
| Does Oracle have a tool like SQL Profiler so that you can see what is occurring on the Oracle server? If so, I would use that to see what is going.Tara |
|
|
|
|
how to link a oracle database?