Tag Archives: example

How to get MS SQL Server job history using a stored procedure

In a previous article (see article) I provided simple queries to return the server’s job history. It’s only natural that when you find yourself running the same script over and over again eventually you’ll get around to turning it into a stored procedure.

Well today was the day.

You can use the script below to create the Stored Procedure usp_GetJobHistory.

To deploy the SP to your instance you’ll need to replace the text PLACEHOLDER with the name of the database you’ll be deploying to. Ideally you should have a utility or administration database rather than deploying to Master.

The SP has 5 parameters.

Parameter Datatype Options Definition
@DaysToPull INT Any Number How many days of history you want
working back from today
@Status VARCHAR One of the following: All’, ‘Succeeded’, ‘Failed’,
‘Retried’, ‘Cancelled’, NULL
Will limited the rows returned
based on the status of the entry
@SortOrder VARCHAR ASC or DESC The occurrence order i.e. oldest to newest
@JobName VARCHAR Any Text Used in a LIKE operator to find jobs containing that text
@ReturnSql BIT 0, 1 or NULL Returns the SQL you can modify or
run to return the history

Example command:

Create the SQL code to return the job history for jobs containing the work backup that failed in the past day sorted by newest to oldest.

EXEC dbo.GetJobHistory @DaysToPull = 1
,@SortOrder = ‘DESC’
,@Status = ‘Failed’
,@JobName = ‘Backup’
,@ReturnSql = 1

Tip: you could use this to create a SSRS report that could be published daily to notify stakeholders of any job failures.

-- REPLACE PLACEHOLDER
USE [PLACEHOLDER];
GO

IF OBJECT_ID('[usp_GetJobHistory]') IS NULL
	EXEC ('CREATE PROCEDURE dbo.[usp_GetJobHistory] AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[usp_GetJobHistory] @DaysToPull INT
	,@Status VARCHAR(9) = NULL
	,@SortOrder VARCHAR(4) = NULL
	,@JobName VARCHAR(256) = NULL
	,@ReturnSql BIT = NULL
AS
BEGIN
	-- =============================================
	-- Author:      Bloggins
	-- Create date: 20170420
	-- Description: <Query to retrieve job history bypassing SSMS inbuilt viewer>
	-- Website: https://techtidbytes.wordpress.com/
	-- =============================================
	SET NOCOUNT ON

	BEGIN TRY
		-- Variable Declarations 
		DECLARE @PreviousDate DATETIME
		DECLARE @Year VARCHAR(4)
		DECLARE @Month VARCHAR(2)
		DECLARE @MonthPre VARCHAR(2)
		DECLARE @Day VARCHAR(2)
		DECLARE @DayPre VARCHAR(2)
		DECLARE @FinalDate INT
		DECLARE @StatusClause AS VARCHAR(255)
		DECLARE @Sql AS VARCHAR(MAX)

		IF @SortOrder IS NULL
			OR (
				@SortOrder <> 'ASC'
				AND @SortOrder <> 'DESC'
				)
		BEGIN
			SET @SortOrder = 'ASC'
		END

		IF @Status = 'All'
			OR @Status IS NULL
			OR (
				@Status <> 'All'
				AND @Status <> 'Failed'
				AND @Status <> 'Succeeded'
				AND @Status <> 'Retried'
				AND @Status <> 'Cancelled'
				)
		BEGIN
			SET @StatusClause = '0, 1, 2, 3'
		END
		ELSE IF @Status = 'Failed'
		BEGIN
			SET @StatusClause = '0'
		END
		ELSE IF @Status = 'Succeeded'
		BEGIN
			SET @StatusClause = '1'
		END
		ELSE IF @Status = 'Retried'
		BEGIN
			SET @StatusClause = '2'
		END
		ELSE IF @Status = 'Cancelled'
		BEGIN
			SET @StatusClause = '3'
		END

		SET @PreviousDate = DATEADD(dd, - @DaysToPull, GETDATE())
		SET @Year = DATEPART(yyyy, @PreviousDate)

		SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))

		SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)), 2)

		SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))

		SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)), 2)

		SET @FinalDate = CAST(@Year + @Month + @Day AS INT)
		/*RunDuration FORMAT (DD:HH:MM:SS)*/
		SET @Sql = '
SELECT ROW_NUMBER() OVER(ORDER BY h.instance_id ' + @SortOrder + ' ) AS Row
	,h.Server AS ServerName
	,s.database_name AS DbName
	,j.name AS JobName
	,s.step_name AS StepName
	,h.step_id AS StepId
	,CASE 
		WHEN h.run_status = 0
			THEN ''Failed''
		WHEN h.run_status = 1
			THEN ''Succeeded''
		WHEN h.run_status = 2
			THEN ''Retried''
		WHEN h.run_status = 3
			THEN ''Cancelled''
		END AS RunStatus
	,MSDB.DBO.AGENT_DATETIME(h.run_date, h.run_time) AS RunTime
	,STUFF(STUFF(STUFF(RIGHT(REPLICATE(''0'', 8) + CAST(h.run_duration AS VARCHAR(8)), 8), 3, 0, '':''), 6, 0, '':''), 9, 0, '':'') AS RunDuration
	,h.sql_severity AS SqlSeverity
	,h.message AS MessageReturned
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
	AND h.step_id = s.step_id
WHERE h.run_status IN (' + @StatusClause + ')
	AND h.run_date > ' + CONVERT(VARCHAR(255), @FinalDate)

		IF @JobName IS NOT NULL
		BEGIN
			SET @Sql = @Sql + '
AND j.name LIKE ' + '''' + '%' + @JobName + '%' + '''' + ''
		END

		SET @Sql = @Sql + '
ORDER BY h.instance_id ' + @SortOrder

		IF @ReturnSql = 1
		BEGIN
			PRINT (@Sql)
		END
		ELSE
		BEGIN
			EXEC (@Sql)
		END
	END TRY

	BEGIN CATCH
		PRINT 'error!'

		DECLARE @error_number AS INTEGER
		DECLARE @error_message AS VARCHAR(400)

		SET @error_number = error_number()
		SET @error_message = left(error_message(), 400)

		PRINT 'error_message: ' + @error_message
	END CATCH
END
Advertisements

How to pass table valued parameters in SQL Server

With the introduction of SQL Server 2008 came the ability to define an entire table as a parameter, think of it like a table data type. This feature greatly eases the development process as constructing and parsing XML data strings is no longer necessary.

Table parameters are user defined parameters, i.e. you are creating a means of storing specific data that is passed by a stored procedure or function.

Limitations:

  • The READONLY clause must be used when passing in the table valued variable
  • Data in the table variable cannot be modified
  • The table variables cannot be used as OUTPUT parameters only input parameters.
  • When data is passed to the table variable the table variable must be passed to the stored procedure in the same batch. Table variables go out of scope as soon as the procedure or batch returns.

The following is a complete end to end example of how to create and pass data to table parameters:

IF OBJECT_ID('OrderHistory') > 0
	DROP TABLE OrderHistory;
GO

CREATE TABLE [dbo].[OrderHistory] (
	[OrderID] [int] IDENTITY(1, 1) NOT NULL PRIMARY KEY
	,[Product] [varchar](10) NULL
	,[OrderDate] [datetime] NULL
	,[SalePrice] [money] NULL
	)
GO

CREATE TYPE OrderHistoryTableType AS TABLE (
	[Product] [varchar](10) NULL
	,[OrderDate] [datetime] NULL
	,[SalePrice] [money] NULL
	)
GO

CREATE PROCEDURE usp_InsertOrder (@TableVariable OrderHistoryTableType READONLY)
AS
BEGIN
	INSERT INTO OrderHistory (
		Product
		,OrderDate
		,SalePrice
		)
	SELECT Product
		,OrderDate
		,SalePrice
	FROM @TableVariable
END
GO

DECLARE @DataTable AS OrderHistoryTableType

INSERT INTO @DataTable (
	Product
	,OrderDate
	,SalePrice
	)
VALUES (
	'Desktop'
	,GETUTCDATE()
	,599.00
	)

INSERT INTO @DataTable (
	Product
	,OrderDate
	,SalePrice
	)
VALUES (
	'Laptop'
	,GETUTCDATE()
	,299.00
	)

INSERT INTO @DataTable (
	Product
	,OrderDate
	,SalePrice
	)
VALUES (
	'Mouse'
	,GETUTCDATE()
	,9.00
	)

EXEC usp_InsertOrder @TableVariable = @DataTable

SELECT *
FROM OrderHistory

 

If you want to view other types of table type definitions in your system, or you’ve forgotten what you called a specific table parameter, you can execute the following query, which looks in the system catalog:

SELECT * FROM sys.table_types

How to export a table as an XML file using a SQL script

Below is a script that will allow you, as the title suggests, to export a table as an XML file.

By use of the find and replace function in your preferred text editor, or by way of the text editor in SSMS, input your values for the required fields, i.e. replace TableName with the name of the table you will be working off.

SERVER NAME: ServerName

DATABASE NAME: DatabaseName

SCHEMA NAME: SchemaName

TABLE NAME: TableName

SAVE LOCATION ADDRESS: AddressName

FILE NAME: FileName (Excluding .xml extension)

Then run the script below and the table will be exported as an XML file to the location and file name of your choosing.

/*
FIND AND REPLACE:

SERVER NAME: ServerName
DATABASE NAME: DatabaseName 
SCHEMA NAME: SchemaName
TABLE NAME: TableName
SAVE LOCATION ADDRESS: AddressName
FILE NAME: FileName
 */
-- Run on ServerName
USE DatabaseName;
GO

DECLARE @saveLocation AS VARCHAR(510)
DECLARE @nameOfFile AS VARCHAR(255)
DECLARE @instanceName AS VARCHAR(255)
DECLARE @cmd NVARCHAR(1020)

SET @saveLocation = 'AddressName'
SET @nameOfFile = 'FileName'
SET @saveLocation = @saveLocation + '\' + @nameOfFile + '.xml'
SET @instanceName = (
		SELECT @@servername
		)

-- Please note that the fully qualified table name is needed
SELECT @cmd = '
    bcp "SELECT * FROM [DatabaseName].[SchemaName].[TableName] row for xml auto, root(''rows''), elements" ' + 'queryout "' + @saveLocation + '" -S ' + @instanceName + ' -T -w -r -t';

EXEC xp_cmdshell @cmd;
GO

 

This script is enabled by two utilities xp_cmdshell and BCP.

For an explanation of each please view the associated links.