How to query a database via email

Justin Mosier · Joined: 05 Aug 2007 Posts: 2

Does anyone know how to query a database (SQL Server) via
email (through Exchange)?

I'm in the process of proposing a web application that
stores vendor information in a SQL Server database. I not
only want to provide an HTML interface to search vendors,
I also want to provide easy access for mobile devices via
a simply email query. I have no idea if email query is
possible with Exchange or how to set it up on the mail
server.

The idea is someone could send an email
to "vendorsearch@mycompany.com" with the search criteria
in the body of the email, such as "48180" for a zip code.
The email would be processed by Exchange, identified as a
query search, and forwarded on to a web server (perhaps
by invoking an HTTP call) or to the SQL server to lookup
the data. Then an email would be sent back to the user
with the results in a text email. Sounds simple, huh?

Is this possible, if so how, or is this pie in the sky?

Thanks!

Archived from group: microsoft>public>exchange>applications

Siegfried Weber [MVP] · Joined: 05 Aug 2007 Posts: 2

"Andrew G" wrote in message:
>
> This is doable. I do something similar with Event Service scripts
attached
> to public folders. I must stress that I think this is only reliable, from
> what I have read, for low volume activity. There are delays in scripts
> executing, and I'm not sure whether scripts are run one at or time or
there
> is some scope for parallel running. (Anyone want to answer this one?)

Neither the Exchange 5.5 Scripting Agent architecture nor the Exchange
2000/2003 Store Event Sink architecure execute scripting based event code
parallel.

--

Development Lead,

CDOLive LLC - The Microsoft Messaging and Collaboration Application Experts
http://www.cdolive.com

This information is provided "AS IS" with no warranties, and confers no
rights.
You assume all risk for your use. © 2003 CDOLive LLC. All rights reserved.
Sample code subject to http://www.cdolive.com/disclaim.htm

Andrew G · Joined: 05 Aug 2007 Posts: 3

Hi Justin,

This is doable. I do something similar with Event Service scripts attached
to public folders. I must stress that I think this is only reliable, from
what I have read, for low volume activity. There are delays in scripts
executing, and I'm not sure whether scripts are run one at or time or there
is some scope for parallel running. (Anyone want to answer this one?)

An email is sent to a Public Folder. On a new message arriving in the
folder a script is executed. My particular application has attached files
being read and their data being uploaded to a database. In writing my
scripts I've put most of the business logic in DLL's. I create instances of
these from the script and set the appropriate properties and execute the
appropriate methods.

My applications are very low volume - a dozen emails a day to each folder.
I don't know how applicable it is to your problem.

You can get SQL Server to send emails. There is a stored procedure for
doing this. A bit fiddly to set up but it works.

Have a look at www.cdolive.com as one great reference on using Exchange's
Event Service.

Something else to look at. SQL Server can receive email but as I understand
it only processes valid SQL statements and then returns the results to the
original sender. Check your Books Online for sp_processmail.

Good luck

"Justin Mosier" wrote in message$65ca0520$a501280a@phx.gbl...
> Does anyone know how to query a database (SQL Server) via
> email (through Exchange)?
>
> I'm in the process of proposing a web application that
> stores vendor information in a SQL Server database. I not
> only want to provide an HTML interface to search vendors,
> I also want to provide easy access for mobile devices via
> a simply email query. I have no idea if email query is
> possible with Exchange or how to set it up on the mail
> server.
>
> The idea is someone could send an email
> to "vendorsearch@mycompany.com" with the search criteria
> in the body of the email, such as "48180" for a zip code.
> The email would be processed by Exchange, identified as a
> query search, and forwarded on to a web server (perhaps
> by invoking an HTTP call) or to the SQL server to lookup
> the data. Then an email would be sent back to the user
> with the results in a text email. Sounds simple, huh?
>
> Is this possible, if so how, or is this pie in the sky?
>
> Thanks!

Justin Mosier · Joined: 05 Aug 2007 Posts: 2

Thanks for the great info and for responding so quickly!

Indeed my application would be low volume. I'm thinking
instead though of simply having the application server
(ColdFusion web server) query the mail server for new
query emails. Then ColdFusion can parse the email for the
search criteria, submit a SQL query to get the data from
the database server and email the original requestor.

Thanks,
Justin

>-----Original Message-----
>Hi Justin,
>
>This is doable. I do something similar with Event
Service scripts attached
>to public folders. I must stress that I think this is
only reliable, from
>what I have read, for low volume activity. There are
delays in scripts
>executing, and I'm not sure whether scripts are run one
at or time or there
>is some scope for parallel running. (Anyone want to
answer this one?)
>
>An email is sent to a Public Folder. On a new message
arriving in the
>folder a script is executed. My particular application
has attached files
>being read and their data being uploaded to a database.
In writing my
>scripts I've put most of the business logic in DLL's. I
create instances of
>these from the script and set the appropriate properties
and execute the
>appropriate methods.
>
>My applications are very low volume - a dozen emails a
day to each folder.
>I don't know how applicable it is to your problem.
>
>You can get SQL Server to send emails. There is a
stored procedure for
>doing this. A bit fiddly to set up but it works.
>
>Have a look at www.cdolive.com as one great reference on
using Exchange's
>Event Service.
>
>Something else to look at. SQL Server can receive email
but as I understand
>it only processes valid SQL statements and then returns
the results to the
>original sender. Check your Books Online for
sp_processmail.
>
>Good luck
>
>

Andrew G · Joined: 05 Aug 2007 Posts: 3

From my experience with using the Exchange Event Service your solution could
be more robust. Another recent post of mine asks for help as to why the
events just stop firing, even in the absence of script errors.

I've always been a fan of the pull approach rather than push. That is let
your process go and get the data it wants rather than wait for it to arrive.

"Justin Mosier" wrote in message$8f5a7120$a501280a@phx.gbl...
> Thanks for the great info and for responding so quickly!
>
> Indeed my application would be low volume. I'm thinking
> instead though of simply having the application server
> (ColdFusion web server) query the mail server for new
> query emails. Then ColdFusion can parse the email for the
> search criteria, submit a SQL query to get the data from
> the database server and email the original requestor.
>
> Thanks,
> Justin
>
> >-----Original Message-----
> >Hi Justin,
> >
> >This is doable. I do something similar with Event
> Service scripts attached
> >to public folders. I must stress that I think this is
> only reliable, from
> >what I have read, for low volume activity. There are
> delays in scripts
> >executing, and I'm not sure whether scripts are run one
> at or time or there
> >is some scope for parallel running. (Anyone want to
> answer this one?)
> >
> >An email is sent to a Public Folder. On a new message
> arriving in the
> >folder a script is executed. My particular application
> has attached files
> >being read and their data being uploaded to a database.
> In writing my
> >scripts I've put most of the business logic in DLL's. I
> create instances of
> >these from the script and set the appropriate properties
> and execute the
> >appropriate methods.
> >
> >My applications are very low volume - a dozen emails a
> day to each folder.
> >I don't know how applicable it is to your problem.
> >
> >You can get SQL Server to send emails. There is a
> stored procedure for
> >doing this. A bit fiddly to set up but it works.
> >
> >Have a look at www.cdolive.com as one great reference on
> using Exchange's
> >Event Service.
> >
> >Something else to look at. SQL Server can receive email
> but as I understand
> >it only processes valid SQL statements and then returns
> the results to the
> >original sender. Check your Books Online for
> sp_processmail.
> >
> >Good luck
> >
> >
>