We are all talking about the latest versions and features available in SQL Server but most of us are still working on older, SQL Server 2000 servers. It might take a few more years before all of the existing SQL Server 2000 applications are upgraded to newer versions of SQL Server or to other database products. This means we are still stuck with using the old, outdated features available in SQL Server 2000.

One such feature is SQL Mail. I thought that in order to configure email notifications it would be better and easier to use database mail (introduced in SQL Server 2005) rather than SQL Mail where I can. In this article, I will run you through an easier way of using database mail in SQL Server 2000 itself. That is, in your SQL Server 2000, you would be in a position to use database mail instead of SQL Mail for sending job failure notification. This is my procedure:

1) As a first step, you need to configure database mail on your SQL Server 2005 instance if you have not already configured. I haven't covered this in this article, but you can find more information from numerous articles on the internet. You could also go to SQLServer Books Online and read about the wizard. If you are new to SQL Server, don't panic, this takes around 5 to10 minutes to configure.

2) Once database mail is configured or if it is already configured in your SQL Server 2005 server, create a login 'test' on this 2005 instance and make sure you make this login a member of the db_owner role in the msdb database.

3) Once this is done, go to your SQL Server 2000 instance with Query Analyzer and run the sample script below. This will create a linked server on your SQL Server 2000 instance that will help you connect to your SQL Server 2005 instance where you have configured database mail

Please enter the appropriate details relevant to your SQL Server 2005 in the @server and @datasrc parameters.

EXEC sp_addlinkedserver 
@server='SQL_Server_2005 ', 
 @srvproduct='',
 @provider='SQLOLEDB', 
@datasrc='SQL Server 2005 instance name'

4) Now Go to SQL Server 2000 instance-> security-> Linked Servers and you will see the new linked server you created. Right click on this new linked server and go to the properties->security tab

Please enter credentials as shown in screenshot, refer to step (2) above where you created a login in SQL Server 2005 (please use the same login details).

And on 'Server Options' tab, we have to enable RPC Out

5) Go to your SQL Server 2000 server and create a test job. Just make sure that this job will fail (eg. Include script for taking backup of non-existent database on your server) and add a job failure notification step on failure of first step. A sample script of the job failure notification step below

exec SQL_Server_2005.msdb.dbo.sp_send_dbmail 
 @profile_name = 'SampleSQLServer2005dbmailProfile', 
 @recipients='yourname@email.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'

We are referencing the linked server 'SQL_Server_2005' (please refer step 3) that we created on SQL Server 2000. Please enter the appropriate details in @profile_name (this is the database profile you configured in SQL Server 2005) and @recipients parameters. Also, please refer to the screenshot below for your reference.

6) Execute this test job on SQL Server 2000 and in a few minutes you will get a notification to your email address

7) If you did not get the email notification, just make sure that the SQL Server 2005 instance where you are connecting to using linked server is running fine. You could try some test queries from SQL Server 2000 Query analyser to retrieve data from SQL Server 2005.

Example: Select * from openquery(SQL Server 2005 linked server name ,'select * from msdb.dbo.sysjobs')

If the linked server set up is fine, above query should return results.

Conclusion

I always found configuring email notifications a pain in SQL Server 2000. I am sure many of you would have faced difficulties doing the same on SQL Server 2000. I hope by using above method, you don't have to tweak errors you may face while using SQL mail, instead you could start utilizing database mail of SQL Server 2005 and above. Good luck.