How to set up SQL Server 2000's SQL Mail to use SMTP/POP
instead of Exchange Server
First, here's a link to a Microsoft Support article that provides some good
background information:
http://support.microsoft.com/default.aspx?scid=kb;en-us;897349
Before you configure SQL Mail, you'll need to locate a copy of Outlook 2000 (not
Outlook Express) and install it on your SQL Server. Newer versions of Outlook
won't work as they use a different method of processing messages that is not
compatible with SQL Server.
Install Outlook using the domain account under which the SQL Server and SQL
Server Agent services will run and specify the "Corporate or Workgroup"
installation. This is important as Workgroup mode includes additional
processing functionality that is necessary for sending messages from SQL Mail.
Verify that there are copies of the MAPI32.dll in both the C:\windows\system32
directory and the c:\program files\common files\system\MSMAPI\NT directory. The
timestamp on the files should be 1/26/1999 or older.
Once Outlook 2000 has been installed, download and install service pack 3 for
Outlook 2000 (available from Microsoft at
http://support.microsoft.com/kb/326585/#XSLTH4134121122120121120120)
Now, configure SQL Mail. There are three locations where SQL Mail needs to be
configured. In Enterprise Manager they are as follows:
1)
Under the SQL Server Properties Server Settings tab. To view
SQL Server properties, right-click on the name of the server, then select "View
Properties". Click on the "Server Settings" tab and click on the "Change"
button in the SQL Mail group box. Select the default profile (the one you just
created) then click on the "Test" button to verify that SQL Mail can
communicate with Outlook.
2)
Under the SQL
Server Agent Properties General tab. To view SQL Server Agent properties,
expand the server folder, expand the management folder then right-click on the
SQL Server Agent icon and select "View Properties". The "General" tab will be
displayed by default. Select the Outlook profile from the drop-down list and
click the "Test" button to verify that SQL Server Agent can communicate with
Outlook. Warning: The SQL Server Agent service may need to restart.
3)
Under the SQL Mail Configuration tab. To view SQL Mail
properties, right-click on "SQL Mail" then select "Properties". Select the
Outlook profile from the drop-down list and click the "Test" button to verify
that SQL Mail can communicate with Outlook.
Use the xp_SendMail extended stored procedure in the master database to send
messages, and use xp_startmail and xp_stopmail to start and stop SQL Mail. Note
that xp_sendmail automatically starts SQL Mail if it hasn't already been
started.