0

We have a email notification going via our SQL Server however I cannot find any job or setting associated with it. Its definitely the correct server and I am logged in as admin. The email is sent via the email operator as setup on the associated server. The email is definitely sent from the SQL server in question as when I query the msdb in the sysemail_mailitems the email the recipients received definitely exists as shown in the screenshot below. All we want to do is change the recipients for the notification but only if we knew where....

enter image description here

1

It is probably either coming from a job or within a stored procedure somewhere. If it's a stored procedure, then after you find it, use the job search from below to look for the job calling that sp.


---- Search Job Steps
SELECT 
J.name AS JobName,
JS.step_name AS StepName,
JS.step_id AS StepNumber,
JS.command

FROM 
msdb.dbo.sysjobs J
INNER JOIN msdb.dbo.sysjobsteps JS
ON J.job_id = JS.job_id

WHERE
JS.command LIKE '%XXXXXX%'


---- Search Stored Procs, might want to loop through all databases 

USE XXXXX


SELECT 
DB_NAME() AS DatabaseName,
SCHEMA_NAME(O.schema_id) AS SchemaName
O.name AS SPName,
MO.definition AS SPDefinition

FROM
sys.sql_modules MO
INNER JOIN sys.objects O
ON MO.object_id = O.object_id

WHERE
MO.definition LIKE '%XXXXXX%'

Replace the WHERE clause with a short bit from the email itself that you have confidence would be in the body of the definition. It's not obvious from your post if some of that content in the email is dynamic or the same every time.

| improve this answer | |
  • Thank you the first query gave it away. I inherited this system from someone else and they have stored procedures all over the place. The job called on a stored procedure which had the code for the email notifications. Thanks a lot. – domdew Oct 15 at 14:14
0

In addition to BCM's answer:

The subject of the mail is an indicator if it comes from a job. Just compare it to an email that you know comes from a scheduled job.

Also, if it isn't Agent, then it it can be a module (stored procedure, trigger).

But it can also be SQL (sp_send_dbmail) submitted from a client. In that case, you would have to try to capture it through a trace. OTOH, since it is initiated outside SQL Server, you would have to change the recipient outside SQL Server as well.

| improve this answer | |
  • Thanks for the tip. – domdew Oct 15 at 14:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.