Getting error 'Root transaction falied to Commit'

[Shaila24072009]

We are getting error 'Root transaction wanted to commit but transaction aborted' when we try to insert huge data into oracle database from the application. We are using Windows Server 2003, service pack 2.
Oracle version - Oracle 10g.
Oracle server is not on the same machine.

Please let us know the cause and the resolution for this problem.

[Shaila24072009]

We have a module in our project, which reads data from XML file and merges the data into the database. This merging at one end happens to a SQL Server 2005/SQL Server 2000 datbase. At the other end it happens to a Oracle database. We have a portal application developed in ASP.NET from where, we merge the data.

When the data to be merged is very huge, we get this message. 'The root transaction wanted to commit, but transaction aborted'. Right now we are getting this message, when we try to merge data on to a oracle database.

But this problem is very intermittent. It happens only when there is huge amount of data to be inserted to one table.

As i have mentioned in my post, we use Windows server 2003 operation system with service pack 2. This error does not come when we do the same operation with service pack 1.

So is it OS dependent?

[debasisdas]

To me that looks like an application issue rather than database issue.

Kindly post the exact oracle error message with error number.

[Shaila24072009]

Here is the complete description of the problem.

We have a module in our project, which reads data from XML file and merges the data into the database. We have a portal application developed in ASP.NET from where, we merge the data.

When the data to be merged is very huge, we get this message. 'The root transaction wanted to commit, but transaction aborted'.

But this problem is very intermittent. It happens only when there is huge amount of data to be inserted to one table.

As i have mentioned in my post, we use Windows server 2003 operation system with service pack 2. This error does not come when we do the same operation with service pack 1.

So is it OS dependent? Also if it was application related problem, then it should throw the same error when running from a different machine.

Following are the error codes:

DMS_CORE_DAL_DBERROR
at Oracle.DataAccess.Client.OracleException.HandleErr orHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQ uery()
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQ uery()
at CoreServices.DAL.DataManager.ExecuteNonQueryProc(D BConnection foConn, String fProcName, DOList foParamDOList)

*** ORA-02291: integrity constraint (ADVTVS.FK_JCARD_JCARD_LAB) violated - parent key not found
ORA-06512: at "ADVTVS.PKG_SYNC_MERGE_TRNS_SERVICE", line 318
ORA-06512: at line 1 ---

Server stack trace:
at CoreServices.Pipeline.TransactionPipeline.Process( IPipelineable& foPipeLineDataObject, PipelineOperation fiPipelineOprn)
at System.Runtime.Remoting.Messaging.Message.Dispatch (Object target, Boolean fExecuteInContext)
at System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)

Exception rethrown at [0]:
at DataSync.MergeData.MergeDataManager.Merge(Int32 fiDealerId)
at Client.DataSync.cmdMerge_Click(Object sender, EventArgs e)



DATASYNC_MERGE
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32 errorCode, IntPtr errorInfo)
at System.EnterpriseServices.Thunk.Callback.DoCallbac k(Object otp, IMessage msg, IntPtr ctx, Boolean fIsAutoDone, MemberInfo mb, Boolean bHasGit)
at System.EnterpriseServices.ServicedComponentProxy.C rossCtxInvoke(IMessage reqMsg)
at System.EnterpriseServices.ServicedComponentProxy.I nvoke(IMessage request)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData& msgData, Int32 type)
at CoreServices.Pipeline.TransactionPipeline.Process( IPipelineable& foPipeLineDataObject, PipelineOperation fiPipelineOprn)
at DataSync.MergeData.MergeDataManager.Merge(Int32 fiDealerId) at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32 errorCode, IntPtr errorInfo)
at System.EnterpriseServices.Thunk.Callback.DoCallbac k(Object otp, IMessage msg, IntPtr ctx, Boolean fIsAutoDone, MemberInfo mb, Boolean bHasGit)
at System.EnterpriseServices.ServicedComponentProxy.C rossCtxInvoke(IMessage reqMsg)
at System.EnterpriseServices.ServicedComponentProxy.I nvoke(IMessage request)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData& msgData, Int32 type)
at CoreServices.Pipeline.TransactionPipeline.Process( IPipelineable& foPipeLineDataObject, PipelineOperation fiPipelineOprn)
at DataSync.MergeData.MergeDataManager.Merge(Int32 fiDealerId)

*** The root transaction wanted to commit, but transaction aborted ---
__________________________________________________ ________________________________________

The ORA codes are ORA-02291 and ORA-06512.

We are not getting any of these errors, when we merge same data from Windows 2003 server SP1. But if we execute it from SP2, we are getting this error.

Are there any hotfixes provided by MS to fix this problem?

[debasisdas]

Cause---A foreign key value has no matching primary key value.You tried to reference a table using a unique or primary key, but the columns that you listed did not match the primary key, or a primary key does not exist for this table.

Action:-------Delete the foreign key or add a matching primary key.