The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Importing Email Attachments into SQL Server

I have a data report that is emailed to me every few days.  I would like to automate the import of this data into my sql server.  I thought this would be straight forward, but I'm running into some walls.  I'm using Exchange 2003 and SQL Server 2005.  The new "Database Mail" seems to only support sending emails.  The older sprocs for SQL Mail say they are being deprecated in the Books Online.  I'm cool with using the old sprocs for now, but I'm getting and error using the xp_findnextmsg sproc.

The following code (from BOL):
DECLARE @status int, @message_id varchar(255)
EXEC @status = xp_findnextmsg @msg_id = @message_id OUTPUT

Gets the following error:
Msg 17985, Level 16, State 1, Line 0
xp_findnextmsg: Procedure expects parameter @user, which was not supplied.

Since these sprocs are being deprecated I can't seem to find any info on what this parameter is.  Any help anyone has would be greatly appreciated...whether is solves this problem or leads me in another direction about how to solve my bigger problem or importing from and email inbox.

Thanks,
Mark
Mark Fruhling Send private email
Monday, April 14, 2008
 
 
Maybe a security issue?

http://msdn2.microsoft.com/en-us/library/ms177418.aspx

"Because of SQL Server 2005 changes, SQL Authenticated users of SQL Mail can send mail attachments only if they are members of the sysadmin fixed server role."
DJ Send private email
Monday, April 14, 2008
 
 
DJ,

I can send email with no problems, and I am testing on my development machine, where I am a sysadmin.  My question is how to deal with reading emails from sql server 2005 and extracting attachments from them so I can import them.

Thanks,
Mark

Tuesday, April 15, 2008
 
 
Hey I just did this project!  Even thought of making it a tool that I would sell on my web site (http://www.DigitalTools.com) , but didn’t see much interest when I Googled around.  I think most people are using linked servers or would setup a web service to transfer the data instead of the email path.    But I was in your situation because I was developing a site for my girlfriend and her company is still in the early 90’s.

So here is what I did:
1.    Wrote a .NET application that would open her email account (Yahoo Mail Plus) using POP3Commands.
2.    It would search for emails that had the subject that I was looking for and find the one with the greatest date.
3.    If the greatest date found was greater than the date it saved off the last time it looked,  it would download that email’s attachment.  All this was done with POP3Commands issued by my .NET application.
4.    This application was scheduled as the first step in a SQL Server 2005 Agent job.
5.    The second step was a SSIS package that uploads a new file, if found, to a table and then runs a stored proc to process it and then moves off the file.


I did it this way because the process of looking thru the inbox and looking at the subjects and dates is quick and cheap.  Downloading the emails and their attachments is expensive.  So this solution never downloads an email and only downloads an attachment that it is going to use.

Wednesday, April 16, 2008
 
 
Souns like it might be a mail profile issue. Log into the SQL box using the user account that SQL is running under (and/or SQL Agent), and check that the appropriate mail client settings are made.
 http://support.microsoft.com/kb/315886/EN-US
 http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q263556
Dave_W
Friday, April 25, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz