Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dogli
Starting Member
32 Posts |
 Posted - 2002-09-25 : 11:39:02
|
| Hi,I have successfully set up a linked server to Oracle 8.1.7 from my SQL server 7.0 box. But it seems that I cannot query or view the data under the VIEW under the linked server. According to BOL, I can only query table with sytax like this: OracleLinkedServerName..OwnerUserName.TableNameBut I do want to query some VIEW under the linked server. I there any good idea to do so?Thanks so much. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-25 : 11:50:57
|
| Have you tried that linked server syntax? It should work, I've done it with Oracle linked servers before. You might need to include the database name:SELECT * FROM OracleLinkedServerName.OracleDBName.OwnerUserName.TableNameIt should also work with views and synonyms. If not, you can try using OPENQUERY or OPENROWSET, these are documented in Books Online. |
 |
|
|
dogli
Starting Member
32 Posts |
Posted - 2002-09-25 : 12:18:54
|
| Thanks for your help, Robvolk. Yes, I can access the VIEW with OPENQUERY. But I read some posts in some groups, all of the those guys say you cannot use linked server sytax to access Views. For example:"You will not be able to view the data in the view under linked servers,but through a query or stored proc, data can be manipulated using OPENQUERY."BTW, I have an ID to access the remote Oracle server, but I don't know the OwnerUserName of the tables. Is it the same as my login ID? How can I find it?Also, I have anohter question: Is it a good idea to create local view in SQL server by using Openquery so that I can just use it for all future queries? If yes, how can I update the local view automatically based on the remote view?Sorry for so many questions. Thank you very very much for your help! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-25 : 12:33:34
|
quote:
But I read some posts in some groups, all of the those guys say you cannot use linked server sytax to access Views. For example: "You will not be able to view the data in the view under linked servers,but through a query or stored proc, data can be manipulated using OPENQUERY...
Well, all of "those guys" are smoking crack, cause I've been able to query views and synonyms using the 4-part naming convention. However, there may be issues with the OLE DB/ODBC drivers you are using to connect to Oracle that might prevent it from happening. I had terrible times getting these to work correctly, but I was finally able to do it with the Microsoft OLE DB Provider for Oracle (MSDORA).There may also be a configuration issue on your Oracle server that prevents it from working correctly, you'd have to ask your Oracle admin about that. I can't really help in that regard as I had no ability to find out about the Oracle configuration I was working with. You'd also have to contact your Oracle admin about the owner names of the tables, but you can try linking to the Oracle server in MS Access. When the tables are linked they will include the owner name, separated from the table/view named with a period (., which is converted to an underscore in Access). You can also try the query designer in DTS, it will list the owner name too.As far as updating, as far as I know it *can* work, but I only had read access to Oracle so I never tested it. The OLE DB providers need to be able to support it. If the MS provider doesn't work you can try the Oracle OLE DB providers, you should be able to get them at http://technet.oracle.com (might have to register to download them...and they're BIG too, about 40MB if I remember correctly) |
|
|
|
dogli
Starting Member
32 Posts |
Posted - 2002-09-25 : 12:48:10
|
| Vow,Great information, great help. Thank you sooooooo much! :) |
|
|
|
dogli
Starting Member
32 Posts |
Posted - 2002-09-25 : 13:39:10
|
Hi Robvolk,Just want you know that you are right- I can query VIEW under linked server with 4-part naming conversion as you mentions. The format I use is: OracleLinkedServerName..OwnerUserName.TableName But the speed is really slow as the Oracle database is in romote server! I can execute it in Query Analyzer, but it is time-out if I run the same query in ASP page. So, I am still thinking it is a good idea to create a local view ... Do you think so?Again, thanks! Cheers! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-25 : 13:50:30
|
quote:
So, I am still thinking it is a good idea to create a local view ... Do you think so?
It won't improve the performance though. SQL Server would need to open a connection to the Oracle view anyway. If anything, write your ASP code to query the Oracle server directly, it should be a little faster as there will be no overhead using the linked server. Unless that's not an option, or you need to combine Oracle data with SQL Server data, linked servers won't really improve the situation here.Whenever I wanted to query the Oracle server from ASP, I did a lot of tests to find what would perform well, and only ran those queries. I did not allow ad-hoc querying. And if there was a lot of information that I needed from Oracle, but I also needed fast performance, I would run DTS jobs each day to grab the data from Oracle and I would query the local SQL Server tables intead. Synchronization wasn't a problem because the data was only updated once a day anyway. I could then add indexes to the local SQL Server tables to optimize their performance for my needs. Updates would be done with stored procedures, you might even be able to create Oracle procedures to do it, and they would provide the best possible performance. As long as you can work your queries and updates around indexed columns and primary keys, you'll see good performance.In short, it's doable, but it will need a lot of work and careful thinking about what you need to do. Look at doing only the most critical things first and get them tweaked as much as possible. Don't be surprised if you need to cut back on some features, and don't get discouraged either.Edited by - robvolk on 09/25/2002 13:51:44 |
|
|
|
dogli
Starting Member
32 Posts |
Posted - 2002-09-25 : 14:18:49
|
| You are right,Robvolk. I tried different queries in ASP page, and the speed varied greately. For example, if I use the 4-part name conventions query, it was very slow. Actually, it takes 2.5 minutes to run the same query in Query Analyzer.But if I use OPENQUERY in ASP page, it is very fast. Also, I am thinking to take your idea - to import data to local SQL server by DTS everyday to improve performance.....Thanks for all of your help. |
|
|
|
|
|
|
|
|
How to query Oracle View from SQL server