February 23, 2012 at 9:19 am
anthony.green (2/23/2012)
SQLKnowItAll (2/23/2012)
Sorry... My point there was that the default has been changed, but SQL Agent probably has not been restarted.Forgot about that, always catches me out, something so simple
🙂 Going along with this. Do you know how to check which is the default profile? I can't seem to determine that on my server... Also, google is not helping.
Jared
CE - Microsoft
February 23, 2012 at 9:23 am
Figured it out...
SELECT *
FROM msdb.dbo.sysmail_profile p
JOIN msdb.dbo.sysmail_principalprofile pp
ON p.profile_id = pp.profile_id
WHERE pp.is_default = 1
Jared
CE - Microsoft
February 23, 2012 at 9:30 am
zero rows returned for that select.
February 23, 2012 at 9:40 am
hat are the results if you remove the WHERE clause?
Jared
CE - Microsoft
February 23, 2012 at 9:56 am
zero rows. there are no rows in msdb.dbo.sysmail_principalprofile.
February 23, 2012 at 10:02 am
Sounds like a problem to me... Something was not configured correctly when setting up database mail. What if you go into the wizard and modify a profile all the way through and save it. You don't have to change anything.
Jared
CE - Microsoft
February 23, 2012 at 10:41 am
you don't need a default mail profile so maybe that's why nothing is being returned from principal profile.
February 23, 2012 at 10:47 am
anthony.green (2/23/2012)
you don't need a default mail profile so maybe that's why nothing is being returned from principal profile.
Maybe so, but I have 2 profiles on my server and both are in this table. It is not documented as to exactly when this table gets populated, at least not that I can find. If the OP has more than 1 profile, how does SQL Server Agent know which profile to choose?
Jared
CE - Microsoft
February 23, 2012 at 11:40 am
Using the wizard does not add a row to msdb.dbo.sysmail_principalprofile, either.
February 23, 2012 at 11:52 am
Ok, let me ask you this... Why do you think account X is default versus account Y? How did you figure that out?
Jared
CE - Microsoft
February 23, 2012 at 11:56 am
3 jobs are configured to send notification to account W; 3 configured to send noti to account X; 3 configured to send noti to account Y. when all 9 jobs run the notification for all of them is coming from account X.
February 23, 2012 at 11:58 am
please attach a screenshot of the alert system tab by right clicking on the SQL agent in Ssms so we can see the settings being used to send notifications
February 23, 2012 at 12:10 pm
rocky (2/23/2012)
3 jobs are configured to send notification to account W; 3 configured to send noti to account X; 3 configured to send noti to account Y. when all 9 jobs run the notification for all of them is coming from account X.
Ok, first thing... You still do not understand that sending and receiving are 2 completely unrelated things.
Let me try rewording your above statement:
3 jobs are configured to send notification to OPERATOR W; 3 configured to send noti to OPERATOR X; 3 configured to send noti to OPERATOR Y. when all 9 jobs run the notification for all of them is coming from ACCOUNT Z.
Account Z happens to share the email address with operator X. However, SQL jobs will not send FROM the operator they are sending to. They send FROM the ACCOUNT that is default PROFILE in database mail to an OPERATOR.
Got it now? OPERATOR != PROFILE; all jobs send from same profile.
You have no entries in the table for mail principles because you probably only have 1 mail profile (not operator) and there is no need to "set" a default when there is only 1.
Jared
CE - Microsoft
February 24, 2012 at 1:13 am
SQLKnowItAll (2/23/2012)
Figured it out...
SELECT *FROM msdb.dbo.sysmail_profile p
JOIN msdb.dbo.sysmail_principalprofile pp
ON p.profile_id = pp.profile_id
WHERE pp.is_default = 1
I have 4 accounts and 4 profiles, they all show in profile and principal profiles but only 1 shows from the above query which is what I would expect as only one is default.
Will wait and see what the screen shot of the alert system says
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply