sp_help_jobactivity - Check Run Time Job Activity

sp_help_jobactivity system stored procedure provides information about the runtime state of SQL Server Agent jobs. This SP can be executed to check state of particular Job by passing value to either @job_id or @job_name parameter.


This SP has following parameters

Parameter_name	Type
@job_id	uniqueidentifier
@job_name	sysname
@session_id	int

This SP can be used along with sp_help_jobhistory to implement generic notification process for all Jobs.

sp_help_jobhistory - Check failing Jobs

sp_help_jobhistory system stored procedure is used to view the history of jobs. For checking the history of all Jobs we can execute below script.


USE msdb;
GO
EXEC dbo.sp_help_jobhistory
GO

We can also execute this SP to check for only failing Jobs by providing @run_status parameter. Below SP call would provide the list of Jobs with steps details which failed to get execute. Specifying a value of 0 for the @run_status parameter returns history for all failed jobs.


USE msdb;
GO
EXEC dbo.sp_help_jobhistory
@run_status = 0;
GO

Specifying a value of 1 for the @run_status parameter returns history for all jobs that completed successfully.


Specifying a value of 2 for the @run_status parameter returns history for all jobs steps that have a retry status.


Specifying a value of 3 for the @run_status parameter returns history for all jobs that were canceled.


This SP can also be executed with @Job_id /@Job_name parameters too to check for particular Job's history. If we do not pass any value to these parameters this SP runs for all Jobs.


There are more parameters to this SP, which I don't think are that much handy then the above 3 described though I have provided the list of all parameters below.

Parameter_name	Type
@job_id	uniqueidentifier
@job_name	sysname
@step_id	int
@sql_message_id	int
@sql_severity	int
@start_run_date	int
@end_run_date	int
@start_run_time	int
@end_run_time	int
@minimum_run_duration	int
@run_status	int
@minimum_retries	int
@oldest_first	int
@server	sysname
@mode	varchar

Exception from HRESULT: 0xC0010014(Microsoft.SqlServer.DTSRuntimeWrap)

I was working on setting up Maintenance Plan for backup on newly installed SQL Server 2005. On this server we installed only database engine services originally but not Integration Services (SSIS), and then later decided to add this service too. While editing backup task on this new Server, I was facing issue with Exception from HRESULT: 0xC0010014(Microsoft.SqlServer.DTSRuntimeWrap) as shown below.

To resolve this issue for 32-bit installation, you can perform below steps (basically register couple dll's which got messed up due to sequence of installation)

%windir%\syswow64\regsvr32 "%ProgramFiles(x86)%\Microsoft SQL Server\90\dts\binn\dts.dll"

%windir%\syswow64\regsvr32 "%ProgramFiles(x86)%\Microsoft SQL Server\90\dts\binn\MsDtsSrvrUtil.dll



and for 64 bit installation

%windir%\system32\regsvr32 "%ProgramFiles%\Microsoft SQL Server\90\dts\binn\dts.dll

%windir%\system32\regsvr32 "%ProgramFiles%\Microsoft SQL Server\90\dts\binn\MsDtsSrvrUtil.dll