@profile_name - Maintenance Plan - Database Mail

Question

@profile_name - Maintenance Plan - Database Mail

mattyk6

Ten Centuries

Points: 1152
More actions
March 16, 2006 at 1:48 pm

#111451

Ok, I have setup Database Mail and I can send a Test Email. I have all of that working.
However, when I try to send a notification on a maintenance plan, I get the following error:
Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter.
Where do you set the @profile_name parameter?
Thanks,
Matt

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

IanR SSC Eights! Points: 998 More actions · Answer 1

Are you using the 64bit version of SQL 2005? For some reason MS did not finish the job from what I've read on Database Mail in the 64 bit version. THey recommend using the legacy SQL Mail using outlook express.

go to this link and search the page for:

"SQL Server Agent Cannot Send Job Status Notifications and Alert Notifications via Database Mail on 64-Bit SQL Server".

http://support.microsoft.com/?kbid=910228

"SQL Server Agent is not integrated with Database Mail in the 64-bit edition of SQL Server."

New to the DBA world...thank you for your help!,

IanR

mattyk6 Ten Centuries Points: 1152 More actions · Answer 2

I was using the 32-bit version.

I did figure out my problem. When you Manage Profile Security (righ-click Database Mail and choose Configure Database Mail), you can set a profile to be the default profile. However, unless you also make that profile public, you can't make it the default profile. This doesn't make a whole lot of sense to me. But at least I got it working.

Matt

IanR SSC Eights! Points: 998 More actions · Answer 3

Excellant

New to the DBA world...thank you for your help!,

IanR

Jim Dillon-291542 Hall of Fame Points: 3344 More actions · Answer 4

I'm running 64 bit SQL Server 2005 and my Database Mail works fine.

I found configuration to be pretty straight forward.

The only problem I ran into was I didn't know you had to restart the SQL Agent after Database Mail has been configured. Once I did that, no problems!

I get an email once every 15 minutes from my transaction log backup jobs.

mattyk6 Ten Centuries Points: 1152 More actions · Answer 5

I didn't restart the SQL Agent after configuring Database Mail with the 32-bit version. Maybe that only affects the 64-bit version.

What threw me, was that after I set the Default flag to Yes for the Profile and clicked Next, it didn't take and I didn't realize that for some time.

IanR SSC Eights! Points: 998 More actions · Answer 6

You're kidding? Thats awesome! I've been thinking of revisiting the issue and trying to figure it out myself but this is good to hear....I had a tough time with it and when I saw Microsoft themselves telling me it was not working 'yet' in the 64bit version...well....I just gave up trying. Thank you for the information Jim.

New to the DBA world...thank you for your help!,

IanR

IanR SSC Eights! Points: 998 More actions · Answer 7

Not sure how you did it Jim...but still doesnt work for us. 64bit Database Mail still fails to initialize and sessions wont start for us. Even after a restart of the Agent service. I'll post a new thread for this issue.

New to the DBA world...thank you for your help!,

IanR

DonWolfi SSC Journeyman Points: 77 More actions · Answer 8

mattyk6 (3/17/2006)
I didn't restart the SQL Agent after configuring Database Mail with the 32-bit version. Maybe that only affects the 64-bit version.
What threw me, was that after I set the Default flag to Yes for the Profile and clicked Next, it didn't take and I didn't realize that for some time.

WOW! txs for the above! I had exactly the same and did not think of checking it untill you did. Coupled with the fact that I was using an email address only meant to collect alerts, ie without proper login credentials, no wonder it never worked !

I have now inserted my own email credentials for testing purposes, set the default public profile to yes, and miracle, it does work !

Txs to everyone

jmcguire SSC Journeyman Points: 87 More actions · Answer 9

Thanks Jim! I thought I had everything configured correctly, but I still couldn't get the database mail to work.

Your solution to restart SQL Agent solved my issue.

Jason

Leo Nosovsky SSChasing Mays Points: 633 More actions · Answer 10

I had no problems sending emails from a 64-bit SQL 2005 using Database Mail. I had to make the profile I was using Public and Default though (it didn't require any service restarts) to make the Notify Operator Task work. To be more flexible, I replaced this task with an Execute TSQL one, where I execute sp_notify_operator with all necessary parameters, including @profile_name and @subject. This way, I didn't have to make the profile Public and Default. Just like to be in control 😎

lifoet SSC Journeyman Points: 91 More actions · Answer 11

it worked for me, too!

I used Brad's Sure Guide to SQL Server Maintanance Plan and to learn stuff, but somehow this little detailed escaped me. The book is excelent for newbies like me in SQL management. Any developer should know the stuff mentioned there.

Many thanks for this little tip.

djackson 22568 SSChampion Points: 11733 More actions · Answer 12

Jim Dillon-291542 (3/17/2006)
I'm running 64 bit SQL Server 2005 and my Database Mail works fine.
I found configuration to be pretty straight forward.
The only problem I ran into was I didn't know you had to restart the SQL Agent after Database Mail has been configured. Once I did that, no problems!
I get an email once every 15 minutes from my transaction log backup jobs.

Uggh! Obviously the choice is yours, but I think this is a horrible idea. I have my systems send me a success email on fulls, a failure if they fail, and a failure for logs if they fail. I don't need to get an email from every system every 15 minutes. I verify I get something from every system once a week, and as long as that continues, I know I get failures when they occur. Yes, I have tested this.

Since I manage about 35 SQL Server systems (separate instances on dedicated servers) and I already get about 300 emails a day between alerts and other emails, adding 35*4*24=3360 emails a day would be ridiculous!

Dave