Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql 2000 email setup Expand / Collapse
Author
Message
Posted Thursday, November 2, 2006 5:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 17, 2006 3:45 PM
Points: 13, Visits: 1

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?

Post #319862
Posted Thursday, November 2, 2006 5:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697

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  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.



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #319865
Posted Thursday, November 2, 2006 5:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 9, 2006 3:57 AM
Points: 18, Visits: 1
I used this script with a good amount of success.

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=510
Post #319866
Posted Thursday, November 2, 2006 3:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:20 PM
Points: 194, Visits: 334

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




CDBA
Post #320114
Posted Friday, November 3, 2006 3:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 9, 2007 7:17 PM
Points: 15, Visits: 1

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

 

 

Post #320477
Posted Tuesday, November 7, 2006 9:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 17, 2006 3:45 PM
Points: 13, Visits: 1

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.

Post #320966
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse