Configuring Email in SQL Server 2000

A couple of days back I was asked to configured database email for a DB Server which was hosted on a SQL Server 2000 environment. Configuring a database email in SQL Server 2000 is somewhat different than the SQL Server 2005 environment as there is no GUI available in SQL Server 2000.

The following steps should be followed for creating a Database email in SQL Server 2000.

We need to setup the xp_smtp_sendmail which is just an extended stored procedure which will be present in the master database.

To setup the xp_smtp_sendmail:

  1. For SQL Server 2000, download XPSMTP80.DLL file.
  2. Copy xpsmtpXX.dll into the SQL Server BINN directory. For SQL Server 2000 copy XPSMTP80.DLL at the default location "C:\Program Files\Microsoft SQL Server\MSSQL\Binn"
  3.  Register the extended stored procedure xp_smtp_sendmail using SQL Query Analyzer by executing the below query against the master database:
    exec sp_addextendedproc 'xp_smtp_sendmail','xpsmtp80.dll'
  4. Grant rights to the users using SQL Query Analyzer by executing the below query against the master database:
     grant execute on xp_smtp_sendmail  to public
    By default only the member of the sysadmin role have execution rights on the extended stored procedures.
  5. Once the first 4 steps are done, execute the below code to send the email:
    exec master.dbo.xp_smtp_sendmail
    @from = N'a@a.com',
    @to = N'a@a.com',
    @cc='a@a.com',
    @subject = N'The subject which you want to give',
    @message = N'Hi Team
    @type = N'html',
    @server = N'Relay Server IP Address'
    
]]>

Leave a comment

Your email address will not be published.