Auto Email Database results

Question

I need some help conceptualizing a project... I have to run 4 different queries and send the results as the body of an email to defined recipients.
The problem is I will need to automate this process as I need to send the results every morning at 9am..., My initial thought was to just setup a job in SQL Server2000 and let that job to email the results, however this particular database is in SQL Server 2000..., So then thought I could create a C# or Visual Basic program and use windows scheduler to run and email the reports, however I again come back to the fact that it is SQL Server 2000 and there is not a Stored Procedure to send mail.
I was able to find a Send Mail stored procedure online, but then couldn't figure out how to attach results to a parameter. Any insight on how others would handle this would be greatly appreciated.

Thanks,

AJ

Answer 1

SQL 2000 does have jobs.

http://msdn.microsoft.com/en-us/library/aa215382(v=sql.80).aspx

How to create a job (Transact-SQL) To create a job

1.Execute sp_add_job to create a job.

2.Execute sp_add_jobstep to create one or more job steps.

3.Execute sp_add_jobschedule to create a job schedule.

Email in SQL 2000 can be done via Outlook, but it's kind of a pain... Blat is free and does not need Outlook or POP3.

To avoid attachments and present a nice looking email, simply concat your row data into an HTML table and assign the result to the body. If you need more than 8000 characters, you'll have to use the text data type, but might be somewhat difficult

declare @result varchar(8000)
set @result = '<table>'

select @result = @result + '<tr><td>' + col1 + '</td><td>' + col2 + '</td></tr>'
from whereever
where something = something_else
order by 1

set @result = @result + '</table>'

http://www.blat.net/

Sample code: http://www.devx.com/dbzone/Article/42178

Exec stp_BlatMail 'ServerName', 'fhtapia@gmail.com',
'System Maintenance: Low Memory', 'D:\Data\TempFiles\MemoryRpt.txt'



CREATE PROCEDURE stp_BlatMail (@FromName AS VARCHAR (1000), @To AS VARCHAR(1000),
@Subject AS VARCHAR(1000), @BODY AS VARCHAR(8000),
@ATTACHMENTS AS VARCHAR(1024) = NULL) AS
-- By: Francisco H Tapia <fhtapia@gmail.com>
-- Date: 8/19/2003
-- Purpose: Provide Outlook free SMTP Mailing
SET NOCOUNT ON
DECLARE @CMD as VARCHAR(8000), @result as INT
IF @TO = ''
BEGIN
SELECT 'ERROR: NO TO Name'
END
ELSE
BEGIN
SELECT @CMD = ' D:\Data\Common\blat.exe - -subject "'+ @Subject
+ '" -t "' + @To
+ '" -sender "SystemUID@Domain.com" -from "'
+ @FromName
+'" -f "SQLMail" -ReplyTo "SystemUID@Domain.com" -org "My Company Name" -x "X-INFO: " -noh
-noh2 -server "ExchangeServerName" -port 25 -u EmailUID -pw Password -body "'
+ LTRIM(RTRIM(@Body)) + '" '
+ ' -q '
If @ATTACHMENTS <> ''
BEGIN
SELECT @CMD = @CMD + ' -attach "' + @ATTACHMENTS + '" '
END
ELSE IF @Attachments IS NOT NULL
BEGIN
SELECT 'NO ATTACHMENT FOUND'
END
EXEC @result = master..xp_cmdShell @CMD, NO_OUTPUT
END
SET NOCOUNT OFF