sql 2000 email setup

  • Mike Anderson-379324

    SSC Veteran

    Points: 219

    I know you guys have seen this post a million times but I'm new to sql. I would like to ask whats the best why to get sql 2000 to send out email notifications I want to set some up for backup jobs. I've heard that its hard to set up with sql 2000 I don't want to run outlook on the server either because that would require for someone to stay logged into the servers and that a no go here. Also are there any third party products out there that can help you run email off of sql server 2000?

  • colin.Leversuch-Roberts

    SSC Guru

    Points: 52551

    This is well documented on ms website. You certainly don't have to be logged in to use an outlook client, whoever told you this is talking rubbish. There are many smtp mail procs available, including from microsoft, which will allow sending of mail - assuming port 25 isn't blocked. Some require IIS installed , some don't. ( I'm sure there will such on this site too )

    To use outlook your ( sql and/or agent&nbsp service account needs to be a domain user with a mail account. Log onto the box as the service account, install the outlook client and set the profile - link in sql server - absolute doddle!

    Using other mail may be more tricky - 2005 supports smtp directly.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • Chris Dearden-247120

    SSC Enthusiast

    Points: 100

    I used this script with a good amount of success.

    http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=510

  • CDBA

    Hall of Fame

    Points: 3266

    Mike,

    There are two main methods:

    1. Use MAPI --> For this install a MAPI client [eg. MS Outlook]. It does not require any one to be logged in to the box

    2. Use SMPT --> Use stored proc/script and fire the mail. U need to have acess to a SMTP server in this case.

    I personally suggest MAPI as u can configure some good notifications easily then.

    Regards

    Utsab Chattopadhyay

  • vaddi

    SSC Enthusiast

    Points: 163

    For the sql server mail the Xp_smtp_sendmail has been used. So first that dll should be downloaded.

     

    To setup the xp_smtp_sendmail:

    1)     For SQL Server 2000, download XPSMTP80.ZIP and unzip the files

    2)     Copy xpsmtpXX.dll into the SQL Server BINN directory.For SQL Server 2000 copy XPSMTP80.DLL.

         For SQL Server 2000 the default location is "C:\Program Files\Microsoft   

         SQL Server\MSSQL\Binn"

    3) Register the extended stored procedure using OSQL or SQL Query

        Analyzer by executing:

         exec sp_addextendedproc 'xp_smtp_sendmail',   

            'xpsmtp80.dll'

    4)     Grant rights to the correct set of users using OSQL or SQL Query

         Analyzer by executing:

         grant execute on xp_smtp_sendmail to public

        By default only the member of the sysadmin role have execution rights    

             on the XP after it is being registered

     

     A test case of how you can use this :  

                             Script to Check the Offline Databases

     

    1)     This Script is to check the status of the Databases on the servers and send a Mail to the DBADMIN when any of the Database goes OFFLINE.

    2)     The script can be run as a Sql Server Agent Job on the Server and can be scheduled according to the requirements to run every one hour or every minute.

     

    The Script is :

     

    SET NOCOUNT ON

     

    DECLARE     @Msg VARCHAR(8000)

     

    SELECT        @Msg = ISNULL(@Msg + CHAR(13), '') + 'Database ' + z.Name + ' on machine ' + z.ServerName + ' is ' + z.Status + ' at ' + z.Now + '.'

    FROM (

                            SELECT                    TOP 100 PERCENT @@SERVERNAME ServerName,

                                                    Name,

                                                    CONVERT(VARCHAR, DATABASEPROPERTYEX(Name, 'Status')) Status,

                                                    CONVERT(VARCHAR, GETDATE(), 109) Now

                            FROM             master..sysdatabases

                            WHERE                     status & 512 = 512

                            ORDER BY    Name

                ) z

     

    PRINT @Msg

     

    IF @Msg IS NOT NULL

                EXEC master.dbo.xp_smtp_sendmail

                @FROM = N'address',

                @TO = N ' address',

                @server = N'smtp.depaul.edu',

                @subject = N'Status of the Database on Testsqlserver!',

                @type = N'text/html',

                @message = @Msg

     

     

  • Mike Anderson-379324

    SSC Veteran

    Points: 219

    Ok how am I supposed to use this script I just want to send email if a database or log backup fails. You know this email thing is so much easier in 2005.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply