OLE DB provider 'MSDAORA' reported an error.(SQL Server 7399)

Hi all
here's the situation:
The Error:
OLE DB provider 'MSDAORA' reported an error. The provider did not give any
information about the error. (SQL Server 7399)OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x80004005
The error araises when quering the remote oracle database. The error does
NOT happen every time, but sometimes (20 times a day with 10 queries am
minute)
The Databases:
MSSQL2000 (SP3a) with link to Oracle9i Enterprise Edition 9.2.0.4.0 64bit
Production.
The call using open query syntax in a function looks like (on MSSQL):
'insert into SAP_ORGANISATION_Temp select ''' + @.pDataID + ''', * from
openquery (SAP_VM, ''select * from ARSREMEDY.ZORGANISATION_FULL ' + @.sqlq +
''')'
where: SAP_ORGANISATION_Temp is a local table, SAP_VM is the linked server,
ARSREMEDY is the user, ZORGANISATION_FULL is a view on the oracle side with
some join, @.sqlq is some condition
Additional info:
oracle client is: 8.1.7.0.0
Windows Registry says:
& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\MSDTC\MTxOCI]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"
SQLNET ComponentVersion: 2.60.6526.2 (at least that's what the regestry says
)
msdaora.dll File Version: 2.71.9030.0
Hope this is any good to someone.
Any help appreciated
MarcusWe recently came across a similar situation. You could try setting your
environment before the execution:
SET IMPLICT_TRANSACTIONS OFF
SET XACT_ABORT ON
In our case, the vendor had coded a BAD trigger against the destination
table. It was bad in the sense that it was coded row based instead of set
based; so, it returned an error whenever the number of records attempted to
insert were more than one, but the Distributed Transaction Coordinator
returned the error message you received.
Sincerely,
Anthony Thomas
"Marcus" <Marcus@.discussions.microsoft.com> wrote in message
news:D9CF72F5-BE5B-41A5-923B-36BBE0F93A52@.microsoft.com...
Hi all
here's the situation:
The Error:
OLE DB provider 'MSDAORA' reported an error. The provider did not give any
information about the error. (SQL Server 7399)OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x80004005
The error araises when quering the remote oracle database. The error does
NOT happen every time, but sometimes (20 times a day with 10 queries am
minute)
The Databases:
MSSQL2000 (SP3a) with link to Oracle9i Enterprise Edition 9.2.0.4.0 64bit
Production.
The call using open query syntax in a function looks like (on MSSQL):
'insert into SAP_ORGANISATION_Temp select ''' + @.pDataID + ''', * from
openquery (SAP_VM, ''select * from ARSREMEDY.ZORGANISATION_FULL ' + @.sqlq +
''')'
where: SAP_ORGANISATION_Temp is a local table, SAP_VM is the linked server,
ARSREMEDY is the user, ZORGANISATION_FULL is a view on the oracle side with
some join, @.sqlq is some condition
Additional info:
oracle client is: 8.1.7.0.0
Windows Registry says:
& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\MSDTC\MTxOCI]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"
SQLNET ComponentVersion: 2.60.6526.2 (at least that's what the regestry
says)
msdaora.dll File Version: 2.71.9030.0
Hope this is any good to someone.
Any help appreciated
Marcus