Hi, i need to build some way of connecting Oracle to SQL Server in order to pull data off Oracle and house it temporarily on SQL 2000 for searching.
Basically we've built an intranet using ASP and SQL 2000 and which also pulls data off Oracle, however our connection to Oracle is very slow so an idea to speed it up would be to have SQL store a temporary snapshot of this data and the ASP site query off the fast connection it has to SQL rather than use our slow connection to Oracle.
This temporary snapshot of Oracle data would be stored on SQL 2000 and updated on a scheduled basis.
Any ideas how to do this, or a better way to work this?
Thing is I tried installing the Official Oracle ODBC & Client software on our SQL 2000 server before and it just took out SQL 2000 so would really like to hear other options.
I would look more at speeding up the oracle connection. If the oracle server, web server, and sql-server is on the same lan, oracle should not be slow. How are you connecting from the web server to the oracle server? If it is ODBC, you should use the oracle odbc drivers. The microsoft drivers are very slow in comparison and have compatibality problems. There is no reason that using oracle's odbc drivers will block a connection to sql-server. We are using both databases on a IIS server and have no problems using native drivers.
You do not need a parachute to skydive. You only need a parachute to skydive twice.
the Oracle Server sits inside the office network and the SQL Server sits in a rack outside the office down the road in a secure environment, and the web server (which hosts the asp pages) sits in the same rack as the SQL Server.
Wish I could work out a better way of querying Oracle, any ideas?