calling a stored procedure inside job and to schedule it one time

Question

Hi Everyone,

i have table LOG_AUDIT with columns (ID,TABLE_NAME,ACTION,KP_VARCHAR,KP_NUMBER,TSTAMP),id is an Identity column (1,1)

i have created a stored procedure :

CREATE PROCEDURE sp_logetl_ins
AS
insert into LOG_AUDIT(TABLE_NAME,ACTION,KP_VARCHAR,KP_NUMBER,TSTAMP) values('ABC','INSERT',NEWID(),123,GETDATE())
GO 

i want to call this stored procedure inside JOB, for this i have created a new job,

in STEP:

type:T-SQL

command:exec sp_logetl_ins

in Schedule:

Schedule Type:one time , i set appropriate time,

my requirement is if the is inserted then i want a mail i.e., sucess mail, if it is any failure  failure message i.e, system generated message either it may be due to Constraint failure or any other reason

i can make use of ALERT but how i can proceed for Failure Message.

please advice me.

---

karteek_miryala

Answer 1

If I understand you correctly, you can configure a job step to issue a success or failure message.

1. On the job definition, Notification option, be sure that the email is set to send to your selected destination.  If this is not set, then set it.
2. If there is no Operator defined on the Notification, then the email will not get sent.  Create an Operator and then change the job.
   Create an Operator: http://technet.microsoft.com/en-us/library/ms175962.aspx
   
3. On the job step, under the Advanced option, you can set what you want to happen with a success or a failure on the job step.
4. Also, on the job step you can configure "Log to table".  Using this will let you see some of what happened inside the job step.  After a failure, you can come back to the Advanced Option and click on View to see what was captured.

If that was not your question, please feel free to give more details.

RLF

Answer 2

Hi Russell, Thanks for ur quick responce,

my requirement is  my procedure

CREATE PROCEDURE sp_logetl_ins
AS
insert into LOG_AUDIT(TABLE_NAME,ACTION,KP_VARCHAR,KP_NUMBER,TSTAMP) values('ABC','INSERT',NEWID(),123,GETDATE())
GO 

1)by using sqlserver agent job: when i start this job it will enter one record into LOG_AUDIT table. then if the record is inserted sucess  mail should come with count.

if  any failure it should send exact meassage as a mail.

2) for testing purpose i changed my Procedure to

CREATE  PROCEDURE sp_logetl_ins
AS
insert into LOG_AUDIT(TABLE_NAME,ACTION,PK_VARCHAR,PK_NUMBER) values('ABC','INSERT',NEWID(),123);
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ''' + name + ''', 
    @recipients = 'k***.m***@***.com',
    @query = 'SELECT COUNT(*) FROM  [LOG_AUDIT]' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

according to :http://msdn.microsoft.com/en-us/library/ms190307.aspx

 i.e.,

B. Sending an e-mail message with the results of a query

when i am executing this procedure:

exec sp_logetl_ins

message:

(1 row(s) affected)

Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42

profile name is not valid

any how i want a procedure that  should be call through job it must and should send an email

if sucess count should be send i.e., SELECT COUNT(*) FROM  [LOG_AUDIT]

else failure message : with appropriate message i.e, system generated message.

i used alert and Notification but i am unable to do this, i have  an appropriate operator , but it is not working.

plese advice me  or any  good example in detail.

---

karteek_miryala