SQL Server Agent Mail Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL Server Agent Mail Problem

Author

Topic

sathyalan
Starting Member

5 Posts

Posted - 2006-07-31 : 10:46:26

hi,

i am working as a sql dba.i am managing more than 20 remote sqlservers from my sqlserver enterprise manager console. i have registered those servers with sa account. According to our company security policy there is no email software on those servers.and all the sql services are running under system account. now i want to be notified via a email if any jobs are failed on those servers.pls solve my problem. thanks in advance.

Regards,
thaya

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-31 : 10:53:13

You can run an app on your desktop which connects to all the servers and emails you if anything fails.
You could also install MOM which will do this sort of thing but is probably overkill.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-07-31 : 17:47:56

There are numerous ways of sending email from a sql server and allthough mine might not be the most elegant it has actually served me quite good for some time so I'll give it to you. It involves a free COM object from www.aspemail.com that you need to download and install on each server and the procedure below. Given that you have access to the servers and is able to install stuff on them and that you have access to an smtp server somewhere this should be plug'n'play:

CREATE PROCEDURE sp_SMTPMail
  @ToAddress varchar(100),
  @FromAddress varchar(100),
  @Subject varchar(200),
  @Body varchar(8000)
AS 
SET NOCOUNT ON
DECLARE 
  @object int,
  @hr int,
  @property varchar(255),
  @return varchar(255),
  @src varchar(255), 
  @desc varchar(255),
  @Mailserver varchar(100)

SET @Mailserver = 'smtp.mymailserver.com'

-- First, create the object.
EXEC @hr = sp_OACreate 'Persits.MailSender', @object OUT

IF @hr <> 0
  BEGIN
    -- Report any errors
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    GOTO END_ROUTINE
  END
ELSE
  -- An object is successfully created.
  BEGIN
    -- Set properties
    EXEC @hr = sp_OASetProperty @object, 'Host', @Mailserver
    IF @hr <> 0 GOTO CLEANUP

    EXEC @hr = sp_OASetProperty @object, 'From', @FromAddress
    IF @hr <> 0 GOTO CLEANUP

    EXEC @hr = sp_OASetProperty @object, 'Subject', @Subject
    IF @hr <> 0 GOTO CLEANUP

    EXEC @hr = sp_OASetProperty @object, 'Body', @Body
    IF @hr <> 0 GOTO CLEANUP

    EXEC @hr = sp_OAMethod @object, 'AddAddress', NULL, @ToAddress
    IF @hr <> 0 GOTO CLEANUP

    EXEC @hr = sp_OAMethod @object, 'Send', NULL
    GOTO CLEANUP
  END

CLEANUP:
-- Check whether an error occurred.
IF @hr <> 0
  BEGIN
    -- Report the error.
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
  END

-- Destroy the object.
BEGIN
  EXEC @hr = sp_OADestroy @object
  -- Check if an error occurred.
  IF @hr <> 0 
    BEGIN
      -- Report the error.
      EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
      SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    END
END

END_ROUTINE:
RETURN

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources