• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 644
  • Last Modified:

Saving email as .msg file from SQLServer 2008 R2

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
Kenny Johnson
Asked:
Kenny Johnson
  • 3
  • 2
1 Solution
 
Kenny JohnsonIT Support StaffAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
Kenny JohnsonIT Support StaffAuthor Commented:
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
 
Anthony PerkinsCommented:
Then I would consider hiring a competent .NET developer to do the project.
0
 
Kenny JohnsonIT Support StaffAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now