Emailing SQL 2000 maintenance report via SMTP?

Hi All
As per my post in microsoft... server NG, I keep getting a problem where my
DB maintenance plan just stops doing what it should, which in turn means
that if I don't paranoid-ly check it each day I get the scenario (like
today) where I find my backups haven't been working for a month!!!
What I would like to do is email the maintenance plan to a designated email
address so that I can easily see every morning whether or not it has worked.
I know the old SQL 6.5 used to provide SQL Mail, but this links in with MS
Exchange Server and I don't use this. I don't even have it running on my
SBS 2000 Server.
Is there anyway in SQL 2000 that I can get it to email the report via SMTP?
I'm familiar with JMail and it's COM/DLL is installed on my server if I
could use that.
Thanks RobbieFirst of all, can I suggest that you not use the maintenance plan for
backups? You can create jobs within the SQL Server Agent that will backup
your databases. Books Online (within the SQL Server program group) contains
T-SQL examples of backup scripts. If you are so inclined you can also
create jobs that perform the appropriate maintenance tasks (such as checkdb,
checkcatalog, updateusage, update statistics).
You could use XPSMTP http://sqldev.net/xp/xpsmtp.htm to send email alerts.
Keith
"Astra" <info@.noemail.com> wrote in message
news:uXPyFCzGFHA.2452@.TK2MSFTNGP10.phx.gbl...
> Hi All
> As per my post in microsoft... server NG, I keep getting a problem where
my
> DB maintenance plan just stops doing what it should, which in turn means
> that if I don't paranoid-ly check it each day I get the scenario (like
> today) where I find my backups haven't been working for a month!!!
> What I would like to do is email the maintenance plan to a designated
email
> address so that I can easily see every morning whether or not it has
worked.
> I know the old SQL 6.5 used to provide SQL Mail, but this links in with MS
> Exchange server and I don't use this. I don't even have it running on my
> SBS 2000 Server.
> Is there anyway in SQL 2000 that I can get it to email the report via
SMTP?
> I'm familiar with JMail and it's COM/DLL is installed on my server if I
> could use that.
> Thanks Robbie
>|||Hi
Check out http://www.sqldev.net/xp/xpsmtp.htm
Other possible mechanisms might mbe Notification Services or Reporting
Services.
John
"Astra" wrote:

> Hi All
> As per my post in microsoft... server NG, I keep getting a problem where
my
> DB maintenance plan just stops doing what it should, which in turn means
> that if I don't paranoid-ly check it each day I get the scenario (like
> today) where I find my backups haven't been working for a month!!!
> What I would like to do is email the maintenance plan to a designated emai
l
> address so that I can easily see every morning whether or not it has worke
d.
> I know the old SQL 6.5 used to provide SQL Mail, but this links in with MS
> Exchange server and I don't use this. I don't even have it running on my
> SBS 2000 Server.
> Is there anyway in SQL 2000 that I can get it to email the report via SMTP
?
> I'm familiar with JMail and it's COM/DLL is installed on my server if I
> could use that.
> Thanks Robbie
>
>|||Check out http://www.sqldev.net/xp.htm
HTH...
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
Keith Kratochvil wrote:
> First of all, can I suggest that you not use the maintenance plan for
> backups? You can create jobs within the SQL Server Agent that will backup
> your databases. Books Online (within the SQL Server program group) contai
ns
> T-SQL examples of backup scripts. If you are so inclined you can also
> create jobs that perform the appropriate maintenance tasks (such as checkd
b,
> checkcatalog, updateusage, update statistics).
> You could use XPSMTP http://sqldev.net/xp/xpsmtp.htm to send email alerts.
>|||Thanks Guys
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:2E065D49-E910-48D1-8B7D-1B4FFB55DA73@.microsoft.com...
Hi
Check out http://www.sqldev.net/xp/xpsmtp.htm
Other possible mechanisms might mbe Notification Services or Reporting
Services.
John
"Astra" wrote:

> Hi All
> As per my post in microsoft... server NG, I keep getting a problem where
> my
> DB maintenance plan just stops doing what it should, which in turn means
> that if I don't paranoid-ly check it each day I get the scenario (like
> today) where I find my backups haven't been working for a month!!!
> What I would like to do is email the maintenance plan to a designated
> email
> address so that I can easily see every morning whether or not it has
> worked.
> I know the old SQL 6.5 used to provide SQL Mail, but this links in with MS
> Exchange server and I don't use this. I don't even have it running on my
> SBS 2000 Server.
> Is there anyway in SQL 2000 that I can get it to email the report via
> SMTP?
> I'm familiar with JMail and it's COM/DLL is installed on my server if I
> could use that.
> Thanks Robbie
>
>|||Keith,
I have found this useful as well, so thanks for the tip.
I am having difficulty putting in carriage returns and linefeeds and
wondered if you could help.
This works (names changed!):
exec master.dbo.xp_smtp_sendmail
@.FROM = 'Fred@.mail.com',
@.TO = 'Jim@.mail.com',
@.subject = 'Hello SQL Server SMTP Mail',
@.message = 'Goodbye MAPI and Outlook
@.server = 'ServerIPAddress'
But when I try and add a carriage return and line feed I get 'Incorrect
syntax near '+'' error:
exec master.dbo.xp_smtp_sendmail
@.FROM = 'Fred@.mail.com',
@.TO = 'Jim@.mail.com',
@.subject = 'Hello SQL Server SMTP Mail',
@.message = 'Goodbye MAPI and Outlook' + CHAR(13) + CHAR(10),
@.server = 'ServerIPAddress'
I would welcome some help.
Thanks,
Adrian
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:Oia5I9zGFHA.1172@.TK2MSFTNGP12.phx.gbl...
> First of all, can I suggest that you not use the maintenance plan for
> backups? You can create jobs within the SQL Server Agent that will backup
> your databases. Books Online (within the SQL Server program group)
contains
> T-SQL examples of backup scripts. If you are so inclined you can also
> create jobs that perform the appropriate maintenance tasks (such as
checkdb,
> checkcatalog, updateusage, update statistics).
> You could use XPSMTP http://sqldev.net/xp/xpsmtp.htm to send email alerts.
> --
> Keith
>
> "Astra" <info@.noemail.com> wrote in message
> news:uXPyFCzGFHA.2452@.TK2MSFTNGP10.phx.gbl...
where
> my
> email
> worked.
MS
my
> SMTP?
>|||On Fri, 25 Feb 2005 16:33:12 -0000, Adrian wrote:
(snip)
>But when I try and add a carriage return and line feed I get 'Incorrect
>syntax near '+'' error:
>exec master.dbo.xp_smtp_sendmail
> @.FROM = 'Fred@.mail.com',
> @.TO = 'Jim@.mail.com',
> @.subject = 'Hello SQL Server SMTP Mail',
> @.message = 'Goodbye MAPI and Outlook' + CHAR(13) + CHAR(10),
> @.server = 'ServerIPAddress'
>I would welcome some help.
Hi Adrian,
Try this:
DECLARE @.msg varchar(40)
SET @.msg = 'Goodbye MAPI and Outlook' + CHAR(13) + CHAR(10)
exec master.dbo.xp_smtp_sendmail
@.FROM = 'Fred@.mail.com',
@.TO = 'Jim@.mail.com',
@.subject = 'Hello SQL Server SMTP Mail',
@.message = @.msg,
@.server = 'ServerIPAddress'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||CHAR(13) + CHAR(10)
should do it
...
select 'test' + CHAR(13) + CHAR(10) + 'ing'
Keith
"Adrian" <NoSpam@.hotmail.com> wrote in message
news:uMM70e1GFHA.588@.TK2MSFTNGP10.phx.gbl...
> Keith,
> I have found this useful as well, so thanks for the tip.
> I am having difficulty putting in carriage returns and linefeeds and
> wondered if you could help.
> This works (names changed!):
> exec master.dbo.xp_smtp_sendmail
> @.FROM = 'Fred@.mail.com',
> @.TO = 'Jim@.mail.com',
> @.subject = 'Hello SQL Server SMTP Mail',
> @.message = 'Goodbye MAPI and Outlook
> @.server = 'ServerIPAddress'
> But when I try and add a carriage return and line feed I get 'Incorrect
> syntax near '+'' error:
> exec master.dbo.xp_smtp_sendmail
> @.FROM = 'Fred@.mail.com',
> @.TO = 'Jim@.mail.com',
> @.subject = 'Hello SQL Server SMTP Mail',
> @.message = 'Goodbye MAPI and Outlook' + CHAR(13) + CHAR(10),
> @.server = 'ServerIPAddress'
> I would welcome some help.
> Thanks,
> Adrian
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:Oia5I9zGFHA.1172@.TK2MSFTNGP12.phx.gbl...
backup
> contains
> checkdb,
alerts.
> where
means
with
> MS
> my
I
>|||Thanks Keith and Hugo.
It works now.
Adrian
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:u00Ti51GFHA.2936@.TK2MSFTNGP15.phx.gbl...
> CHAR(13) + CHAR(10)
> should do it
> ...
> select 'test' + CHAR(13) + CHAR(10) + 'ing'
> --
> Keith
>
> "Adrian" <NoSpam@.hotmail.com> wrote in message
> news:uMM70e1GFHA.588@.TK2MSFTNGP10.phx.gbl...
> backup
> alerts.
> means
(like
designated
> with
on
via
if
> I
>sql