Looks like all is good now, thanks for all the response.

Based on the feedback above, I did modify some of the logic related to my auto-commit and transactions, thank you for that. However as for my original issue, it was related to some business logic (where I least expected it) with a negative return code preventing the object from updating

Hi, Samir -

Do the two databases reside in the same SQL Server database engine or in separate ones? It is not clear from your post.

If both reside in the same DB engine, are you accessing them in PB with a single Transaction object or do you use a separate Transaction object for each database?

If separate DB engines, are they connected to each other via SQL Server Linked Server?

Does the app encapsulate this "insert into one database and update another database" operation you have described within a database transaction (Begin Transaction/Commit or Rollback) so that SQL Server treats the whole operation as a single transaction?

Hello,

Possibilities to check.

1.  We faced an issue of data loss in one of our clients' place, while updating only one database.    Randomly few data (while invoicing) will gets lost.  It happened only during peak hours, which we could not replicate at any other place.  Finally it was an hardware issue, that by replacing all switches and connectors to higher quality solved that issue.   

2.  Your update code might give us a better picture.    In one of our application we too update two database, with a variation from your scenario.  Data is entered in one single screen, where it will be split into two on a condition while saving.   Few records go to one db and remaining to another db.  This is working fine for the past one decade.  [ Developed using PB 10.5 and SQL Server 2000/2012 ].

2a.  This is the code which is working here 

Call event / function for validation

ib_save    = True                              <- To check db error first db
ib_another = True                              <- To check db error second db

SQLCA.AutoCommit   = False                     <- First database
gt_sales.AutoCommit = False                    <- Second database

If lb_proceed = True Then
   Call event to separate the data into two

   // Check whether the total rows matches	
   If dw_detail.RowCount() <> dw_main_detail.RowCount() + dw_another_detail.RowCount() Then
		MessageBox('Elifa', 'Given Item Are ' + String(dw_detail.RowCount()) + '~r~n Main is  ' +  String(dw_main_detail.RowCount()) + '~r~n Other is ' + String(dw_another_detail.Rowcount()))
		SQLCA.AutoCommit = True
		gt_sales.AutoCommit = True
		Return 0
   End If
	
   TriggerEvent('ue_save_main')             <- Event to update first db
   If ib_save = True Then TriggerEvent('ue_save_another')   <- Event to update second db
   If ib_save = True and ib_another = True Then       <= both update success
      Commit Using gt_sales;                          <- Commit second
      Commit Using SQLCA;                             <- commit first
   Else
      RollBack Using SQLCA;                           <- Roll back first
      RollBack Using gt_sales;                        <- Roll back second
      MessageBox('Elifa', 'Commit Failed')    
   End If	
End If
SQLCA.AutoCommit   = True
gt_sales.AutoCommit = True

HTH

Happiness Always
BKR Sivaprakash