After a recent upgrade to SQL Server 2005 my linked server export to Oracle 10g no longer gives me the option of deleting first or appending. Any clues?
More: Since there are 3 machines in this mix so I’m not sure which one I should be checking. Computer A is running Windows 2000 and SQL Server 2000; computer B is running Windows XP Professional and Oracle Server 10.2.0.2; computer C is running Windows XP Professional with SQL Server 2005 client tools (Management Studio) and Oracle 10.2.0.2 client. I am using Computer C to transfer data from Computer A to Computer B. To confuse me even more, using computer D, which has SQL Server 2000 client tools, I can transfer the data from Computer A to Computer B with no problem. Doesn’t that mean A and B are configured correctly?
You need to address computer C in the scenario you outlined as that version of MDAC/ODBC/Oracle client is being used. There is likely a mismatch between the SQL Server 2005 tools and your linked server. I would try the following:
1) ensure computer C is running the latest version of MDAC and an ODBC driver that fully supports Oracle 10g
2) check that you’ve got the latest version of the Oracle client on the linked SQL Server machine. Some customers have said that they require patch 5203839
3) ensure you’ve configured the connection to “Allow inprocess” in the provider options
Finally, check out these MSDN links to ensure your Microsoft software is completely up-to-date for this scenario:
http://support.microsoft.com/kb/280106
http://msdn2.microsoft.com/en-us/library/ms190618.aspx
http://msdn2.microsoft.com/en-us/library/ms189063.aspx
Hope that helps.
