I am using SqlTransaction to BulkCopy data to a table in SqlServer database. There are several stored procedures which are called based on column values in inserted table. If there is any error, I am using Rollback to avoid dirty data in tables. I am logging some data to one table whose whole purpose is to note logs when those stored procedures are called. When I am rolling back transaction I am losing log data too. Can please anyone suggest me how to avoid this problem. Please share any helpful links if available.
If your transaction is coded within the stored procedure, then instead of logging from within the transaction, concatentate the messages (and timestamp) and and log after the rollback.
Another way is to return the concatenated string as error, and then log into a log table as part of handling the SQLException.