December 29, 2011 at 1:03 pm
Hi Folks
I am looking for to setup like need to get query result in mail.
I have query which give the sql agent job history . i want to setup like the result of query send through in e mail with excel format . If you have better query which works with my scenario please post me .
thanks and have a good day.
December 29, 2011 at 1:18 pm
Do you have an SSRS instance available to you?
Could always set up a subscription.
December 29, 2011 at 1:30 pm
Yes i do have SSIS ,SSRS both.
December 29, 2011 at 1:34 pm
I would toss that query into a simple SSRS report and then set up a subscription with email delivery and excel render type.
Should get you what you are wanting.
December 29, 2011 at 1:59 pm
i did try with SSIS but iw as getting blank e-mail. did not try with SSRS. if you help me in detail so can try with SSRS.
Thanks
December 29, 2011 at 2:03 pm
First step would be to build the report and deploy it.
Verify that you can run it manually on the report server.
After that, you'll be ready to go in and create a subscription.
I would Google how to create a subscription in SSRS, should give you what you need.
December 29, 2011 at 2:06 pm
Wouldn't it be a lot easier to use sp_send_dbmail?
For the @query_attachment_filename parameter, use a xls file extension.
December 29, 2011 at 2:08 pm
True.
I tend to err on the side of more customizable presentation.
December 29, 2011 at 2:29 pm
I tired with this option but i am getting blank excel file in mail.
December 29, 2011 at 2:34 pm
logicinside22 (12/29/2011)
I tired with this option but i am getting blank excel file in mail.
Does the query return any result at all when executed using the same query window as the sp_send_dbmail?
It might not be an issue of the mail task...
December 29, 2011 at 2:38 pm
Here is a short tutorial how to get the query results via email.
http://jasonbrimhall.info/2011/08/15/send-dbmail/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 29, 2011 at 3:38 pm
LutzM (12/29/2011)
Wouldn't it be a lot easier to use sp_send_dbmail?For the @query_attachment_filename parameter, use a xls file extension.
Something simple like this should work, if your file is blank there's another issue
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'mydoggiejessie@ssc.com',
@execute_query_database='MyDB',
@query_attachment_filename='MyTest.txt',
@query = '
SELECT * FROM dbo.MyTable',
@subject = 'My File Attachment Test',
@attach_query_result_as_file = 1 ;
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 30, 2011 at 7:19 am
Thanks for the link SQL RNNR.
I am trying with the query explained on your blog to execute SP but keep saying like this
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504
Query execution failed: Msg 2812, Level 16, State 62, Server M102034, Line 1
Could not find stored procedure 'JobSummaryUtil'.
though that SP is already there.
Any idea?
December 30, 2011 at 8:08 am
please post the exact query you are using.
If it is saying it can't find the proc, try calling it with three part naming (db.schema.proc).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 30, 2011 at 8:13 am
The Below Procedure i am using to get Job Summary. but i want to get result of this proc in e-mail ( Excel File would be better).
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[JobSummaryUtil] Script Date: 12/30/2011 10:12:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[JobSummaryUtil]
@JobName VARCHAR(255) = null, -- Optional job name filter
@ShowDisabled bit = 0, -- Include disabled jobs?
@ShowUnscheduled bit = 0, -- Include Unscheduled jobs?
@JobThresholdSec INT = 0, -- If positive, show only the jobs with LAST duration above this.
@AvgExecThresholdSec INT = 0 -- If positive, show only the jobs with AVERAGE duration above this.
AS
SELECT *
FROM
(
SELECT JobName, ISNULL(LastStep,'') LastStep,
CASE WHEN StartDate IS NOT NULL AND FinishDate IS NULL THEN 'Running'
WHEN Enabled = 0 THEN 'Disabled'
WHEN StepCount = 0 THEN 'No steps'
WHEN RunStatus IS NOT NULL THEN RunStatus
WHEN ScheduleCount = 0 THEN 'Not scheduled'
ELSE 'UNKNOWN' END Info,
DatabaseName, Enabled, ScheduleCount, StepCount,
StartDate, FinishDate, DurationSec,
RIGHT('0'+convert(varchar(5),DurationSec/3600),2)+':'+RIGHT('0'+convert(varchar(5),DurationSec%3600/60),2)+':'+ RIGHT('0'+convert(varchar(5),(DurationSec%60)),2) DurationSecFormatted,
avgDurationSec,
RIGHT('0'+convert(varchar(5),avgDurationSec/3600),2)+':'+RIGHT('0'+convert(varchar(5),avgDurationSec%3600/60),2)+':'+ RIGHT('0'+convert(varchar(5),(avgDurationSec%60)),2) avgDurationSecFormatted,
CASE WHEN (DurationSec IS NULL OR ISNULL(avgDurationSec, 0) = 0) THEN 0 ELSE CONVERT(DECIMAL(18,2), (100*CAST(DurationSec AS DECIMAL)) / CAST (avgDurationSec as DECIMAL)) END AS DurationRatio,
NextRunDate,
StepCommand,
HistoryMessage
FROM
(
SELECT j.name JobName,j.enabled Enabled,
(select COUNT(1) from msdb..sysjobschedules jss where jss.job_id = j.job_id) ScheduleCount,
(select COUNT(1) from msdb..sysjobsteps jps where jps.job_id = j.job_id) StepCount,
ls1.job_history_id HistoryID,
ls1.start_execution_date StartDate,
ls1.stop_execution_date FinishDate,
ls1.last_executed_step_id LastStepID,
DATEDIFF(SECOND, ls1.start_execution_date, CASE WHEN ls1.stop_execution_date IS NULL THEN GETDATE() ELSE ls1.stop_execution_date END) DurationSec,
ISNULL(avgSec, 0) avgDurationSec,
ls1.next_scheduled_run_date NextRunDate,
st.step_name LastStep, st.command StepCommand, st.database_name DatabaseName,
h.message HistoryMessage,
CASE WHEN h.job_id IS NULL THEN 'Never Run' ELSE
CASE h.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled' END END RunStatus,
h.run_date rawRunDate,
h.run_time rawRunTime,
h.run_duration rawRunDuration
FROM msdb..sysjobactivity ls1 (NOLOCK)
INNER JOIN msdb..sysjobs j (NOLOCK) ON ls1.job_id = j.job_id
INNER JOIN
(
SELECT job_id JobID, MAX(session_id) LastSessionID
FROM msdb..sysjobactivity (NOLOCK)
GROUP BY job_id
) ls2 ON ls1.job_id = ls2.JobID and ls1.session_id = ls2.LastSessionID
LEFT OUTER JOIN msdb..sysjobsteps st (NOLOCK) ON st.job_id = j.job_id and ls1.last_executed_step_id = st.step_id
LEFT OUTER JOIN msdb..sysjobhistory h (NOLOCK) ON h.instance_id = ls1.job_history_id
LEFT OUTER JOIN
(
SELECT j.job_id JobID, SUM(h.avgSecs) avgSec
FROM msdb..sysjobs j (NOLOCK)
INNER JOIN
(
SELECT job_id, step_id, AVG(run_duration/10000*3600 + run_duration%10000/100*60 + run_duration%100) avgSecs
FROM msdb..sysjobhistory
WHERE step_id > 0 AND run_status = 1
GROUP BY job_id,step_id
) h on j.job_id = h.job_id
GROUP BY j.job_id
) jobavg ON jobavg.JobID = j.job_id
)jj
WHERE (@ShowDisabled = 1 OR (StartDate IS NOT NULL AND FinishDate IS NULL) OR Enabled = 1)
AND (@JobName IS NULL OR JobName = @JobName)
AND (@ShowUnscheduled = 1 OR (StartDate IS NOT NULL AND FinishDate IS NULL) OR ScheduleCount > 0)
AND (@JobThresholdSec = 0 OR DurationSec >= @JobThresholdSec)
AND (@AvgExecThresholdSec = 0 OR avgDurationSec >= @AvgExecThresholdSec)
)x
ORDER BY CASE Info
WHEN 'Running' THEN 0
WHEN 'Failed' THEN 1
WHEN 'Retry' THEN 2
WHEN 'Succeeded' THEN 3
WHEN 'Canceled' THEN 4
WHEN 'No steps' THEN 5
WHEN 'Not scheduled' THEN 6
WHEN 'Disabled' THEN 7
WHEN 'Never Run' THEN 8
WHEN 'UNKNOWN' THEN -1
ELSE -2 END, NextRunDate, JobName
GO
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply