• Solution 1 - Use a DOS based email program.
• Solution 2 - Use SQL’s built-in mail facility.

I’m not really lazy, but I much prefer it when information comes to me rather me having to go to the information. I am, therefore, always looking for ways in which I can get log files, system alerts, backup failures etc sent to my mail box rather than having to tramp through various applications, some of which actually require me to get up and log on to the server (usually in another room) to check if an overnight job ran OK. No doubt one of the many reasons why companies buy SMS will be because they’ve read somewhere that it will email you with alerts, but I wonder how many network administrators, having installed SMS, have pondered on why there isn’t a simple mail facility for alerts. After all, SMS is part of the Back Office suite so one would expect easy connectivity to Exchange Server. Well perhaps if you’re not Bill Gates you might! Well search no more. Here are two solutions that resolve not only the SMS problem, but might also provide solutions to emailing alerts and logs from other programs.

Solution 1 - Use a DOS based email program

There’s a wonderful piece of freeware which you can download from the Internet at http://www.theshareware.net/directories/internet/utilities/mail/ called GBMailer. It’s simple, it works, and it’s free. It uses a simple command line to send the contents of any text file via email. This could be any log file from any application or a specially created text message describing a specific event. In the case of an SMS alert here’s what you would do. For the purpose of this exercise we’ll assume we want to trap alerts relating to low disk space on a file server.

• Create a folder on the SMS server into which you will place the GBMailer program e.g. c:\smsalerts.
• Using notepad, create a simple text message describing the SMS alert (e.g. ‘File server low on disk space’) and save it in the same folder as the GBMailer program with a suitable file name e.g. Alert001.txt.
• In SMS Administrator, create a new query that checks your File server for low disk space (Figure 1 shows a query that checks for disks exceeding 80% capacity on a file server with an SMSID of ST100001), and then create an alert and select this query.
• In the Alert Properties dialogue box, click on the ‘Actions’ button and check the box marked ‘Execute command line’. Enter the full path to the GBMailer program and the appropriate command to send the contents of the text file created in 2 above. e.g. c:\smsalerts\gbmail.exe -file c:\smsalerts\alert001.txt -h exchange-server -from "SMS Admin" -to administrator@mydomain.com -s "SMS Alert on File server"

This instructs SMS to run the GBMailer program (residing in the smsalerts folder) to send the administrator the contents of the file alert0001.txt with the subject of ‘SMS Alert on File server’, where ‘exchange-server’ is the name of the mail server in this example. N.B. The full command line syntax for GBMailer comes with the download.

It is also important to note that the ‘from’ part of the command does not have to be a valid email address with GBMailer, but SMS does insist on having the full file location for both the GBMailer program and the text file you want to send. This is true of any command line execution in SMS, and leaving out the full path can cause unpredictable results. To simplify the command, you could create a batch file to run GBMailer and pass the name of the appropriate text file to it.

• You can now sit back and wait for the alert to trigger and send you an email notifying you of the event.

Like most good solutions, this one is simple and very effective. GBMailer can also be triggered with a batch file or scheduler to send log files etc following overnight backups or folder synchronisations.

Solution 2 - Use SQL’s built-in mail facility

Whereas the previous solution works regardless of what type of mail server you are using, this solution requires you to use Exchange and have SQLMail running on your SQL server. So the first thing you have to do (if not done already) is install an Exchange or Outlook client on the SQL server and then you can start SQLMail running. If you already have an appropriate Exchange or Outlook client installed you can go straight to step 4. If you already have SQLMail running you can go straight to step 8.

1. Log on to your SQL server with the user account that is the one that MSSQLServer service runs under. You can determine this by opening the Services tool in Control Panel, selecting the MSSQLServer service and clicking the Start-up button.
2. Install the Exchange client, and set up a profile using the account name of the user who logged on to Windows NT in step one, and check that EXCHNG32.EXE can connect to the Exchange server and mail can be sent.
3. Make sure the Exchange profile used does not have a Personal Message Store (.pst) by running the Mail and Fax tool in Control Panel. Click the Show Profiles button and in the Mail dialog box select the profile you just created. Select the services tab and confirm that the only services are Microsoft Exchange Server and Personal Address Book.
4. To run SQLMail with an Exchange Server, it is necessary that the MSSQLServer service is run under the same user account that logged on during step one above. Once again, you can determine this by opening the Services tool in Control Panel, selecting the MSSQLServer service and clicking the Start-up button.
5. Confirm that the SQLMail user name and password are correct by running SQL Server Setup. Select Set Server Options and click the Mail Login button. The user name must match the Profile name created in step two above. Check the box that reads ‘Auto start the Mail client’.
6. To test the SQL Server access permissions to the Exchange Server, start up ISQL_w and log in to your SQL server. In the query window, type the following command xp_cmdshell "NET USE \\Exchange-Server\IPC$" and execute it. N.B. in this example the Exchange Server is located on a computer named "Exchange-Server", and obviously you will need to alter this to suit your own circumstances. If you don’t get a message in the results window telling you the command has completed successfully, then step two has not been completed correctly.
7. Type in a new command in the query window as follows, xp_startmail and execute it. You should get a message in the results window either telling you that SQL Mail has started, or if it has been able to Autostart, has already started. You can now close ISQL_w.
8. Now follow steps 3 & 4 as in Solution 1, except for a change to the command line. Instead of using GBMailer we’ll use SQL’s built-in mail connection to Exchange. Enter a line as in Figure 2, c:\mssql\binn\isql.exe -U sa -P sql -Q "xp_sendmail ‘administrator@mydomain.com’, ‘Alert message’ " replacing

The full syntax for xp_sendmail can be found in the Transact help files of both ISQL_w and the SQL Enterprise Manager, and allow for all the usual options of carbon copies, blind carbon copies etc.

Which one is best for you depends on how much work you want to put into getting your alerts. Option one is more simple to operate but does require a text file to be created for every instance of an alert you create in SMS. Option two requires more effort initially, but once you’ve got the hang of it, does provide a reasonably straightforward messaging system for alerts. There’s only one possible drawback with option two and that’s related to security. It probably hasn’t escaped your notice that a password for an SQL account appears in the command line. You will therefore need to use the SMS Security Manager to make sure that details of alerts are hidden from prying eyes. John Hunter