Post reply

Maintaining linked servers

  • March 29, 2006 at 8:47 am

    #111507

    Hi Guys!

    I've just made our test server (MS SQL 2000) talking with a remote one (Oracle 8.1.7.4.1) and now I'd like to know how much work it will require in the future. Do I have to make any changes on the MS SQL side when the Oracle guys update to 10i? What do you know about this issue? Please tell me about your experiences, even if it's not with Oracle.

    It was quite a bunch of work to make this link work . First, I had to install the Oracle client software, which contain some OLE DB stuff, on the MS SQL server. I wanted to use Oracles OraOLEDB driver because I've read about problems with Microsoft's MSDAORA and Oracle 8i. After modifying some registry keys the connection seemd to work. And it really did, untill some special data occured in a table - my select statements always ended in some unspecific error (Msg 7399). Insert, update and delete worked most of the time, but with some tables, a simple sql statement just didn't work . Then I tried MSDAORA, which didn't work at all at the begining , but I've searched the internet and found the KB article 280106 which explained how to change some registry keys again. Somehow the Oracle 8.0 driver where set up in the registry while I had to use 8.1... But now, it finally works .

    Thanks and greetings

    Tom

    PS: Sorry about all the emoticons, I'm new in this forum - or is it a board? *duckandcover*

  • April 3, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • April 12, 2006 at 7:38 am

    #632089

    Yeah, I have done this a lot in the past year and have overseen the migration of our 8i database instances to 10g so I have some expereince there as well. Personally, what I like to do is every night I have a scheduled drop and recreation of the linked database connection. I like to try to do that after the Oracle instance's maintenance time. This allows me to make sure that the connection has not gone "stale" or anything.

    Another thing to keep an eye on is the query type being run by your users. Many times I prefer to run things using "OPENQUERY" rather than a simple fully qualified database object name as it allows me to do things in a much more distributed manner and tends to cut down on the network bandwidth usage.

    Other than that, keep an eye on numeric data types as sometimes an Oracle developer will not define all parameters for a numeric type and the driver will throw an error along the lines of it expecting one thing and getting another or some such...

    Almost forgot, make sure to use the 10g driver as it tends to perform a bit better and the security is a bit better. With that in mind though, be aware that if you have developers still using the older Oracle 8i forms tools, they may experience serious problems with 10g in the mix.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply