We have serveral servers, both SQL Servers and others. Since SQL Server, especially 2005 and newer, do email so well I have two of my SQL Servers, so if one is down the other still functions, read a list of servers from a table and ping them. If they don't respond I read from a list of people from a table and email them.

-- Pings servers listed in a table, if no contact emails people

Declare @ServerName as nchar(20), @EmailPerson nchar(40), @result int,

@CmdTxt varchar(100), @Comment varchar(1000)

DECLARE Servers CURSOR FOR

SELECT ServerName

FROM dba..ServersToPing

ORDER BY ServerName

OPEN Servers

FETCH NEXT FROM Servers

into @ServerName

WHILE @@FETCH_STATUS = 0

BEGIN

set @CmdTxt = 'ping ' + ltrim(rtrim(@ServerName))

EXEC @result = xp_cmdshell @CmdTxt, no_output

IF (@result = 1)

begin

Set @Comment = 'Server ' + char(34) + ltrim(rtrim(@ServerName)) + char(34) +

' - Contact failure, possibly not running'

Print @Comment

declare EmailList cursor for

select EmailAddress from dba..EmailAddresses

open EmailList

fetch next from EmailList

into @EmailPerson

WHILE @@FETCH_STATUS = 0

BEGIN

-- Be sure and make sure the appropriate profile_name is used for

-- server this code is being run on.

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'in-mslc-sql3 mail',

@recipients = @EmailPerson,

@body = @Comment,

@subject = @Comment

fetch next from EmailList

into @EmailPerson

end

CLOSE EmailList

DEALLOCATE EmailList

end

FETCH NEXT FROM Servers

into @ServerName

END

CLOSE Servers

DEALLOCATE Servers

GO