Solved

User SQL database mail to send warning

Posted on 2010-09-03
9
435 Views
Last Modified: 2012-05-10
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
Comment
Question by:LarryDAH
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 8

Expert Comment

by:dba2dba
ID: 33599087
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
 

Author Comment

by:LarryDAH
ID: 33599652
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
 
LVL 7

Expert Comment

by:lundnak
ID: 33614350
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33614610
>>How would I get it to run my dbmail query?<<
If there is no SQL Service then you cannot using database mail.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 8

Expert Comment

by:dba2dba
ID: 33617210
In this case, you can use some windows mailers.... BMAIL is one of them.

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

Thanks,

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33618124
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
 

Author Comment

by:LarryDAH
ID: 33618382
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33622575
I am afraid I have no idea.
0
 

Accepted Solution

by:
LarryDAH earned 0 total points
ID: 33728252
I found a network monitor software package at activexperts.com that can do what I need, plus monitor a lot more than that.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 34 80
why does some in the xx field return as null?  Case statement 2 21
Get Duration of last Status Update 4 29
datetime in sql 6 23
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

919 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

20 Experts available now in Live!

Get 1:1 Help Now