Solved

Why does mail sproc get triggered?

Posted on 2010-11-10
6
242 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
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PERFORMANCE OF SQL QUERY 13 71
How to place a condition in a filter criteria in t-sql? 12 68
INSERT DATE FROM STRING COLUMN 18 56
Query group by data in SQL Server - cursor? 3 28
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard 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.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

816 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now