SQLMAIL изнутри

По материалам статьи Sqlguru на SQLTEAM.COM "SQLMail in Depth"

Один из ГУРУ SQLTEAM рассказывает в этой статье о том, как можно с помощью электронной почты посылать запросы SQL серверу в прикреплённых файлах так, чтобы информация была введена в базу данных.
Чтобы получить результат действия запроса по почте, используется хранимая процедура xp_sendmail. Ниже представлен её синтаксис (за исключением recipients, все параметры необязательны):

xp_sendmail
@recipients='recipient list',
@subject='subject line',
@message='message text',
@attachments='file to attach',
@query='query to execute'

Xp_sendmail может быть использована и для отправки простых уведомлений:

xp_sendmail
@recipients='MSSQLHelp@pisem.net',
@subject='Проба пера',
@message='Это просто проба. Удалите это письмо!'

Эту процедуру можно использовать для отправки по почте результатов исполнения запроса:

xp_sendmail
@recipients=''MSSQLHelp@pisem.net'',
@subject='Выборка по подписчикам:',
@query='SELECT * FROM Subscribers WHERE SubscriberID=63'

Многие из дополнительных параметров xp_sendmail предназначены для управления исполнением запроса (database context, database user) и его отображением (headers, formatting, attach query results in a file).

Хранимая процедура - xp_findnextmsg возвращает маркер одиночного сообщения электронной почты во входном почтовом ящике. Она используется для просмотра списка сообщений и имеет следующий синтаксис:

xp_findnextmsg
@msg_id='идентификатор сообщения' [OUTPUT],
@unread_only='{true|false}'

Если unread_only установлен в ИСТИНУ, то будут возвращаться идентификаторы только не прочтённых сообщений из входного почтового ящика.
Если msg_id установлен в NULL, то вернётся идентификатор первого сообщения во входном почтовом ящике.
Если msg_id является допустимым идентификатором существующего сообщения, то вернётся идентификатор следующего за ним сообщения.

Идентификатор сообщения возвращается через параметр msg_id, если он определён как OUTPUT. Иначе, идентификатор возвращается в отчёте исполнения процедуры. Xp_findnextmsg возвращает NULL если сообщений больше нет.

Следующий пример возвратит все идентификаторы сообщений во входном почтовом ящике:

DECLARE @hMessage varchar(255)

EXEC xp_findnextmsg @msg_id=@hMessage OUT
WHILE @hMessage IS NOT NULL
BEGIN
SELECT 'Message handle:' + @hMessage
EXEC xp_findnextmsg @msg_id=@hMessage OUT
END

Для просмотра сообщений с известными идентификаторами используется хранимая процедура xp_readmail. Синтаксис её следующий:

xp_readmail
@msg_id = 'идентификатор сообщения',
@peek = '{true|false}',
@date_received = 'message date' OUTPUT,
@originator = 'sender's friendly name' OUTPUT,
@originator_address = 'sender's resolved address' OUTPUT,
@subject = 'message subject' OUTPUT,
@message = 'message body' OUTPUT,
@recipients = 'recipient list' OUTPUT,
@cc_list = 'carbon copy recipient list' OUTPUT,
@bcc_list = 'blind copy recipient list' OUTPUT,
@unread = '{true|false}' OUTPUT,
@suppress_attach = '{true|false}',
@attachments = 'attachment list' OUTPUT

Большинство выходных параметров этой процедуры возвращают вспомогательные атрибуты сообщения и хорошо описаны в Books Online. Остановимся только на самых характерных моментах:
Если peek установлен в 'истину', то сообщение не будет отмечено как прочтённое, когда Вы просмотрите его. Это удобно, когда у Вас одновременно работают несколько процессов просмотра не прочтённой корреспонденции.
Если suppress_attach установлен в 'истину', то SQLMAIL не будет обрабатывать прикрепления.
Если suppress_attach установлен в 'ложь' (по умолчанию), SQLMAIL сохранит прикрепление сообщения во временный файлы и возвратит разграниченный точкой с запятой список имен файлов через параметр attachments. Этот список становится доступным для Вашего анализа и дальнейших операций с прикреплёнными файлами. Например, обработка их BCP.

Следующий пример печатает каждое сообщение во входном почтовом ящике:

DECLARE @hMessage varchar(255),
                    @MessageText varchar(1000)

EXEC xp_findnextmsg @msg_id=@hMessage OUT
WHILE @hMessage IS NOT NULL
BEGIN
EXEC xp_readmail @msg_id=@hMessage, @message=@MessageText OUT
SELECT 'Вам пришла почта! Вот она: ' + @MessageText
EXEC xp_findnextmsg @msg_id=@hMessage OUT
END

Если запустить xp_readmail без параметров, Вы получаете полный список сообщений во входном почтовом ящике. Сохранив этот список во временной таблице, Вы можете делать над ним любые выборки для достижения необходимых результатов.

Для удаления сообщения используйте:

xp_deletemail 'message ID'

Также, в своей статье, автор ссылается на реализованный им пример, когда он синхронизировал таблицу MS Excel с SQL сервером через электронную почту. Система была предназначена для поддержки мобильных пользователей, которые имели доступ к центральному офису только через электронную почту.

Перевод: Александр Гладченко  2001г.