Alternatives to SQL Mail?

(SQL Server 2000, SP3a)
Hello all!
We've got a development project that is using on SQL Mail (xp_sendmail) that we're moving
to production . Our production environment is configured in such a way that we want to
avoid using SQL Mail. I see a commonly recommended alternative is the xp_smtp_sendmail
.DLL. However, as noted by the documentation from: http://www.sqldev.net/xp/xpsmtp.htm
<Quote>
The current version does not provide @.query support like the one found in SQL Mail
</Quote>
Naturally, the way that we're using SQL Mail is reliant upon the @.query parameter. ;-)
Are there any other alternatives, or perhaps someone has extended/wrapped the
xp_smtp_sendmail to provide more functionality? Basically, we use the SQL Mail with the
@.query to invoke a query that will then attach the results as a comma-separated (.CSV)
file. If such a thing exists, I'd be greatly obliged to learn of it!
Thanks!
John PetersonCan't you run the query and output it to a text file (e.g. see a couple of
options at http://www.aspfaq.com/2482) and then just attach the file? In
this way, you could have greater control over the output, as well...
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eIBS1pBoDHA.3024@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> We've got a development project that is using on SQL Mail (xp_sendmail)
that we're moving
> to production . Our production environment is configured in such a way
that we want to
> avoid using SQL Mail. I see a commonly recommended alternative is the
xp_smtp_sendmail
> .DLL. However, as noted by the documentation from:
http://www.sqldev.net/xp/xpsmtp.htm
> <Quote>
> The current version does not provide @.query support like the one found in
SQL Mail
> </Quote>
> Naturally, the way that we're using SQL Mail is reliant upon the @.query
parameter. ;-)
> Are there any other alternatives, or perhaps someone has extended/wrapped
the
> xp_smtp_sendmail to provide more functionality? Basically, we use the SQL
Mail with the
> @.query to invoke a query that will then attach the results as a
comma-separated (.CSV)
> file. If such a thing exists, I'd be greatly obliged to learn of it!
> Thanks!
> John Peterson
>|||Thanks, Aaron!
I guess I was looking for something as convenient as xp_sendmail, with which to execute
the query, format it as a comma-separated file, and send it as an email attachment all in
one fell swoop. :-)
With the link you provided -- I suppose I could "roll my own", but I had hoped someone had
already done all the leg work. ;-)
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23YGc6vBoDHA.2140@.TK2MSFTNGP09.phx.gbl...
> Can't you run the query and output it to a text file (e.g. see a couple of
> options at http://www.aspfaq.com/2482) and then just attach the file? In
> this way, you could have greater control over the output, as well...
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:eIBS1pBoDHA.3024@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > We've got a development project that is using on SQL Mail (xp_sendmail)
> that we're moving
> > to production . Our production environment is configured in such a way
> that we want to
> > avoid using SQL Mail. I see a commonly recommended alternative is the
> xp_smtp_sendmail
> > .DLL. However, as noted by the documentation from:
> http://www.sqldev.net/xp/xpsmtp.htm
> >
> > <Quote>
> > The current version does not provide @.query support like the one found in
> SQL Mail
> > </Quote>
> >
> > Naturally, the way that we're using SQL Mail is reliant upon the @.query
> parameter. ;-)
> >
> > Are there any other alternatives, or perhaps someone has extended/wrapped
> the
> > xp_smtp_sendmail to provide more functionality? Basically, we use the SQL
> Mail with the
> > @.query to invoke a query that will then attach the results as a
> comma-separated (.CSV)
> > file. If such a thing exists, I'd be greatly obliged to learn of it!
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>|||> With the link you provided -- I suppose I could "roll my own", but I had
hoped someone had
> already done all the leg work. ;-)
Well, I've been meaning to update http://www.aspfaq.com/2403 with such a
technique, because that's the only complaint I've ever heard about
xp_smtp_sendmail. Sounds like you will get to it before I do, so feel free
to send along any examples you can provide; I'll add them to the article,
and credit you of course.
Cheers,
Aaron|||"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eIBS1pBoDHA.3024@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> We've got a development project that is using on SQL Mail (xp_sendmail)
that we're moving
> to production . Our production environment is configured in such a way
that we want to
> avoid using SQL Mail. I see a commonly recommended alternative is the
xp_smtp_sendmail
> .DLL. However, as noted by the documentation from:
http://www.sqldev.net/xp/xpsmtp.htm
> <Quote>
> The current version does not provide @.query support like the one found in
SQL Mail
> </Quote>
> Naturally, the way that we're using SQL Mail is reliant upon the @.query
parameter. ;-)
> Are there any other alternatives, or perhaps someone has extended/wrapped
the
> xp_smtp_sendmail to provide more functionality? Basically, we use the SQL
Mail with the
> @.query to invoke a query that will then attach the results as a
comma-separated (.CSV)
> file. If such a thing exists, I'd be greatly obliged to learn of it!
> Thanks!
> John Peterson
>
Is this an email that is being invoked by a trigger, or a scheduled task?
If it's not a trigger, ie. you run it every hour, day or week then we
actually wrote a VB Script that creates an email using CDO (Collaboration
Data Objects) with a really great HTML body (that is larger than 8000
characters - the main reason for using VB script) and added it as a
scheduled task. Works really well but a lot of work to do something that
should be relatively simple.
NEIL.
ps: If you want the source I'll happily divulge it to you.