Solved

Why does mail sproc get triggered?

Posted on 2010-11-10
6
248 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 500 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Impove long SQL Stored Procedure Performance 14 79
Following an example - removing duplicate strings 4 67
Problem with SqlConnection 4 184
Replication failure 1 21
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

756 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