• You can log the error to the SQL Server system log by specifying "WITH LOG" on your call to RAISERROR. These log entries survive the transaction rollback:

    BEGIN TRAN

    BEGIN TRY

    RAISERROR('Sample Error for Testing', 16, 1) WITH LOG

    COMMIT TRAN

    END TRY

    BEGIN CATCH

    PRINT 'Error message was logged to the SQL Server log even though the transaction has been rolled back.'

    ROLLBACK TRAN

    END CATCH

    (See the log in SSMS under Management / SQL Server Logs / Current)

    This doesn't help you log to a normal table...but it may be cleaner than writing to a flat file from xp_cmdshell.