May 29, 2002 at 10:09 am
I would like to have an email sent every Friday and Monday in SQL 7.0 with results from a query. How and what do I use in SQL to do this?
May 29, 2002 at 10:15 am
Hey,
Firstly you need to have the mail task configured within SQL Server and running. (green arrow will confirm it's running). Once that is done, create a new scheduled job, add a new step and in the command string type the Send mail code directly or call a previously written Stored Proc.
You will need to use the xp_sendmail stored proc to send eMails. For information on xp_sendmail, see BOL. Search on "xp_sendmail".
Clive
Clive Strong
May 29, 2002 at 10:28 am
I've setup SQL Mail and also setup SQL Server Agent to use email. That's what I thought I was supposed to do. I created a new job. Then created a step using TSQL with the following in the command:
EXEC xp_sendmail @recipients = 'roger',
@query = 'SELECT * FROM table1 WHERE empid NOT IN
(SELECT empid FROM table2 WHERE dated = '5/25/02')
ORDER BY last ASC',
@subject = 'Unapproved Timesheets'
Then I schedule it the way I want it to run and save it. Then when it runs it fails. Now to test if the job was setup correctly I changed the step and just put a sql statment in the command field and it ran fine. So is something wrong with the way I'm using xp_sendmail?
May 29, 2002 at 10:43 am
Hey,
Use....
EXEC master.dbo.xp_sendmail
and also use the full path when referencing your tables in the select query.
Clive
Clive Strong
May 29, 2002 at 12:51 pm
Well I did the suggestions but now it just hangs on Performing Completion Actions. This is what I'm putting in the command field:
exec master.dbo.xp_sendmail @recipients = 'Muscarello Roger',
@subject = 'Unapproved Timesheet',
@message = 'You need to complete and approve your timesheet.;'
In history nothing shows up but under the Last Run Status column it says it failed. What does this mean? I'm not receiving the email either.
May 29, 2002 at 2:25 pm
Now I get: Could not find stored procedure 'master.dbo.xp_sendmail'. [SQLSTATE 42000] (Error 2812). The step failed.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply