INF: How to Query with Data Using SQL Mail and Exchange Server (279660)

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 "How to use SQL Mail (Transact-SQL)" 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 "Microsoft Exchange Server Scripting Agent" 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.