Quote:
Both sites (Quality and Production) are on same Server (Server A) and database also on same server (Server B)
First off, that worries me: development should never have any access to production servers, and especially not production DB servers.
You should always develop and test against a separate dev DB server (often SQL Server Express) as a tiny error can easily damage or destroy the production DB.
Your ordering is wrong: you cannot commit the transaction after you close the connection, and that will probably cause the problem you have noticed.
Instead, make the connection outside (with a
using
block) so it the tha last thing to be terminated, and commit as the last thing you do inside it. That way, any failure in other code will always close your transaction - aborting all updates - before the connection is closed.