Need to find profile name in stored mail profile that already exists
-
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.
-
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?
-
Anyone?
-
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.
-
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.
-
I was able to remove (drop) the trigger which stops them, but it doesn't fix that error above.
-
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?
-
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?
-
Or how do I just completely stop the error without having it notify us anymore. That is fine also.
-
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' messageCheck 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.
-
You are awesome. That resolved it. I decided to update just the profile name and it resolved all the issues.
Thank you!
-
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
-
Will do. Thanks again.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply