Saving a mail to a file

• Michael Solander-204238

  Valued Member

  Points: 53

  More actions

  January 26, 2005 at 7:59 am

  #162279

  I've created af modified version og the xp_processmail procedure that reads mails sent to a specifik adress and inserts subject, sender etc. in a table(mails) in a database(mailDB).

  I would like to save the mail as a file on the server (and inserting a path to the file in a coloumn of the table) - how do I do this (in my stored procedure) ?

  Any help would be most welcome !

  Michael Solander

  IT-consultant

  Denmark

   

• Wayne Lawton

  Hall of Fame

  Points: 3532

  More actions

  January 26, 2005 at 8:25 am

  #539155

  G'Day,

  Assuming

  a) that you are able to store the mail as text (not a direct binary mail file), and

  b) that text you wish to store can be retrieved via a query

  then the following snippet shows how to store the results of a query to a text file.  The example uses the orders table in the Northwind DB.  @KeyValue is simply a unique identifier for the row you wish to store out to the text file.

  DECLARE @TableName VARCHAR(200),

          @FileLocation VARCHAR(200),

          @Command VARCHAR(8000),

          @ReturnCode INT,

          @KeyValue INT

  SET @TableName = 'ORDERS'

  SET @FileLocation = 'c:\bcp_test.txt'

  SET @KeyValue = 10258

  SET @Command = 'BCP ' + '"SELECT * FROM ' + DB_NAME() + '.dbo.' + @TableName + ' WHERE OrderID = ' + CAST(@KeyValue AS VARCHAR) + '"' + ' QUERYOUT "' + @FileLocation + '" -t,  -c -a8192'

  PRINT @Command

  EXEC @ReturnCode = master..xp_cmdshell @Command

  Hope this helps

  Wayne

   

• Michael Solander-204238

  Valued Member

  Points: 53

  More actions

  January 27, 2005 at 1:16 am

  #539277

  Hi Wayne,

  a) and b) is assumed correctly - I tried your code out and it works !

  Thanks a lot !

  Michael