May 7, 2002 at 1:09 am
Hi
We have a DTS job which uses a linked server to an Oracle server and inserts data into an SQL Server 2000 table. We have had to split the architecture so that we now have an application server running the DTS jobs and an SQL Cluster. The problem is that we do not want to install the Oracle provider to the cluster. What we want to do is have the linked server on the Application server using Oracle Provider and on the cluster a linked server pointing to the linked server on the application server but using the SQL Provider.
Does anyone know if this setup is possible?
thanks for any help
May 7, 2002 at 5:37 am
Sounds feasible but when I try this I get "The operation could not be performed because the OLE DB provider 'OraOLEDB.Oracle' does not support distributed transactions." and am not sure there is a way to work around this. Try in a test environment to see if you can get it work.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 7, 2002 at 4:00 pm
Is there any reason why you must use linked servers?
It looks to me like they are just complicating matters.
Why not specify the Oracle database as the DTS source, and the SQL cluster as the DTS destination and just execute the package on your application server?
May 10, 2002 at 6:29 am
We also use an Oracle Database synchronized with a SQL2000 Server.
The first thing to know is that ther is a wellknown bug in that context.
The linked Server works but so slow!
If you need only SELECT statements on Oracle DB better use an OPENROWSET command
like this one:
SELECT MAT_USR, NOM_USR, PRENOM_USR, SIGNET_USR, CC_DEFLT_USR, STATUS_USR into #b
FROM OPENROWSET(
'MSDAORA',
'SOURCE';'USERNAME';'PASSWORD',
'Select DISTINCT FIELD1, FIELD2, FIELD3, FIELD4 instance.table1 abc where abc.FIELD1 = 3')
The only little problem is that it does not work when called though a storedproc or client code ...
-> 'OraOLEDB.Oracle' does not support distributed transactions."
but I also dicoverd a walkaround :)))
-> Put this into a job executed by a trigger you fire on an update of a TOTO table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply