Solved

Saving email as .msg file from SQLServer 2008 R2

Posted on 2013-05-28
5
621 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
[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: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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Many people use more than one email account and so it becomes difficult for them to manage them when they use separate accounts,  so, in this article, I have shared an easy way to add Other Mail Accounts in your Google Inbox. It helps to combine all…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

752 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