Solved

Saving email as .msg file from SQLServer 2008 R2

Posted on 2013-05-28
5
611 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:densha99
  • 3
  • 2
5 Comments
 

Author Comment

by:densha99
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:densha99
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:densha99
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remaiā€¦
Read this checklist to learn more about the 15 things you should never include in an email signature.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

776 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