Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Saving email as .msg file from SQLServer 2008 R2

Posted on 2013-05-28
5
Medium Priority
?
633 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 2000 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

715 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