Last post Dec 08, 2014 01:56 AM by Michelle Ge - MSFT
Dec 05, 2014 04:27 AM|kaviyarasan|LINK
I have used instead of insert trigger in a table and insert the records using looping. In the trigger, while after insert operation to that table, pass that row to one Stored Procedure (it contains some calculations and update that into another table) that
time any error caused means whole insert operation get failed. this s the functionality.
But i need to insert the records except error occurred rows and error occurred rows information stored into Error Log table. i.e. if i insert 10 records with 3rd & 8th row causing error. I need to insert the rows except 3rd & 8th and that two rows error
information insert into ErrorLog table. so i decided to using Try.....Catch and i'm not familiar with SQL TRY...CATCH. I google it but i cant understood how to use it to my requirement.
Following code is what i tried,
WHILE(@I <= @RowCount)
INSERT INTO Table1(column1,Column2,.....ColumnN) values (@c1,@c2,..@cN)
DECLARE @ErrMsg nvarchar(max)
SELECT ERROR_MESSAGE() as ErrMsg
SELECT @ErrMsg = ERROR_MESSAGE()
INSERT INTO ErrorLog(ErrorProjectID,ErrorMsg,[Created On]) values(@ProjectID,@ErrMsg,GETDATE())
above code executing and i got the error, Msg 3930, Level 16, State 1, Procedure t_InsFeed, Line 206
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Please advice me how to handle this and give me sample code.
Dec 08, 2014 01:56 AM|Michelle Ge - MSFT|LINK
According to your description, I created a demo, please refer to the code below:
First I have a table which names 'TimeTable'.
Second I create a store procedure as below, it caontains a error.
Third, insert the error message into table.
CREATE PROCEDURE usp_MyErrorASSelect ** from TimeTable;GOBegin try
declare @msg nvarchar(max)
insert into TimeTable(WeekID,StutID,StartTime,EndTime,Day,ClassID) values(Error_Number(),100,GETDATE(),GETDATE(),3,'test')
select Error_Message() as Error_Message,ERROR_NUMBER() AS ErrorNumber;
As I did not run the transaction, the error message will be inserted into the table.
For more information about Try Catch, please refer to the link below:
Hope it's useful for you.