0

I have a SQL Job where I want to send an email alert if job fails. I know that for this functionalty I have to Configure Database Mail and than Adjust the properties within the SQL Server Agent. This article gives pretty good detail http://www.sherweb.com/blog/how-to-set-up-database-mail-for-sql-server-job-failures/.

What I want is, instead of setting up an email address to particular user like person1@test.com, I want to send an email to current user. This way who ever logged in to database and runs the job receives the email on job failure.

As I see, there are two benefits of this approach, i. One particular user won't get flooded with emails. ii. Who ever is running the job can know that job has failed.

For example, If I set person1@test.com and person2 logs in than on job failure person1 will receive an email. I don't want that. Is there any way that I can do this in SQL Server Agent Job settings. If no than how can I achieve this?

Like SELECT SYSTEM_USER or SELECT SUSER_NAME() gives me current user and use that as outgoing email address.

GThree
  • 2,708
  • 7
  • 34
  • 67
  • does this mean ,jobs are not automated as per schedule and only user can run job manually? – TheGameiswar Mar 02 '16 at 16:09
  • Further can you clarify what do you mean by user logged – TheGameiswar Mar 02 '16 at 16:11
  • @TheGameiswar Job is automated and runs on every half an hour. For example, if I add my name as email recipient in job and tomorrow 5 different people sets the request than I will receive an email. I want other 5 people to receive the email. Make sense? – GThree Mar 02 '16 at 16:20
  • What do you mean by set the requests? – TheGameiswar Mar 02 '16 at 16:21
  • @TheGameiswar User can set `Daily` or `Weekly` option to fire certaion query from UI. I store the start date, start time and desired time to execute in table. I've a `stored procedure` which checks the condition and picks the records from that `table` if it matches the condition. `SQL job` will execute this `stored procedure` on every half an hour. If job fails than send email to person. – GThree Mar 02 '16 at 16:35

1 Answers1

0

I think you are confused, if one user will start the job manually then you use a STORE PROCEDURE and there you can get the connected user information.

But pgAgent will run the process automatic and run using the USER you setup on the job.

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118