Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 462
  • Last Modified:

User SQL database mail to send warning

Occassionally we have a service on a SQL stop; I have database mail working, at least I can send a test message to myself. Is there some way to use it to send an email when a server does not start after reboot or stops during the day?
0
LarryDAH
Asked:
LarryDAH
  • 3
  • 3
  • 2
  • +1
1 Solution
 
dba2dbaCommented:
Run this as a DOS Script. Schedule this as reuired or add as a startup script if needed.

------------------------------------------------------
@ECHO OFF
IF EXIST (SC query IISAdmin | FIND "STATE" | FIND "RUNNING")
sqlcmd
'
EXEC msdb.dbo.sp_send_dbmail ................

'
----------------------------------------------------------
Replace IISAdmin with the service name of interest. (Please note the actual service name in the properties and not go by name of service in services console)

Write appropriate dbmail command to send a message to you.

Thanks,
0
 
LarryDAHAuthor Commented:
The server I am interested in for th SQL server, so I changed IISAdmin to MSSQLServer. Using info at http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/ I created a SQL query that sends and email to me when executed, but how would I tie it all together. If the SQL service stops under Recovery you can restart the service, restart the server, etc or execute a program. How would I get it to run my dbmail query?
0
 
lundnakCommented:
Look at this link.  It describes out to execute a procedure on SQL Server startup.

http://msdn.microsoft.com/en-us/library/ms181720.aspx
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Anthony PerkinsCommented:
>>How would I get it to run my dbmail query?<<
If there is no SQL Service then you cannot using database mail.
0
 
dba2dbaCommented:
In this case, you can use some windows mailers.... BMAIL is one of them.

http://www.beyondlogic.org/solutions/cmdlinemail/cmdlinemail.htm

Thanks,

0
 
Anthony PerkinsCommented:
The part you are overlooking is in bold:
"How would I get it to run my dbmail query?"
No application or utility can be used to retrieve a query if there is no SQL Server service. Period.
0
 
LarryDAHAuthor Commented:
That makes sense, if the SQL service is stopped it will not send a query off to tell us that. WHat about using an Event ID trigger. How can I figure out what event id a stopped SQL service would be?
0
 
Anthony PerkinsCommented:
I am afraid I have no idea.
0
 
LarryDAHAuthor Commented:
I found a network monitor software package at activexperts.com that can do what I need, plus monitor a lot more than that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now