SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sql 2000 email setup


sql 2000 email setup

Author
Message
Mike Anderson-379324
Mike Anderson-379324
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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?


colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15733 Visits: 715

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/
Chris Dearden-247120
Chris Dearden-247120
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 1
I used this script with a good amount of success.

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=510
CDBA
CDBA
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1062 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

vaddi
vaddi
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 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


Mike Anderson-379324
Mike Anderson-379324
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search