Sqlmail can send and receive mail, maybe many people have XP_sendmail sending mail, here is introduced to a mail server receiving the mail, according to the message content (only query statement), the result of the query statement in the message is automatically returned in an attachment form. Send the mail.
EG: Send emails, mail content is: SELECT TOP 10 ID, NAME from sysObjects, automatically reply mail, the content of the above query statement, is sent in an attachment.
1: Of course, it's a lot of information in this area, and you will not be described here.
2: Take sp_processmail to modify (resolve the sender's mail address to the real email address)
Alter procedure sp_processmail @Subject varchar (255) = null, @filetype varchar (3) = 'txt', @separator varchar (3) = 'tab', @set_user varchar (132) = 'guest', @dbuse varchar (132 ) = 'master' as declare @status int declare @msg_id varchar (64) Declare @ORIGINATOR VARCHAR (255) Declare @originator_address varchar (255) Declare @a varchar (255)
declare @cc_list varchar (255) declare @msgsubject varchar (255) declare @query varchar (8000) declare @messages int declare @mapifailure int declare @resultmsg varchar (80) declare @filename varchar (12) declare @current_msg varchar (64) select @ messages = 0 select @ mapifailure = 0 if @ separator = 'tab' select @ separator = CHAR (9) / * get first message id * / exec @status = master.dbo.xp_findnextmsg @ msg_id = @ msg_id output, @ unread_only = 'true' if @status <> 0 select @ mapifailure = 1 while (@ mapifailure = 0) begin if @msg_id is null break if @msg_id = '' break exec @status = master.dbo.xp_readmail @ msg_id = @ msg_id, @ originator = @ originator output, @ cc_list = @ cc_list output, @ subject = @ msgsubject output, @ message = @ query output, @ peek = 'true', @ originator_address = @originator_address output, @ suppress_attach = 'true' IF @Status <> 0 Begin Select @ mapifault = 1 Break end / * get new message ID before processing & dele ing current * / select @ current_msg = @ msg_id exec @status = master.dbo.xp_findnextmsg @ msg_id = @ msg_id output, @ unread_only = 'true' if @status <> 0 begin select @ mapifailure = 1 end if ((@subject Is null) or (@ Subject = @ msgsubject) begin / * generate random filename * / select @ filename = 'sql'
convert (varchar, ROUND (RAND () * 100000,0)) @filetype exec @status = master.dbo.xp_sendmail '.' - @ recipients = @ originator, @ recipients = @ originator_address, @ copy_recipients = @ cc_list , @ message = @ query, @ query = @ query, @ subject = 'Query Results', @ separator = @ separator, @ width = 256, @ attachments = @ filename, @ attach_results =' true ', @ no_output =' false ', @ echo_error =' true ', @ set_user = @ set_user, @ dbuse = @ dbuse if @status <> 0 begin select @ mapifailure = 1 break end select @ messages = @ messages 1 exec master.dbo.xp_deletemail @current_msg end / * end of xp_sendmail block * / end / * end of xp_findnextmsg loop * / / * finished examining the contents of inbox; now send results * / if @ mapifailure = 1 begin raiserror (15079, -1, -1, @ messages RETURN (1) ELSE RETURN (0) - sp_processmail3: Built a stored procedure under the Master database USE MASTERCREATE PROC ANSWERMAILASSP_PROCESSMAIL @Subject = 'SQL', @ filetype = 'csv', @ s EPARATOR = ',', @ set_user = 'dbo', @ dbuse = 'database name'
4: Sched out this stored procedure with Job.
5: It can be tested now. Send an email to the mailbox address configured on the server. The subject is SQL content as a simple query statement eg: select getdate () sends
6: Wait until Job scheduling time will receive a reply.
(Test by above Windows2000 SQL2000 Microsoft Outlook)
