[sqr-users] Need Help with Oracle DB Link

["Pospical, Alex" <Alex_Pospical@RL.gov>]

Hello
 
I have spent the last few days searching through the archives on this
subject while waiting for my registration confirmation and while I have
found many postings that have helped I have still not been able to make this
work.  Any additional or specific help would be appreciated.
 
I have been trying to set up a process to copy a table from a SQL 2000
Server database to an Oracle 8.1.7.3.0 database inside of an SQR running on
SQL Server.  The SQR is executed from the SQL environment and I have a
database link defined to the Oracle database using Microsoft Ole db for
Oracle.  I have verified that the link works by running the delete and
insert processes from SQL Query Analyzer.  I tried using the following
select to see if I can even get to the Oracle database from an SQR:
 
BEGIN-SELECT ON-ERROR=SELECT-ERROR
 
a.XXXX_XXX  &xxxx_xxx
FROM DDMDB..HRIS_DM.XX_XXXXXXXXX_XXX a
 
END-SELECT
 
DDMDB is the database link, HRIS_DM is the Oracle schema user and the
remainder is the oracle table (standard 4 part name convention)  This same
syntax works from SQL Query Analyzer but causes the following error when
executed from SQR.
 
SQR 5528) ODBC SQL dbdesc: SQLNumResultCols error 7300 in cursor 20:
   [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].
(SQR 5528) ODBC SQL dbdesc: SQLNumResultCols error 7391 in cursor 20:
   [Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be
performed because the OLE DB provider 'MSDAORA' was unable to begin a
distributed transaction.
 
Error on line 56:
   (SQR 3716) Error in SQL statement.
 
Any ideas/suggestions would be appreciated.
 
Thanks
 
Alex Pospical
LMSI
 
 
 
 
 

 
 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users