Hi,
Thanks for the idea. I manage to get it right, but I'm not sure if what I did will have a drawback.I simply put the two commit statements after the second ExecuteNonQuery.
sqloCnLocal.Open()
TransLocal = sqloCnLocal.BeginTransaction()
cmdLocal.Transaction = TransLocal
cmdLocal.Connection = sqloCnLocal
cmdLocal.CommandText = SqlLocal.ToString() + "xxx"
cmdLocal.CommandTimeout = 0
cmdLocal.ExecuteNonQuery()
TransHQ.Commit()
TransLocal.Commit()
So the idea is not to commit first until the whole execution finished.
Thanks for the great help!