?
Solved

Why does mail sproc get triggered?

Posted on 2010-11-10
6
Medium Priority
?
256 Views
Last Modified: 2012-05-10
In sql server 2005, I'm getting the error below when trying to run a sproc.  The sproc doesn't reference mail and I'm not sure why this happens.  I have the same db on another server that doesn't throw this error.  What might be causing this?

Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0

SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.
0
Comment
Question by:brettr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34106252
http://msdn.microsoft.com/en-us/library/ms190307%28SQL.90%29.aspx

The error message already indicates what you need to so, go to Surface Area Configuration and enable it.

start\Programs\SQL Server 2005\Configuration Tools\Surface Area..
Bottom link "for features"
Pick "database mail" from the left tree, on the right, click tick "Enable.."
0
 

Author Comment

by:brettr
ID: 34106327
Thanks.

What is triggering that sproc?  I don't recall ever integrating it.
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 total points
ID: 34106401
How often does it happen?
You could set up a SQL Profiler trace and capture for where (rpc started/sql batch started/sp stmt started) LIKE '%sp_send_dbmail%' and wait for it to be captured!  That will tell you the application name that caused it to fire.

It is also possible some sql agent job is attempting to send job completion notifications.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Closing Comment

by:brettr
ID: 34106448
Thanks.
0
 

Author Comment

by:brettr
ID: 34106561
Oh no.  I don't see the database mail option for the problem machine.  What does that mean?  I see it on the other machine.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34106630
Okay you can check using (run in query window)

exec sp_configure 'Database Mail XPs'

Result of the "run_value" column is what is currently being used (1=on, 0=off)

If that returns the error

"The configuration option 'Database Mail XPs' does not exist, or it may be an advanced option."

Then use this whole block below:

exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'Database Mail XPs'
exec sp_configure 'show advanced options', 0   -- reset the advanced options setting
reconfigure
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question