How to Use Database Mail feature in SQL Server 2000

In SQL server 2000 we don't have any option to configure DatabaseB mail, this option was introduced in SQL server 2005.

There are basically three ways to configure Mail to send mail.

1. SQL Mail:
2. Using Linked Server:
3. Using xpsmtp80.dll:

I will focus on 2 and 3 points to set up a mail on SQL server 2000.

Using Linked Server: I assume that the reader knows How to configure DB mail and Linked Server-

Follow below steps to use Database mail feature:



1. Make sure SQL server 2005/SQL server 2008 is added to SMTP exchange relay where you have DB mail feature.

2. Configure DB Mail on SQL server 2005/2008 server.

3. Create one SQL Login 'xyz' on 2005/2008 server and provide DB_owner on MSDB Database.

4. Use the same login created in 3rd step to create linked server on SQL server 2000.

5. Run below query to check if you are getting result using linked server

select * from [Linked server name].msdb.dbo.backupset

6. If you are getting result it means linked server is configured properly.

7. Then run below query to check if you are getting mail from the server or not.

exec Linked server name.msdb.dbo.sp_send_dbmail
 @profile_name = 'Test',
 @recipients='xyz@abc.com',
--@copy_recipients = 'yourgroup@email.com',
 @subject='Job test_db_mail failed on SQL Server 2000 server',
 @importance='HIGH',
 @body ='Job test_db_mail failed on SQL Server 2000 server'

Using xpsmtp80.dll:

1. Make sure SQL server 2000 server to SMTP relay.

2. Download " xpsmtp80.dll" from below link

http://www.sqlservercentral.com/Forums/Attachment5240.aspx

3.Copy "xpsmtp80.dll" to "C:\Program Files\Microsoft SQL Server\MSSQL\Binn" location

4. Register Extended Proc using below query

exec sp_addextendedproc 'xp_smtp_sendmail', 'xpsmtp80.dll'

5. Grant rights to the correct set of users using SQL Query Analyzer by executing:

grant execute on xp_smtp_sendmail to public

6. Use below query to check if you are getting mail from the server.

exec master..xp_smtp_sendmail   

      @TO = 'xyz@abc.com',  

       @from = 

'xyz@abc.com', 

      @subject =  @qstring,
      @message =  @qstring,
      @server = 'SMTP server name'