Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1011121314»»»

Automating SQL Server Health Check (SQL Server 2005) Expand / Collapse
Author
Message
Posted Thursday, July 22, 2010 8:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:24 AM
Points: 205, Visits: 1,352
Is there a SQL2000 version of this SP?

...thanks
Post #957246
Posted Tuesday, September 14, 2010 3:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:02 AM
Points: 68, Visits: 336
Hello,

I'm not a expert but there something tricky because I didn't get the right execution time for jobs.

First I replaced "INSERT #jobs_status EXEC msdb.dbo.sp_help_job" by
"select sj.job_id, sj.name, sc.name as Category, sj.Enabled, sjs.last_run_outcome,
(select max(run_date) from sysjobhistory sjh where sjh.job_id = sj.job_id) as last_run_date
into #jobs_status
from sysjobs sj
join syscategories sc
on sj.category_id = sc.category_id
join msdb.dbo.sysjobservers sjs
on sjs.job_id = sj.job_id "

It works but I thing there are a problem with this part of script :

"Datediff(mi, A.last_executed_step_date, A.stop_execution_date) execution_time_minutes"

Why did you use "last_executed_step_date" and not "start_execution_date" to calculate the execution time of job.

I executed this script to check I get a different result but when I compared with the history of jobs I got the right result :

select
A.job_id,
A.start_execution_date,
A.stop_execution_date,
datediff(mi, A.start_execution_date, A.stop_execution_date) execution_time_minutes
from msdb..sysjobactivity A

Could you help me ?
Post #985349
Posted Tuesday, November 15, 2011 7:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 15, 2011 7:21 PM
Points: 1, Visits: 3
Find it useful. Many Thanks!

Roy
Post #1206382
Posted Friday, November 18, 2011 12:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 7, 2011 1:30 PM
Points: 1, Visits: 5
Has there been a fix for the error below?

Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Arithmetic overflow error converting int to data type numeric. [SQLSTATE 22003] (Error 8115). The step failed.
Post #1208598
Posted Friday, November 18, 2011 12:48 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:08 PM
Points: 95, Visits: 1,139
I have to take a look on monday when I´m back in office.
Also had the error in the beginning. Just changed a variable or column of a temp table since it was too small.
That fixed it for me.

Cannot remember right now where the problem exactly was


--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
Post #1208636
Posted Sunday, April 8, 2012 8:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 1:07 AM
Points: 21, Visits: 72
How can i declared a variable at the beginning of the stored procedure, @TableHTML ?
Post #1279977
Posted Tuesday, April 10, 2012 2:27 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:08 PM
Points: 95, Visits: 1,139
Sorry Christopher,

I completely forgot to look after the modification to get rid of the overflow error.
The only difference I have found is in line 330 (depending on your editor of choice).
It's the
select ...
from sys.databases MST
inner join (select b.name [LOG_DBNAME],
CONVERT(DECIMAL(10,2), sum....

Here I have a DECIMAL(20,2).
Afterwards the script worked fine for me.



christopher.jones 39322 (11/18/2011)
Has there been a fix for the error below?

Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Arithmetic overflow error converting int to data type numeric. [SQLSTATE 22003] (Error 8115). The step failed.


--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
Post #1280662
Posted Tuesday, April 10, 2012 2:31 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:08 PM
Points: 95, Visits: 1,139
What exactly do you need or in which part of the sproc do you want to declare an additional variable?


alnawrass2002 (4/8/2012)
How can i declared a variable at the beginning of the stored procedure, @TableHTML ?


--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
Post #1280666
Posted Saturday, April 14, 2012 11:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 1:07 AM
Points: 21, Visits: 72
Dirk.Hondong (4/10/2012)
What exactly do you need or in which part of the sproc do you want to declare an additional variable?


alnawrass2002 (4/8/2012)
How can i declared a variable at the beginning of the stored procedure, @TableHTML ?



Its mentioned in the article: " I have declared a variable at the beginning of the stored procedure, @TableHTML, which gets built and then executed at the end before it sends an e-mail".


What the code the declare the same?


Regards
Post #1283708
Posted Monday, April 23, 2012 11:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 1, 2014 6:33 AM
Points: 8, Visits: 124
Where is the .sql File. Also, i do not see SP code for TableHTML
Post #1288653
« Prev Topic | Next Topic »

Add to briefcase «««1011121314»»»

Permissions Expand / Collapse