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:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOC I]
"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
We 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:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOC I]
"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
Showing posts with label situationthe. Show all posts
Showing posts with label situationthe. Show all posts
Friday, March 9, 2012
Wednesday, March 7, 2012
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
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
Subscribe to:
Posts (Atom)