How to display Message/notification while execution a job

Question

How to display Message/notification while execution a job

Mishal

Ten Centuries

Points: 1103
More actions
December 1, 2011 at 11:16 pm

#263923

Hi,
I am executing a job in Sql server 2005,
which is executing 5 Stored Procedures together,
Is there is any way by which i can write any message/notification in log,after completing 1 sp execution.
thanks ...in advance.
if anyone know, Plz reply me ASAP.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Divine Flame SSCoach Points: 15943 More actions · Answer 1

adlakha.22 (12/1/2011)
Hi,
I am executing a job in Sql server 2005,
which is executing 5 Stored Procedures together,
Is there is any way by which i can write any message/notification in log,after completing 1 sp execution.
thanks ...in advance.
if anyone know, Plz reply me ASAP.

1. If the database mail is configured, you can write the code to send a mail to you every time the procedure runs, with its successful or failed status (if failed it can send you the exact error also if you used TRY CATCH block).

2. If you just want to know which procedure got failed or want to control the flow of other procedure's execution, you can separate these stored procedures in 5 separate job steps within the job.

Sujeet Singh

Mishal Ten Centuries Points: 1103 More actions · Answer 2

@flame

I want to know how much time each procedure is taking to execute,

for this i have to make entry in .txt file, to track them..

Divine Flame SSCoach Points: 15943 More actions · Answer 3

adlakha.22 (12/2/2011)
@flame
I want to know how much time each procedure is taking to execute,
for this i have to make entry in .txt file, to track them..

You can track that also by using database mail (sp_send_dbmail). What you can do is simply declare 2 variables say @ProcStartTime and @ProcEndTime in stored procedure.

Now,

within the stored procedure you can put this as the first command to execute

SET @ProcStartTime = GETDATE();

similarly,

put this command to execute as last command,

SET @ProcEndTime = GETDATE()

And, you can send the mail from within the stored procedure to yourself with the body as :

Procedure <ProcName> execution started at : ProcStartTime

Procedure <ProcName> execution ended at : ProcEndTime

Its just a way :-). However, you can write the log of a job to the file also, may be that can also work if you have separated the procedure in separated steps.

Sujeet Singh