Sql – Sending infopath forms via email (as attachment) to be parsed by SQL Server 2005

infopathsql server

Just looking at the requirements of a new project and I wanted to make sure this use case was sound:

  • user fills in InfoPath (2003) form locally on their PC
  • a button within the InfoPath form titled 'submit' brings up a new outlook (2003) email message with the infopath form attached. User presses sends and email is sent to an exchange mailbox.
  • sql server preiodically checks this mailbox, downloading any new submissions with the infopath form attached
  • sql server parses the attachment and the fields within the infopath form.

Is SQL Server capable of parsing mail attachments this way? Any caveats with this approach?

The attraction to using Outlook as the submission technology is that the process for the user is the same if they are offline. Outlook will then automatically sync when they come back online. It is essential that users have some way to fill the forms in offline, 'submit' them, and then have then synced automatically with the server when they next come online.

edit: to clarify, I am not looking for a way to cache form data from the server->client. I am looking to cache the completed form. Building a separate application to cache the completed reports on the client is not an option.

Best Answer

Later versions of SQL Server are capable of running .NET code within them, and as such you might be able to poll a mailbox from SQL Server and process an InfoPath form. However, I'm not sure I'd do it this way.

It might be better to consider writing a Windows Service that does this work. The Windows Service would start up, inspect the mail box of a "service account", read the mails, extract the attachments, process the xml and, eventually, write the data to SQL. It could also, presumably, respond to that mail with a confirmation or errors if business rules or validation errors occurred.

I'm not sure I'd put all of the above logic into SQL - for one thing, I suspect you'd have issues with accounts (having to have the account SQL was running under be able to access the Exchange mailbox account).

Your mileage may vary, and you should prototype this to determine what works best for you, but I'd try and keep the code the uses Exchange as a "work queue" separate from SQL and only put the code that deals with writing data into tables in SQL.

Related Topic