I have been asked to include a view from our Oracle system in one of my SQL queries. As a programmer I can write querries all day long, but I have no administration skills.
Can someone just give me the basics on creating this ability? On my development system I have installed the Oracle client and configured an ODBC driver that points to the Oracle views. This has allowed me to create a linked table in Access, but I have never attempted to port this to SQL Server 2k.
I assume I just load the drivers on the production system as before, but do I then use an ODBC driver again, or is there a better method with SQL Server? If its the ODBC method, then how do I add a linked table in SQL?
Sorry for the basic questions, and I really appreciate any help.
I'd suggest just using the Microsoft provided drivers unless you need specific Oracle functionality. It makes life a lot simpler in the long run.
Just use Enterprise Mangler or sp_addlinkedserver to make the Oracle server a linked server from your SQL Server, and life ought to be lovely. I don't remember needing to add any drivers to make it work, but I almost always use the Enterprise Edition of MS-SQL so I'm not always aware of what ships/installs with other editions.
Thanks for that tip - I have looked into sp_addlinkedserver and I can probably figure it out, but using the mangler just seems easier to me. If I start up the register server wizard, it seems to only be looking for SQL servers. I don't see any way to change the provider. Is it possible that the developer edition I am using doesn't have the functionality?