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'm not sure why you've had such problems with Oracle and SQL Server. They usually behave like siblings... They fight at first until somebody hauls one or the other "out to the woodshed", then they seem to grudgingly accept each other (although they sometimes still squabble).
Things get a wee bit corn-fused sometimes when you install the Oracle ODBC drivers, because most of them either skirt the rules or violate them outright. Most of those problems are well understood by Oracle users (they have to), and are not usually very hard to work around. The biggest problem I've seen is that no two sets of Oracle drivers seem to misbehave in quite the same way, so you are always fighting a new and different battle with them.
Once I get the Oracle ODBC problems resolved, I'm usually "home free" until the next release. If you are having problems beyond that, I'd like to hear about them... It is almost always good to get new challenges!