Solved

Saving email as .msg file from SQLServer 2008 R2

Posted on 2013-05-28
5
625 Views
Last Modified: 2013-06-06
I have a SQL Server 2008 R2 database in which emails are stored in a table and any attachments (pdf, xls, etc) to those emails are stored in an image datatype field in a separate table.  

My task is to reassemble the emails with their attachments and then save the emails as .msg files in a folder.   The .msg files will be imported into a different application and the old application will be decommissioned.

I am looking for a way to do this using a stored procedure or sql script.  Does anyone have an example of something along these lines?
0
Comment
Question by:Kenny Johnson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 

Author Comment

by:Kenny Johnson
ID: 39205172
Based on internet and EE searches, I put together the following code which retrieves a specific attachment (stored as binary image in db) and writes the image to a file.  I need to further develop the script to select attachments as I step through the table that stores the email subject, body, from/to, etc. to write a combined email with attachment(s) to a .msg file, but this is a start.  


DECLARE @objStream INT
DECLARE @imageBinary VARBINARY(MAX)
SET @imageBinary = (SELECT documentdata FROM dbo.Documents WHERE documentID = 43)
-- hard code to retrieve one specific pdf document for now, will change to pass a value based on each email in a cursor

DECLARE @filePath VARCHAR(8000)
SET @filePath = 'C:\Temp\Test_File.pdf'


EXEC sp_OACreate 'ADODB.Stream', @objStream OUTPUT
EXEC sp_OASetProperty @objStream, 'Type', 1
EXEC sp_OAMethod @objStream, 'Open'
EXEC sp_OAMethod @objStream, 'Write', NULL, @imageBinary
EXEC sp_OAMethod @objStream, 'SaveToFile', NULL,@filePath, 2
EXEC sp_OAMethod @objStream, 'Close'
EXEC sp_OADestroy @objStream


Anyone done anything similar to construct a .msg file with attachment(s) using a T-SQL script or a stored procedure?  Or is this a bad idea to try to use T-SQL/stored procedure for this purpose? I've been going this route because I don't have VB or C# background; I have more of a dba/admin knowledge.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39210542
Or is this a bad idea to try to use T-SQL/stored procedure for this purpose?
To be blunt and since you asked yes.  This is best achieved using .NET to create an app that does not run on SQL Server.
0
 

Author Comment

by:Kenny Johnson
ID: 39210894
Thanks for confirming what I suspected.  

Given that I have no background in .NET, any suggestions that would point me in the right direction to get started with that approach?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39212240
Then I would consider hiring a competent .NET developer to do the project.
0
 

Author Closing Comment

by:Kenny Johnson
ID: 39227366
Thanks for the point in the right direction.  I had an external resource look at the problem and they confirmed the complexity of my problem and we're working on a solution. Saved me from a big headache of trying to do it with the wrong tools.
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question