January 29, 2013 at 3:55 am
Hi Guys,
I need a script that will send me a complete list of failed jobs by looping through all the linked servers. Currently, I have a report sent by some third party person through SSRS, which looks quite nice, and it checks for a specific kind of job for e.g: (DBA_weeklyindex_maint). I am actually interested to get this working for my environment. I don't have the code, and lack the knowledge of SSRS. I would really appreciate if you guys could help me out with this script to get job details only for a specific job through SSRS. Is it also possible if you guys could also provide an alternate script that I can use to get the same report, but for all the failed jobs looping through all linked servers, and getting it e-mailed it in a formatted manner, like an "HTML" sort of report.
--Pls. note I've attached the screenshot for the report through SSRS which we get on a weekly basis. It actually gets details from all the servers in prod, and checks for a specific maintenance jobs, and then sends an e-mailed report.
Pls. help me out guys!!!
Regards,
Faisal
January 29, 2013 at 4:42 am
You do not need linked servers. Use powershell or sqlcmd to connect directly to each sql instance, query them and store results. For list of servers use textfile or central management server. There are at least several other ways to do that as well.
January 29, 2013 at 6:53 am
Hi Vedran,
Thanks. for those piece of advice. Could you document one way to do this along with the code & procedure to run it successfully. I would really appreciate!!!
Regards,
Faisal
January 29, 2013 at 7:00 am
This isn't "pretty" code but it works and will email you the results (I have it running hourly). I just set up a simple SQL Agent job on each of the servers I want to be notified for:USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[utl_dba_CheckSQLJobsForErrors]
@notifyEmailGroup VARCHAR(300) ='dba_alerts@company.com'
AS
DECLARE @sqlSelect NVARCHAR(500)
CREATE TABLE #SQLAGENTERRORS(
[id] INT IDENTITY(1,1) NOT NULL,
[SQL AGENT JOB NAME] [sysname] NOT NULL,
[step_id] [int] NOT NULL,
[step_name] [sysname] NOT NULL,
[sql_message_id] [int] NOT NULL,
[sql_severity] [int] NOT NULL,
[message] [nvarchar](1024) NULL,
[run_status] [int] NOT NULL,
[run_date] [int] NOT NULL,
[run_time] [int] NOT NULL,
[run_duration] [int] NOT NULL,
[retries_attempted] [int] NOT NULL,
[server] [sysname] NOT NULL
)
SET @sqlSelect = '
SELECT DISTINCT
a.[Name][SQL AGENT JOB NAME],[step_id],[step_name],0[sql_message_id]'+
',0[sql_severity],0[message],0[run_status],[run_date],[run_time],0[run_duration] '+
', 0 [retries_attempted],[server]
FROM [msdb].[dbo].[sysjobs] as a
JOIN [msdb].[dbo].[sysjobhistory] as b on
a.job_id=b.job_id '+
'WHERE
b.Sql_Severity <> 0
AND run_date = REPLACE(CONVERT(VARCHAR(10),GETDATE(),121),''-'','''') '+
'AND [run_time] >= '''+REPLACE(CONVERT(VARCHAR(20),DATEADD(minute, -20,GETDATE()),08),':','')+''''+
' ORDER BY [run_date],a.[Name],b.[step_id] '
-- WRITE TEMP TABLE TO FORMAT DATA
INSERT INTO #SQLAGENTERRORS
EXECUTE MASTER..SP_EXECUTESQL @sqlSelect
IF(SELECT Count(1) from #SQLAGENTERRORS) > 0
BEGIN
DECLARE @calldate DATETIME
,@sqlStatement NVARCHAR(350)
,@subject VARCHAR(128)
,@body VARCHAR(4000)
SET @body='
'
SET @subject = 'Job Step Failures Detected ' + CONVERT(Varchar(25),GETDATE(),120)
IF(SELECT Count(1) from #SQLAGENTERRORS) > 0
BEGIN
DECLARE @Run_Date VARCHAR(12)
,@Run_TimeVARCHAR(12)
,@SQLAGENTJOBNAME VARCHAR(200)
,@step_id VARCHAR(12)
,@step_name VARCHAR(200)
,@sql_message_id VARCHAR(12)
,@sql_severity VARCHAR(12)
,@message VARCHAR(500)
,@run_status VARCHAR(12)
,@run_duration VARCHAR(12)
,@retries_attempted VARCHAR(12)
,@server VARCHAR(25)
,@id INT
,@maxID INT
SELECT @id = 1, @maxID = MAX(ID) from #SQLAGENTERRORS
SELECT * from #SQLAGENTERRORS
WHILE @id <= @maxID
BEGIN
SELECT
@Run_Date =RTRIM(SUBSTRING(CONVERT(VARCHAR(8),[run_date]),1,4)+ '-'+ SUBSTRING(CONVERT(VARCHAR(8),[run_date]),5,2)+'-'+SUBSTRING(CONVERT(VARCHAR(8),[run_date]),7,2))
,@Run_Time = CASE WHEN LEN([run_time]) = 5 THEN
RTRIM('0'+ SUBSTRING(CONVERT(VARCHAR(10),[run_time]),1,1)+':'+SUBSTRING(CONVERT(VARCHAR(10),[run_time]),2,2)+':'+SUBSTRING(CONVERT(VARCHAR(10),[run_time]),4,2))
ELSE
RTRIM(SUBSTRING(CONVERT(VARCHAR(10),[run_time]),1,2)+':'+SUBSTRING(CONVERT(VARCHAR(10),[run_time]),3,2)+':'+SUBSTRING(CONVERT(VARCHAR(10),[run_time]),5,2))
END
,@SQLAGENTJOBNAME = RTRIM([SQL AGENT JOB NAME])
,@step_id=RTRIM([step_id])
,@step_name=RTRIM([step_name])
,@sql_message_id=RTRIM([sql_message_id])
,@sql_severity=RTRIM([sql_severity])
,@message=RTRIM([message])
,@run_status=RTRIM([run_status])
,@run_duration=RTRIM([run_duration])
,@retries_attempted=RTRIM([retries_attempted])
,@server=RTRIM([server])
FROM #SQLAGENTERRORS
WHERE id=@id
SELECT @body= @body +'ERROR #'+RTRIM(CONVERT(VARCHAR(10),@id)) +' - '+ @SQLAGENTJOBNAME +' on ' + @server +'
RUN DATE: ' + @Run_Date + ' Run Time: ' +@Run_Time +'
=======================================
Step: ' + @step_id+' - '+@step_name+'
'
SET @id=@id+1
END
EXEC msdb..sp_send_dbmail @recipients = @notifyEmailGroup
,@subject = @subject
,@body=@body
END
END
DROP TABLE #SQLAGENTERRORS
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 29, 2013 at 7:22 am
Thanks. for that code Jessie.
I really need the body part formatted that can give me the details what I am looking for the way it's there in the image attached. I would appreciate if someone comes up or modify the code that you've provided so that I can get a more formatted version of that stuff. I also appreciate if someone comes up with an SSRS solution, documenting the step & providing the code for the same. I think there are ways in which we can format how the results will look when the report is mailed, same as the one in the attachment.
Thanks. once again ""MydoggyJessie" for your help....really appreciate.
Regards,
Faisal
January 29, 2013 at 7:44 am
Create a simple SSRS report (RDL file) and call the stored-procedure running this code, format the report to your liking. Reporting Services is pretty straightforward...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 30, 2013 at 9:22 am
Hey Jessie,
The code is great but it seems that it doesn't do what I wanted. I don't want to set it up across all the instances, instead I would rather set it up across an instance & then get the result by looping through all of my linked servers. This will send me a consolidated report of job failures noticed across all the instances.
I would appreciate if someone could really help me out over here.
Regards,
Faisal
January 30, 2013 at 12:36 pm
It should be quite simple to modify the code to execute against your linked servers...
Example:INSERT INTO #SQLAGENTERRORS
EXECUTE [LINKSERVER].MASTER..SP_EXECUTESQL @sqlSelect
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 30, 2013 at 11:04 pm
Hey Jessie,
That's what I don't want to do providing the linked server name each time for the execute statement. I would like to store all my linked server info in some table or retrieve it from sys.sysservers...something in that way, then either using a cursor loop through all those servers present in that table. That's a much quicker way of doing it I feel.
Thanks. anyways.
Regards,
Faisal
January 31, 2013 at 7:18 am
Hi Jessie,
It seems that your code is not working correctly. After creating the stored proc, when I execute it just completes it's execution within no time...something in the code doesn't seem to do what it has to do, and to add a little more twist e-mail msg can't be sent to my e-mail address...just fyi I have my database mail settings correctly configured, so there is no doubt that there is something to look in the mail part. Could you pls. check your code, most probably the @sqlSelect part.
Regards,
Faisal
January 31, 2013 at 9:16 am
Do you have any errors to report? I tested the code before I posted it, it does work fine - if there are no failed jobs on the server you're running it against, it won't return any records.
The code I posted was meant for you to use as a template 🙂 There's no reason you can't log everything to one centralized location...just change the code...not sure if you can loop through the linked servers and execute it remotely, but you can try it and see how it goes
Try something like:
DECLARE @servers TABLE (idx int IDENTITY(1,1), SName sysname)
INSERT INTO @servers
SELECT name FROM sys.servers WHERE Product = 'SQL Server' AND is_linked = 1
SET @Cnt = @@ROWCOUNT
WHILE @Cnt <> 0
BEGIN
SELECT @Server = Sname, @idx = idx FROM @servers
<< EXECUTE YOUR CODE HERE >>
DELETE FROM @servers WHERE idx = @idx
SET @Cnt = @Cnt -1
END
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 31, 2013 at 9:51 am
Thanks. Jessie,
Really appreciate your quick responses. I'll again run that code of yours, onto other servers, and check the response. What about the e-mail part it should mail me, there is no condition where it says that send mail only if you have something in the report.
I would also appreciate if you could attach a snapshot of the way the report is generated when it lands into your mail box...just to get an understanding of the end result.
Regards,
Faisal
January 31, 2013 at 10:06 am
Really appreciate your quick responses. I'll again run that code of yours, onto other servers, and check the response. What about the e-mail part it should mail me, there is no condition where it says that send mail only if you have something in the report.
IF(SELECT Count(1) from #SQLAGENTERRORS) > 0
EXEC msdb..sp_send_dbmail @recipients = 'YourEmailGoesHere'
,@subject = @subject
,@body=@body
Example of email
-----Original Message-----
From: Reports [mailto:dba_alerts@company.com]
Sent: Wednesday, January 30, 2013 7:45 AM
To: DBA_Alerts
Subject: Job Step Failures Detected 2013-01-30 07:45:06
ERROR #1 - DAILY - SEND REPORTS on MyServer
RUN DATE: 2013-01-30 Run Time: 07:30:07
=======================================
Step: 2 - Email SSRS Reports
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 31, 2013 at 10:20 am
Thanks. on millions Jessie. I'll rigorously test your code, and let you know how things are working in my environment.
Thanks. a lot.
Regards,
Faisal
February 1, 2013 at 2:15 am
Jessie,
Sorry brother but this code is really not doing anything for me. Whenever I execute this sp it runs successfully but at the result pane I can just see (0 row(s) affected). I have a lot of jobs that have failed on a particular instance, but it's reporting about none of them.
Do I have to make any changes in the code apart from 'email address' to make it work in our environment. Pls. let me know about it
Regards,
Faisal
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply