How can e-mail with SQL Server without using an MS-Mail or Exchange server?

A. A variety of methods :-

1. Use xp_cmdshell to run an SMTP sendmail program.

2. Use an XP with built-in SMTP functionality - http://www.spudsoft.demon.co.uk/code/index.html

3. Configure NT's mapi interface to talk to SMTP - try the following instructions for SQL 6.5. (note these aren't written by the author, and haven't been tested either, but they do, allegedly, work)

Set up Microsoft SQL Server service. Note: Microsoft SQL Server must start up using a user account that has been given administrator privileges. The account must also allow the user to logon as a service. This allows Microsoft SQL Server to interact with email. Note: By default, SQL Server 6.5 creates an account named "SQLExecutiveCmdExec". Verify that this account exists and is set up properly: 

1. Click on the "Start" button 
2. Programs 
3. Administrative Tools ( Common ) 
4. User Manager for Domains, if the option is "User Manager" go to step 9. 
5. When the User Manager program loads, select "Users" from the menu. 
6. Choose the menu option "Select Domain" 
7. Type in the name of the machine, e.g. "DBASQLTEST" 
8. Click the OK button. 
9. You should now have a list of users on the machine. 
10. If the account "SQLExecutiveCmdExec" exists: double-click the user, a dialog box will appear. Skip to step 18. 
11. Select "Users" from the menu. 
12. Choose the menu option "New User". 
13. A dialog box will appear. 
14. In the User Name field, type "SQLExecutiveCmdExec" 
15. In the Full Name field, type "SQLExecutiveCmdExec" 
16. In the password field type a unique password (record you password in a safe place, you will need it later in this setup )
17. In the Confirm Password field retype the password you just entered. 
18. Uncheck the box next to "User Must change Password at Next Logon". 
19. Check the box next to "User Cannot Change Password". 
20. Check the box next to "Password Never Expires". 
21. Click on the "Groups" button. 
22. The Group Membership dialog box will appear. 
23. Add the group "Administrators" 
24. Click the "OK" button to close the Group Membership dialog. 
25. Click the "OK" button to close the User Properties dialog. 
26. Select "Policies" from the menu. 
27. Choose the menu option "User Rights". 
28. The User Rights Policy dialog box will appear. 
29. Check the box next to "Show Advanced User Rights" 
30. On the drop down select list, choose the option "Log on as a Service". 
31. Click on the "Add" button. 
32. The "Add Users and Groups" dialog box will appear. 
33. On the drop down select list, choose the current computer name, e.g. "\SERVERNAME*". 
34. Click the "Show Users" button. 
35. Scroll down the list of users and select "SQLExecutiveCmdExec". 
36. Click on the "Add" button. 
37. The user "SQLExecutiveCmdExec" should now be in the "Add Names:" list. 
38. Click on the "OK" button to close the "Add Users and Groups" dialog. 
39. Click on the "OK" button to close the "User Rights Policy" dialog.

Install Windows NT Messaging. Note: Next, make sure Windows NT Messaging
is installed on the Microsoft SQL Server machine. · Note: SQLmail uses
Windows Messaging, so it needs to be configured for proper operation. Note:
This can be verified by: 

1. Open the Control Panel and double click on Add/Remove Programs. 
2. Select the Windows NT Setup tab and scroll to the bottom of the list box that appears. 
3. Verify that the Windows Messaging check box is checked. 
4. If it is not,check it now. 
5. Click OK. 
6. At this point, if you need to install Windows Messaging you will need to supply the Windows NT CD.

Set Up Mail Services · Note: You will need two internet mail accounts before
continuing. 1. You will need a POP mail account login, password and server
name. 2. You will need a SMTP mail account login, password and server name.
3. Next, close all programs on the Microsoft SQL Server machine and log in as
a new user. Use the SQLExecutiveCmdExec account.

1. On the desktop, right-click the "Inbox" icon, and select Properties
option. Here you will need to add anew profile for the SQLExec user. Click on
the "Add" button to begin creating a Windows Messaging Profile: 2. Uncheck
the box next to "Microsoft Mail" (Unless there is an Exchange Server
Available) 3. Ensure the box next to "Internet Mail" is checked. 4. Click on
the "Next" button. 5. You will be asked for the method to connect to the mail
server, check the radio button next to "Network". 6. Click on the "Next"
button. 7. You will be asked to specify the Mail server name or IP address,
enter either one here. 8. Click on the "Next" button. 9. You will be asked to
choose the mode for transferring messages. Check the radio button next to
"Automatic". 10. Click on the "Next" button. 11. You will be asked for the
email address, enter it in the Email Address field. 12. Enter
"SQLExecutiveCmdExec" in the Full Name field. 13. Click on the "Next" button.
14. You will be asked for the mailbox name. Enter a mailbox name in the
mailbox name field, e.g. userid. (This is the mail server login name ). 15.
Enter the password for the mailbox name in the password field. (This is the
mail server login name's password ). 16. Click on the "Next" button. 17. You
will be asked for a location for your personal address book. Use the
default. 18. Click on the "Next" button. 19. You will be asked for a location
for your personal folder file. Use the default. 20. Click on the "Next"
button. 21. You should receive the message that setup is "Done!" 22. Click on
the "Finish" button. 23. In the profiles list box, you should now see an
entry for "Windows Messaging Settings". 24. Click on the "Copy" button. 25.
You will be asked to name the new profile. Type "SQLExecutiveCmdExec". 26.
Click "OK" to commit the copy. 27. In the profiles list box, you should now
see an entry for "SQLExecutiveCmdExec". 28. In the drop down select list for
"When Starting Windows Messaging, use this profile:", choose the newly
created profile, "SQLExecutiveCmdExec". 29. Click on the "Close" button to
complete the setup.

Configure SQL Server Service and SQLExecutive to use new mail account · You
will need to stop the Microsoft SQL Server service 1. Open the Control Panel.
2. Double-click Services. 3. Scroll down until you find Microsoft SQL Server,
select it, and click the stop button. 4. The service status should change to
stopped. 5. Double-click on the Microsoft SQL Server service to bring up its
properties dialog box. 6. At the bottom of the dialog is the account by which
Microsoft SQL Server will use to startup. Change this to use the
SQLExecutiveCmdExec account. Be sure to enter the password correctly here or
Microsoft SQL Server will not start up. 7. Close this dialog by clicking the
"OK" button. 8. Start the service by clicking the start button. 9. The
service should start up successfully. If it does not start check to make sure
the password you just entered matches the one entered in the User Manager for
Domains. 10. Scroll down until you find SQLExecutive, select it, and click
the stop button. 11. The service status should change to stopped. 12.
Double-click on the SQLExecutive service to bring up its properties dialog
box. 13. At the bottom of the dialog is the account by which Microsoft SQL
Server will use to startup. Change this to use the SQLExecutiveCmdExec
account. Be sure to enter the password correctly here or Microsoft SQL Server
will not start up. 14. Close this dialog by clicking the "OK" button. 15.
Start the service by clicking the start button. 16. The service should start
up successfully. If it does not start check to make sure the password you
just entered matches the one entered in the User Manager for Domains.

Configure SQL Server Setup Options 1. Click on the "Start" button 2. Programs
3. Microsoft SQL Server 6.5 4. SQL Setup 5. The "Welcome" dialog will appear.
Click the "Continue" button. 6. The "SQL Server Already Installed" dialog
will appear. Click the "Continue" button. 7. Check the radio button next to
"Set Server Options". 8. Click the "Continue" button. 9. Check the box next
to "Autostart SQL Mail". 10. Click the "Mail Profile" button, which is the
last button in the center of the dialog box. 11. In the dialog box that
appears, enter "SQLExecutiveCmdExec". 12. Click the "OK" button. 13. Click
"Change Options" button. 14. The "Exchange Login Configuration" dialog box
may appear again. If it does, simply click the "Continue" button. 15. A
dialog box stating that the options have been successfully set should appear.
Click the "Exit to Windows NT" button.

You will need to stop and restart SQL Server 1. Click on the "Start" button
2. Programs 3. Microsoft SQL Server 6.5 4. SQL Service Manager 5. A dialog
box with a traffic light will appear. 6. Double click the Red light to stop
the service. Watch the status bar at the bottom of the dialog box for the
message "The service is stopped" 7. Double click the Green light to start the
service. Watch the status bar at the bottom of the dialog box for the message
"The service is running" 8. Close the dialog box.

Set up Internet Mail ( MAPI Setup )
· You should have at least Internet Explorer installed on your server.
1. Double click the "Internet Explorer" icon on your desktop to start IE3.
2. Select "Go" from the menu.
3. Choose the "Read Mail" option.
4. The "Browse for Folder" dialog box will appear.
5. You may either choose a folder or use the default.
6. Click on the "OK" button.
7. A new dialog "Wizard" will appear.
8. Click on the "Next" button.
9. In the name field, enter "SQLExecutiveCmdExec".
10. In the Email Address field enter the email address, e.g. [email protected].
11. Click on the "Next" button.
12. In the "Incoming mail (POP3) server" field, enter the name of the POP3
server.
13. In the "Outgoing mail (SMTP) server" field, enter the name of the SMTP
server.
14. Click on the "Next" button.
15. In the "Account Name" field, enter the email account name, e.g. userid
16. In the "Password" field, enter the email account name's password.
17. Click on the "Next" button.
18. You will be asked for the connection type, choose "I use a LAN connection"
19. Click on the "Next" button.
20. Click on the "Finish" button.
21. The MAPI mail client will appear. You may close the MAPI mail client
application.

Start SQL Enterprise Manager. See if SQL Mail is working by expanding your server and seeing if the SQL Mail icon turns green. · If it turns red, something went wrong; green means it is working properly. · It may take a little time for this icon to turn any color, so wait for a little bit before doing anything else. · If everything seems to be working properly, open the SQL Tool and type the following: 

1. xp_sendmail '[email protected]',@message='This is a test message.' Where [email protected] is an existing internet mail address that can receive messages. 
2. Execute the statement by pressing Ctrl-E or clicking the green GO button in the Query
Tool. You should receive a message in the result window stating "Mail Sent."

Hopefully the configuration process went well and Microsoft SQL Server is now capable of processing mail. There are numerous stored procedures available to process inbound and outbound mail. See the SQL reference manuals for more
information on these.