July 1, 2015 at 7:56 pm
I have created a log table to enter error information where a stored proc is failed, custom message and exact error. but it is not logging due to rollback transaction. If i remove transaction it works. I dont want to remove transaction is there any way to commit records to log table
Stored proc syntax as below
begin try
begin tran
begin
begin try
insert tbl1
begin catch
exec dbo.insertlogtable @message,@error,@state
end catch
end
begin
begin try
insert tbl2
begin catch
exec dbo.insertlogtable @message,@error,@state
end catch
end
commit tran
end try
begin catch
exec dbo.insertlogtable @message,@error,@state
end catch
July 2, 2015 at 2:02 am
Please see if below given code helps in understanding what changes you might have to do to make it work
CREATE TABLE #LOG
(
ERROR VARCHAR(8000)
)
CREATE TABLE #TEST
(
ID SMALLINT
)
BEGIN TRY
BEGIN TRAN
INSERT INTO #TEST
SELECT 1000000
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
INSERT INTO #LOG (ERROR)
SELECT ERROR_MESSAGE()
END CATCH
SELECT * FROM #TEST
SELECT * FROM #LOG
DROP TABLE #LOG
DROP TABLE #TEST
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply