January 22, 2015 at 3:58 pm
We have a previous SQL 2012 cluster that emails us when a new database is added. I am unable to figure out why we get this error any time we add a new database to it, and also it prevents us from adding a new availability group to this cluster because of this error.
I also am unable to figure out what profile it is talking about as this was setup before me and I am not a DBA. I am learning SQL and trying to figure this out. Any assistance would be greatly appreciated.
The error we receive is attached.
January 22, 2015 at 4:23 pm
Basically, how do I remove the msdb.dbo.sp_send_dbmail that was put in there to create a new one or not use it?
January 23, 2015 at 6:06 am
Anyone?
January 23, 2015 at 6:38 am
Do you receive emails from the cluster successfully in other situations? Or check by right-clicking on Database Mail & selecting Send Test Email... and send yourself an email.
If not SQL Agent may be using a profile that's been deleted.
This query will show you valid profiles on the server: select * from msdb.dbo.sysmail_profile
To check SQL Agent, right-click then properties, choose the Alert System tab. The profile being used will be listed on there.
January 23, 2015 at 6:40 am
Yes we are getting emails.
But it's not related to database mail, but more related to this procedure which I am trying to remove.
January 23, 2015 at 6:42 am
I was able to remove (drop) the trigger which stops them, but it doesn't fix that error above.
January 23, 2015 at 6:46 am
Ah, Ok, I was struggling to work out what was generating the error/email!
Is it not just a matter of modifying the trigger to change the @profile_name parameter when calling msdb.dbo.sp_send_dbmail to a valid value then?
January 23, 2015 at 6:50 am
I created an account called SQLAlerts. That account is mapped to MSDB with a role of DatabaseMailUserRole.
I am unsure if that is still the name of the profile because when I submitted it again it still errors out. Database Mail has two profiles. One called SQLAlerts and another just default.
I am unsure what else to do to fix this because I am not familiar with this. So possibly modify it to work with that above. But how?
January 23, 2015 at 7:04 am
Or how do I just completely stop the error without having it notify us anymore. That is fine also.
January 23, 2015 at 7:13 am
No, it'll be in the definition of the trigger, as shown on the link you provided.
Locate the trigger in Object Explorer (under Server Objects\Triggers), right-click on Script as DROP & CREATE to a new window.
You'll then see a part like this:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLAlerts',
@recipients = 'SQLDBAGROUP@YOURDOMAIN.COM',
@body = @results,
@subject = @subjectText,
@exclude_query_output = 1 --Suppress 'Mail Queued' message
Check that the bit @profile_name = 'SQLAlerts' is actually 'SQLAlerts' (or 'default'). If not, change it and execute the drop & create.
Or just delete the trigger if you're sure you don't need it. Might be an idea to save the create script first.
January 23, 2015 at 7:23 am
You are awesome. That resolved it. I decided to update just the profile name and it resolved all the issues. 🙂
Thank you!
January 23, 2015 at 7:35 am
Ha, thanks & you're welcome. If you're new to SQL Server then this site has to be one of the best resources around.
Highly recommend taking a look at the 'Stairways' series on the left.
Cheers
Gaz
January 23, 2015 at 7:38 am
Will do. Thanks again.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply