sql 2000 email setup
-
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?
-
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.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I used this script with a good amount of success.
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=510
-
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
-
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
-
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply