May 16, 2012 at 7:39 pm
Hi All,
I'm currently working out the ideal way to display my result set to a set of people within a department and I'm not entirely sure what is the best method to use in my case.
Brief background; Every night I have a table populated with information regarding backup sets i.e. success/fail. I want a report generated to there email with information required for Today's fail, Last Weeks Fail & Last Months fail. Should i use a view, stored proc or something else.
Below is an example of the Database/table & result structure.
DATABASE: [Backups].[dbo].[EventLog]
TBL COL: Hostname,Database,Logged,Error,Result
RESULT : "TestMachine1", "NULL", "2012-05-14 00:00:00", "Update Failed: could not find server xxxxxx", "0"
Where "Result = 0" that means its failed and I only want it for the current date period specified (Today/LW/LM) Should i create 3 new tables (TODAY,LW,LM) that i insert the results or am i double handling information.
I'm confused and any ideas/help would be appreciated.
Thanks
Tava
May 16, 2012 at 9:48 pm
I would write a stored procedure to pull the data. I would then call that stored procedure from reporting services to generate a report. Either that, or create a stored procedure that is called by a scheduled job and will send an email using database mail and make the body html using table tags.
Jared
CE - Microsoft
May 16, 2012 at 10:14 pm
SQLKnowItAll (5/16/2012)
I would write a stored procedure to pull the data. I would then call that stored procedure from reporting services to generate a report. Either that, or create a stored procedure that is called by a scheduled job and will send an email using database mail and make the body html using table tags.
Thanks for the feedback, I thought about reporting services but they didnt want to go down that path as they would need to then run the reports them selves correct?
I will go down the path of a SP using maintenance plan and then email the requested parties. I dont quiet get the body html using table tags?
May 16, 2012 at 10:56 pm
Sample code you can put at the end of your SP. MAke sure you declare any missing variables as I did not have time to completely edit it.
set @subject = 'Report name or whatever subject you want'
SET @tableHTML =
N'<table border="1" cellpadding="1"><tr><th> Column1Name </th><th> Column2Name </th>' +
N'<th> Column3Name </th><th>Column4Name</th><th>Column5Name</th><th>Column6Name</th><th>Column7Name</th>'+
N'<th>Column8Name</th>'+
CAST ( ( SELECT td = Column1 , '' ,
td = Column2, '' ,
td = Column3, '' ,
td = Column4, '' ,
td = Column5, '' ,
td = Column6, '' ,
td = Column7, '',
td = Column8, ''
from table
where whatever
order by whatever
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'
exec msdb.dbo.sp_send_dbmail @recipients='recipient1@blah.com;recipient2@blah.com',@subject=@subject, @body=@tableHTML, @body_format ='HTML'
Jared
CE - Microsoft
May 16, 2012 at 11:07 pm
I didnt even know you could do that, you learn something new everyday. I will give this a go and hopefully I pick it all up but at least I'm now on the right path.
Thanks
Tava
May 16, 2012 at 11:12 pm
Tava (5/16/2012)
I didnt even know you could do that, you learn something new everyday. I will give this a go and hopefully I pick it all up but at least I'm now on the right path.Thanks
Tava
It's quick and dirty, but makes for simple monitoring reports. 🙂
Jared
CE - Microsoft
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply