June 9, 2008 at 11:44 pm
Hello all,
Can we schedule a job to run a query everyday and get its result set via email in SQL2005 SP2.
if so, could you please let me know.
Thanks in advance.
June 10, 2008 at 12:07 am
Hi Ziljan4,
you can do both:
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'Ziljan4@mail.com',
@body='Message Body',
@subject ='Message Subject',
@profile_name ='DatabaseMailProfile',
@query ='SELECT Product FROM sb2..SalesHistory GROUP BY Product HAVING COUNT(*) > 3',
@attach_query_result_as_file = 1,@query_attachment_filename ='Results.txt'
or in html :
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT Product AS 'td','',SUM(SalePrice) AS 'td'
FROM SalesHistory GROUP BY Product FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body =' '
SET @body = @body + @xml +' '
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'Ziljan4@mail.com',
@body = @body,
@body_format ='HTML',
@subject ='Message Subject',
@profile_name ='DatabaseMailProfile'
[font="Verdana"]CU
tosc[/font]
June 10, 2008 at 12:36 am
Thank you so much for your reply.
I tried to use the script but it gave me the following error.
Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid
Any suggesstion?
Thanks
June 10, 2008 at 12:46 am
Hi
run the Configure Database Mail Wizard. Then select "Manage Database Mail accounts and profiles" option in the Select Configuration Task - "manage database mail accounts and profiles" Then select, "view change delete existing account". In the manage existing account screen, choose from existing accounts to manage from the dropdown list.
Be sure, that you use the right @profile_name!
[font="Verdana"]CU
tosc[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply