How can I insert the error message into the database

I am using the following sql statment in the OnStart and OnFinish and OnError in the job Notification and Actions

INSERT INTO JobLog (SID, JobNumber, JobName, RunDate, JobMessage) VALUES ('200', @V"job_id", '@V"job_name"',GetDate(),'@V"event"')

This works perfectly recording START, FINISH and ERROR in the JobMessage in a SQL Express database

However, when there is an ERROR, I need to display the error message that appears in the log to be inserted. I have reviewed the documentation and macros and I can't find one that represents the error message.

I would appreciate any help you can offer in getting the error message to be inserted as well.

I'm afraid in the notification event you cannot obtain the error message of the error caught by the job. Two common solutions are used to deal with that

1. Insert a generic error message like "An error occurred in job @V"job_name" see job log for details."

2. Don't insert each event in a real time. Instead schedule a job running every 10-15 minutes to load schedule.log into a staging table and then from that table copy only new records to the log table, a simple INSERT..SELECT SQL query with a subquery to find max event time can be used for such purpose.

Note that schedule.log is a tab separated file and can be easily loaded into a SQL Server database using BCP utility, or event build in DatabaseImport method available in 24x7

By the way, if you run a lot of jobs, inserting messages in a real time may make you system unstable. Virtually all database drivers are known to loose some system resources during frequent connects/disconnects.