Send e-mail based on criteria from script
-
trying to run something like below to notify me if the space is less than 1GB. However, I get an error message (see below). Also, this is a clustered SQL server.
if @MB_Free < 1024
exec master.dbo.xp_sendmail
@recipients ='me@organization.com',
@subject ='SERVER NAME - Free Space Issue on C Drive',
@message = 'Free space on C Drive
has dropped below 1 gig'
------ERROR Msg---------------------------------
SQL Server blocked access to procedure 'sys.xp_sendmail' of component 'SQL 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 'SQL Mail XPs' by using sp_configure. For more information about enabling 'SQL Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.
-
the error is self describing...go to Start>>Programs>>Microsoft SQL server and find the Surface Area Configuration....scroll down to database mail and enable it.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Once I click on "surface area configuration for features" I get a message that it cannot connect to server localhost, and I'm assuming that is due to this being a clustered environment. I think? I have db mail configured on the one node already...I'm cluster stupid though
-
in small letters, right next to the words "Configure Surface Area for localhost" there is a link that says "change computer"
click it and put in the name and connection info for the cluster.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
That worked! Thank you SO much! 😀
-
yeah configuring the server like that is something you like once a year at the most, and only when you needed a functionality you didn't need at startup;
glad i could help.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
aroatenberry (6/23/2010)
Once I click on "surface area configuration for features" I get a message that it cannot connect to server localhost, and I'm assuming that is due to this being a clustered environment. I think? I have db mail configured on the one node already...I'm cluster stupid thoughBTW, when you configure SQL server in clustering environment, always use SQL server name, which is a virtual name, so your change (most likely related to security since databases are shared) will apply to both nodes.
So you have db mail configured, you should use db mail instead turn on SQL mail.
No Signature
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply