Sending E-Mail messages to a SQL Server

Sending E-Mail messages to a SQL Server

Post by Shaunes » Sun, 30 Dec 2001 13:59:35



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
 
 
 

Sending E-Mail messages to a SQL Server

Post by Narayana Vyas Kondredd » Mon, 31 Dec 2001 01:00:06


Carl, you can start a job using msdb..sp_start_job. There is no email
involved in this process.
--
HTH,
Vyas,
SQL Server FAQ, articles, code samples,

http://vyaskn.tripod.com/


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


 
 
 

Sending E-Mail messages to a SQL Server

Post by Carl Bent » Mon, 31 Dec 2001 03:44:00


Thanks for the comment, Vyas. I need to expand the
explanation a bit more as to what I need to
accomplish.
I am the DBA for the company, but as things go,
there are a number of other things that I get to
do as well! Such is life!

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.

 
 
 

Sending E-Mail messages to a SQL Server

Post by Narayana Vyas Kondredd » Mon, 31 Dec 2001 20:26:21


Hello there, if I understand you right, your requirement still boils
down to executing a DTS package on demand, preferably by sending an
email.

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!

 
 
 

1. Sending messages from SQL Server without SQL Mail

Hi,

   We are using SQL 2000 on Win  2000 Server. We are at SP2 for windows 2000
and SP2 for SQL 2000. We  have a requirement wherein, We have to send a mail
or a page to a set of addresses if the database bacup job fails. This has to
accomplished without configuring or using SQL Mail.  I was taking a look at
the KB articles about achieving this. I found a very helpful KB article
titled 'HOW TO: Send E-Mail Without Using SQL Mail in SQL Server' which
suits my requirement just fine. As per this article it is achieved thru a
stored procedure created on Master db. But to tailor the code to suit my
situations, I think, I have to change the following lines in SP code that is
in the KB article:

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdos...

'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendus
ing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.

'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").Value', 'MailServerName'
----------------------------------------------------------------------------
----------------------------------------------------------------------------
---

I just wated to make sure I am proceeding in the right direction or not. As
per my understanding the only change that I have to make is to replace the
string 'MailServerName' with a fully qualified servername or IP address of
the SMTP server that I will be using for the purpose of sending the mail in
the line underlined. I wanted to get opinions and feedback from guys having
tried this solution successfully. An early response would be very much
appreciated.

Thanks

Raghuveer S

2. Package Lineage

3. can sql mail send html e-mail messages?

4. Pasted text is smaller than should be based on text property of field?

5. NT Windows Messaging and network SMTP server (sending email to Lotus Notes server)

6. How can I create a database

7. Send E-Mail Without Using SQL Mail in SQL Server (Q312839)

8. DTS - ActiveX Control

9. Send E-Mail Without Using SQL Mail in SQL Server

10. SQL Mail send Internet Email

11. How to write PL/SQL to send email message

12. SQL Mail: How I send a email in Htlm

13. sql mail not sending or picking up new messages