Microsoft KB Archive/279660

= INF: How to Query with Data Using SQL Mail and Exchange Server =

Article ID: 279660

Article Last Modified on 10/31/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q279660



SUMMARY
SQL Server SQL Mail can act as an e-mail query gateway that allows you to send a query by e-mail and receive the results by e-mail. For more information, see the &quot;How to use SQL Mail (Transact-SQL)&quot; topic in SQL Server Books Online.

You may encounter a situation where you want to send only a parameter to SQL Server by e-mail, have that parameter inserted into a query template, and then have the results e-mailed back to the sender. You can accomplish this in several ways:  Use an Exchange Event Service Scripting Agent. A script will be installed in the Inbox of an Exchange Mailbox, or a Public Folder. Every time that a message arrives in that folder, the script will examine the message, and then use ActiveX Data Objects (ADO) to do some SQL work with the data in the message.

To get started using Exchange Event Service scripts, see &quot;Microsoft Exchange Server Scripting Agent&quot; at the following Microsoft Developer Network (MSDN) Web site:

http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/exchserv/events_4rg4.htm

  Create a job and schedule it on SQL Server.

In the code below (which uses the pubs sample database), if you send an e-mail that contains an author's last name to SQL Server, you will receive a reply with the author's details. Note that the xp_findnextmsg extended stored procedure will return the msg_id.

Also note that the author's name must have something to delimit it with; otherwise, we cannot use PATINDEX to get just the name (because there will be some trailing line feed characters). This code uses a comma as a delimiter.

NOTE: For this code to work on SQL Server 2000, you must apply Service Pack 1 to obtain the fix that is described in the following article:

281238 FIX: xp_findnextmsg Does Not Return a Value for @msg_id Parameter on Output

declare @status int declare @msg_id varchar(64) declare @originator varchar(255) declare @cc_list varchar(255) declare @msgsubject varchar(255) declare @query varchar(255) declare @messages int declare @resultmsg varchar(80) declare @filename varchar(12) declare @current_msg varchar(64)

Set @msg_id = NULL while (1=1) begin exec master..xp_findnextmsg @msg_id = @msg_id output if @msg_id is null break exec @status = master.dbo.xp_readmail @msg_id, @originator=@originator output, @cc_list=@cc_list output, @subject=@msgsubject output, @message=@query output

if @status <> 0 begin Print 'MAPI Failed' end else begin declare @nIndex int set @nIndex = patindex('%,%',@query) set @query= substring(@query,1,@nIndex - 1 ) set @query = 'select * from pubs.dbo.authors where au_lname =  + @query +' select @query

EXEC master.dbo.xp_sendmail @originator,  @query = @query, @no_header= 'TRUE' end end For example, a sample e-mail might have the following message: White, and this name would be used to collect information about the author named White.  Use a combination of the previous two methods. Use Exchange to insert a row into SQL Server table, and the trigger on that table will send the e-mail.

Keywords: kbinfo KB279660

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.