Configuring the E-mail Mechanism

When a database is created by the MetaCommunications Install Expert, a special SQL Server Agent job is created on the SQL Server. The name of the job initially looks like:

<Your_Database_Name>-MetaSQLMail-mail.example.com

This job is part of the e-mail sending mechanism, and in its initial state it is inactive. To activate the e-mail mechanism, it is necessary to configure this job. Once configured, the job runs every minute and sends all messages accumulated in the message queue. To configure the job, the last part of the job name must be changed from mail.example.com to the real IP address of the SMTP (E-mail) server that should be responsible for sending e-mail messages. For example:

MyTestDB-MetaSQLMail-192.168.68.2

To change the job name:

1. Go to the SQL Server Enterprise Manager -> Management -> SQL Server Agent -> Jobs to view the list of SQL Server Agent Jobs.

2. Find the SQL Job which matches the following pattern: [<Your_Database_Name>-MetaSQLMail-<smtp_server_address>]

3. Go to "Properties" of the SQL Job and change the Job Name suffix to the real SMTP server address.

4. Troubleshooting:

   • If the SQL Job for your DB does not exists, restart the Application Server.
   • If the SQL Job does not appear after restarting the Application Server, contact MetaCommunications Technical Support.

Sending E-Mail from T-SQL Scripts

To send e-mail messages, use the stored procedure [sp_core_mail_add_message_to_queue] from your database. Each call to this procedure results in one message being added to the message queue. The procedure supports several ways to form the body (text content) of the message, as illustrated in the examples below:

Note on the examples: The e-mail addresses and the file names used in the examples are not real; they must be changed if you want to run the examples.

1. To form the message body from a char/varchar variable, supply the @aMailBody parameter:

   exec sp_core_mail_add_message_to_queue
         @aTo          = 'example-recipient-name@example-recipient-domain.com'
       , @aFrom        = 'example-sender-name@example-sender-domain.com'
       , @aFromName    = 'Name_of_the_Sender'
       , @aSubject     = 'Just a test...'
       , @aMailBody    = 'Test mail message.'
   

2. To form the message body from the contents of one column of one particular database table record, supply the @aMailBodyRef parameter:

   exec sp_core_mail_add_message_to_queue
         @aTo          = 'example-recipient-name@example-recipient-domain.com'
       , @aFrom        = 'example-sender-name@example-sender-domain.com'
       , @aFromName    = 'Name_of_the_Sender'
       , @aSubject     = 'Just a test...'
       , @aMailBodyRef = 'pubs.dbo.pub_info/pr_info/pub_id=9999'
   

   Notes on the format of the references:

   • The reference structure is:

     <table_name>/<column_name>/<condition_to_find_the_record_in_the_table>
     

   • To use a table from another database, add the database name and the SQL database user name (e.g. dbo) before the <table_name>:

     <database_name>.<database_user_name>.<table_name>/<column_name>/<condition_to_find...>
     

3. To add attachments to an e-mail message, supply the @aAttachment parameter:

   -- in the case of a single attachment:
   
   exec sp_core_mail_add_message_to_queue
         @aTo          = 'example-recipient-name@example-recipient-domain.com'
       , @aFrom        = 'example-sender-name@example-sender-domain.com'
       , @aFromName    = 'Name_of_the_Sender'
       , @aSubject     = 'Just a test...'
       , @aMailBody    = 'Test mail message.'
       , @aAttachment  = 'C:\file1.txt'
   
   -- in the case of multiple attachments:
   
   exec sp_core_mail_add_message_to_queue
         @aTo          = 'example-recipient-name@example-recipient-domain.com'
       , @aFrom        = 'example-sender-name@example-sender-domain.com'
       , @aFromName    = 'Name_of_the_Sender'
       , @aSubject     = 'Just a test...'
       , @aMailBody    = 'Test mail message.'
       , @aAttachment  = 'C:\file1.txt;C:\file2.txt'
   

Troubleshooting

All error messages are reported to the Windows Event Log, and can be seen using the Event Viewer (eventvwr.exe), in the Application Log.