Very Computer

Quote:> I would like to send a message to a SQL Server as a signal
> to initiate a DTS package  or to start a pre-defined job. I
> know that sp_processmail is involved, but so far have not
> been able to get enough of the pieces together to understand
> how to make it all work for me. Can anyone provide some
> ideas?  Carl B

Specifically to this question, there are a couple
of things that the accounting department askes me
to do regularly, although not so
regularly that they can be "scheduled." One is
definitely a SQL Server type function - extracting
some information from an AS-400
application database into SQL Server - using a DTS
package. The accounting staff then uses Access to
do manipulation of the
data for whatever purposes.  Admittedly, they
could probably deal with a direct connection
between Access and the AS-400, but
using SQL Server and DTS places a security layer
between that was deemed preferable. At this point,
they have to track me down
to launch the DTS package. No BIG problem, as I am
usually there, but on occasion, if I am tied up in
a meeting, or out of the office,
they must wait until I return to get the transfer
of data from the AS-400.

The other function is more fun. We want to
transfer a spread sheet from our main office to
several other offices, using the DTS
e-mail function to let us know if it arrived
successfully or not. Again, I am usually there and
can start the DTS operation, but . . . .
[And then there's the problem of everyone having
rights to write a file to servers scattered in
other areas of the country.]

In as much as SQL Server is supposed to be able to
receive e-mails, and in as much as using the DTS
breaks the security rights
chain to some extent, and because I believe this
could prove quite useful for a number of other
things if I can make it work,
what I would like to understand how to do is be
able to send an email with a specific subject and
specific message to one of the
servers, that server would in turn launch the
appropriate DTS package for the job requested by
the message.

In part, I don't know how to structure the
message, etc., to pull something like this off.
That's what I'm looking for help to do.

My approach:
I will eliminate the email from this process. Just create a package, add
a schedule to it, so that a job gets created. Now disable the schedule,
so that the DTS package never runs on its own. Create a stored procedure
that starts a given job using msdb..sp_start_job. Create a VB app or an
ASP page that passes the required job name (that we created earlier) to
this stored procedure. Let your users execute this VB app or ASP page
whenever they want. This app inturn calls the stored procedure and the
procedure executes the job and the DTS package runs eventually. Hope
this is not confucing :)

Your approach:
Yes, you can use email. Just use the email stored procedures like
sp_processmail, sp_readmail to read the incoming mail, and take
necessary actions based on the subject or body of the mail. I haven't
tried this, but it should work. Books Online has more information on
these email handling stored procedures.

Good luck!
--
HTH,
Vyas
SQL Server FAQ, articles, code samples,

http://vyaskn.tripod.com/

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!