I am currently having much difficulty setting up a linked server from a MS SQL 2005 64-bit on a Windows 2003 server

If you could specify how you did it, it would be of great help to me. I managed to establish a connection to my oracle from a ms sql 32 bit on another windows server, however this was using the msdaora provider wich to my knowledge is not available in the x64 windows 2003.

In the create new linked server i cannot choose oraoledb - how do I gain access to that provider?

Regards

Peter

I have SQL Server 2005 on windows 2003 64 bit. I initially had a similar trouble to use Oracle Provider for OLE DB of 10g client. After I reinstalled 10gr2 (10.2.0.1) client, applied patch 5203839, checked "Allow inprocess" in the provider options, then it works fine.

i'm also have the same situation where the MSDAORA or Oracle 10G Provider is not visible under the Providers list in SQL Server.

i'm desperate for a (step-by-step how-to) on this. any information would be
greatly appreciated.

thanks in advance.

Our Database Team Resolved this Issue by Chaning the OPENQUERY Statement to a Four Part Syntax.

Old Query:

UPDATE OPENQUERY (DBNAME,'SQL STATEMENT') SET Statement

Revised Query:

UPDATE [DBNAME]..SYSTEM.TABLE SET Statement

The funny thing with this is, if you are Updating a Table with a Single Record such as a Sequence Table, it was necessary to run a SELECT Statement Prior to the UPDATE Command.

Example:

SELECT ORD_NUM FROM [CUSTOMER]..SYSTEM.ORDER_NUMBER_INTERNAL

UPDATE [CUSTOMER]..SYSTEM.ORDER_NUMBER_INTERNAL SET ORD_NUM = (@UNIQUEID + @RECCOUNT + 1)