Category Archives: SSRS

How to update/replace or remove an email address from all SSRS subscriptions with T-SQL

October 2, 2021Database Administrator, dba, SQL Server, SSRS, SSRS Reporting, T-SQL, tsqlchange ssrs subscription with sql, guide, How To, how to remove ssrs email address, how to update ssrs email address, how to update ssrs subscription, howto, lesson, ssrs subscriptions sql, T-SQL, tsql, tutorialTidbytez

Updating/replacing or removing an email address from SSRS subscriptions manually is far too time consuming and tedious. Use the below script instead to either update/replace an email address with a new one across all subscriptions or remove the email address from all subscriptions.

To exclude subscriptions, i.e. keep the email address active for a particular subscription, find the subscription Id for that subscription and include it in the WHERE clause. Remember to uncomment that line in order for the clause to be active.

If you’re worried about messing anything up then back up the table before running the script!

Create backup:

SELECT *
INTO [dbo].[Subscriptions_bk]
FROM [dbo].[Subscriptions]

Update/replace or remove an email address:

/*
To replace an email address with another email address:
Find and replace the following email addresses (Ctrl+H) with the new email address*/
/*
Email address to update/replace:
replaceSomeGuy@someCompany.com

Email address replacement:
newGuy@someCompany.com
*/
UPDATE [dbo].[Subscriptions]
SET ExtensionSettings = REPLACE(CONVERT(VARCHAR(MAX), ExtensionSettings), 'replaceSomeGuy@someCompany.com', 'newGuy@someCompany.com')
WHERE CHARINDEX('replaceSomeGuy@someCompany.com', ExtensionSettings) <> 0
--AND SubscriptionID NOT IN ('FindTheSubscriptionId')
;

/*
To remove an email address:
NOTE:Run both of the following scripts as an email address may or may not end with ";"

Find and replace the following email address (Ctrl+H)
*/
/*
Email address to remove
removeSomeGuy@otherCompany.com
*/
UPDATE [dbo].[Subscriptions]
SET ExtensionSettings = REPLACE(CONVERT(VARCHAR(MAX), ExtensionSettings), 'removeSomeGuy@otherCompany.com;', '')
WHERE CHARINDEX('removeSomeGuy@otherCompany.com;', ExtensionSettings) <> 0
--AND SubscriptionID NOT IN ('FindTheSubscriptionId')
;


UPDATE [dbo].[Subscriptions]
SET ExtensionSettings = REPLACE(CONVERT(VARCHAR(MAX), ExtensionSettings), 'removeSomeGuy@otherCompany.com', '')
WHERE CHARINDEX('removeSomeGuy@otherCompany.com', ExtensionSettings) <> 0
--AND SubscriptionID NOT IN ('FindTheSubscriptionId')
;

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

April 20, 2017Database Administrator, dba, Development, Reporting, sql, SQL Server, SSRS, T-SQL, tsqlbypass, bypass sql server management studio, example, from, GET, guide, history, How To, howto, Job, lesson, sample, SP, SQL Server, sql server management studio, ssms, stored procedure, tutorialTidbytez 1

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

How to turn a month name and year into a date field in SQL

April 10, 2016sql, SQL Server, SSRS, SSRS Reporting, T-SQLHow to turn a month name and year into a date field in SQL, month, month and year to date, month name, month name to date, Reporting, sql month and year to date, SQL Server, SSRS, T-SQL, tsql, tutorial, uncategorisedTidbytez 1

When would I use this?

Say you have an SSRS report which provides users with the parameter options month and year and the report returns data for that month and year. You will likely need that month name (varchar) and year (int) to be converted into a date fields representing the first and last day of that month/year. Once you have those two dates they can then be used in the WHERE clause of your SQL query to return data within that range. The logic below will allow the conversion of month name and year into start and end dates described above. You can now take this logic and input it into a stored procedure or user defined function.

There will only ever be 12 months going forward but to create an ever updating parameter option for years please see this tutorial.

DECLARE @year AS INT
DECLARE @month AS VARCHAR(9)
DECLARE @monthNumber AS CHAR(2)
DECLARE @startDate AS DATE
DECLARE @endDate AS DATE;

SET @year = 2016
SET @month = 'February'

IF @year IS NOT NULL
BEGIN
	WITH monthPicker
	AS (
		SELECT CASE 
				WHEN @month = 'January'
					THEN '01'
				WHEN @month = 'February'
					THEN '02'
				WHEN @month = 'March'
					THEN '03'
				WHEN @month = 'April'
					THEN '04'
				WHEN @month = 'May'
					THEN '05'
				WHEN @month = 'June'
					THEN '06'
				WHEN @month = 'July'
					THEN '07'
				WHEN @month = 'August'
					THEN '08'
				WHEN @month = 'September'
					THEN '09'
				WHEN @month = 'October'
					THEN '10'
				WHEN @month = 'November'
					THEN '11'
				WHEN @month = 'December'
					THEN '12'
				ELSE NULL
				END AS monthPicked
		)
	SELECT @monthNumber = (
			SELECT monthPicked
			FROM monthPicker
			)

	SET @startDate = (
			SELECT CAST(CAST(@year AS VARCHAR(4)) + @monthNumber + '01' AS DATETIME)
			)
	SET @endDate = (
			SELECT DATEADD(s, - 1, DATEADD(MM, DATEDIFF(M, 0, @startDate) + 1, 0))
			)
END
ELSE
BEGIN
	SET @startDate = (
			SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)
			)
	SET @endDate = (DATEADD(MONTH, 1 + DATEDIFF(MONTH, 0, GETDATE()), - 1))
END

PRINT @startDate
PRINT @endDate

How to keep an SSRS Report on one page

October 19, 2015SQL Server, SSRS, SSRS Reportingguide, How To, How to keep SSRS Report on one page, lesson, multiple, one page, Reporting, sheets, splitting, SSRS, tutorial, Visual StudioTidbytez 1

Using visual studio left click just under design to focus on the report.

Now the report properties window should be visible in the bottom right of the screen.

Set the interactive height to 0 as shown below.

You will get the following warning sign below. Limiting the report to one page, by setting the interactive height to zero, means everything needs to be loaded all at once. This will create lag when a user loads the report and interacts with it.

If you are basically using SSRS as a means for end users to pull a data dump via excel, heed this warning. If you just have a few aggregated tables and you want them all on the same page you should be fine.

How to remove NaN and Infinity from a SSRS table

August 24, 2015SQL Server, SSRSerror, guide, How To, Infinity, NaN, remove, Removing NaN and Infinity from an SSRS table, report, SQL Server, SSRS, tutorialTidbytez 1

Typically this occurs when a field uses an expression in a report table were the expression tries to divide a number by zero (Or the field can populate with #Error when there is a NULL involved). The best solution to resolve this problem is to create a custom function.

Right click on the background of your report (i.e. just below where it says Design) and go to Report Properties as shown:

Then you can left click on Code and add enter the custom code below in the window provided:

Public Function Divide(ByVal dividend As Double, ByVal divisor As Double) As Double
   If IsNothing(divisor) Or divisor = 0 Or IsNothing(dividend) Or dividend = 0 Then
      Return 0
   Else
      Return dividend / divisor
   End If
End Function

IsNothing() is used to avoid #Error and check whether the divisor or the dividend are 0 to avoid NaN and Infinity.

Now change the field expression from, for example:

SUM(Fields!A.Value)/SUM(Fields!B.Value)

To the expression below using the newly defined function Divide:

= Code.Divide(Sum(Fields!A.Value), Sum(Fields!B.Value))

Next you can update the fields textbox properties to make the number display as a percentage.

NOTE: Inserting a user defined function into a table will mean that that table will no longer be able to be copied and pasted as this throws an error. To copy and past the table you will need to look at the code of the report by right clicking on the report and choosing View Code. Search for “Code.Divide” and comment it out with an apostrophe ( ‘ ). You will now be able to copy the table.

How to pass a multi-value parameter to a stored procedure from a SSRS Report

July 16, 2015Reporting, SQL Server, SSRS, T-SQLguide, How To, howto, lesson, Multi-value, multiple select, multivalue, parameter, Parameters, Passing a multi-value parameter to a stored procedure from a SSRS Report, report, Reports, SP, split, SSRS, stored procedure, tsql, tutorial, UDF, uncategorised, user defined functionTidbytez

When you allow for multiple field values to be selected in a SSRS report there needs to be additional logic added to the back end to deal with this.

This is best explained with an example scenario.

I have a table called Ireland with two columns, ID_Column and County. You can use the script below to create and populate this table. Run the query below to follow the working example.

CREATE DATABASE [TEST_DB];

USE [TEST_DB];
GO

/****** Object:  Table [dbo].[Ireland]    Script Date: 07/15/2015 10:49:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Ireland] (
	[ID_Column] [int] IDENTITY(1, 1) NOT NULL
	,[County] [varchar](9) NULL
	,PRIMARY KEY CLUSTERED ([ID_Column] ASC) WITH (
		PAD_INDEX = OFF
		,STATISTICS_NORECOMPUTE = OFF
		,IGNORE_DUP_KEY = OFF
		,ALLOW_ROW_LOCKS = ON
		,ALLOW_PAGE_LOCKS = ON
		) ON [PRIMARY]
	) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

SET IDENTITY_INSERT [dbo].[Ireland] ON

INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (1, N'Antrim')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (2, N'Armagh')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (3, N'Carlow')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (4, N'Cavan')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (5, N'Clare')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (6, N'Cork')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (7, N'Derry')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (8, N'Donegal')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (9, N'Down')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (10, N'Dublin')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (11, N'Fermanagh')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (12, N'Galway')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (13, N'Kerry')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (14, N'Kildare')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (15, N'Kilkenny')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (16, N'Laois')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (17, N'Leitrim')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (18, N'Limerick')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (19, N'Longford')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (20, N'Louth')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (21, N'Mayo')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (22, N'Meath')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (23, N'Monaghan')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (24, N'Offaly')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (25, N'Roscommon')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (26, N'Sligo')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (27, N'Tipperary')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (28, N'Tyrone')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (29, N'Waterford')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (30, N'Westmeath')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (31, N'Wexford')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (32, N'Wicklow')
SET IDENTITY_INSERT [dbo].[Ireland] OFF

If I wanted to allow a user to select every county from the Ireland table in an SSRS report I would create a stored procedure that simply executes the following query.

SELECT * FROM Ireland

However an SSRS report which allows users to choose counties in Ireland like below means that a WHERE clause needs to be introduced and be able to respond to the field values selected.

But the issue is that the SSRS report will pass the multi-valued parameter as a string with the values separated by a comma.

So instead of receiving the required: ‘Antrim’, ‘Armagh’, ‘Carlow’, ‘Cavan’ etc. for use in the WHERE clause.

SQL Server is passed: ‘Antrim, Armagh, Carlow, Cavan’ etc. which cannot be used.

So the first additional logic and code to be added to the back end to deal with the multi-value parameter is a User Defined Function (UDF) which splits the parameter. The following function and quotations are taken from the 4guysfromrolla website.

Function Scope:

“There are generally two parameters to a split function: the list to split and the character(s) to split on, the delimiter. In the following function we begin by declaring our input variables – @List, the list to split, and @SplitOn, the delimiter(s) to split on. The return value of this UDF is a table with two fields: Id, an identity column, and Value, an nvarchar(100) column.”

Function Logic:

“The main body of the function simply loops through the string finding the first occurrence of the delimiter on each pass. Once the delimiter has been found, the string is broken into two pieces. The first piece is inserted into the result table while the second piece replaces the original list. The loop continues until no more occurrences of the delimiter are found. Lastly, the remainder of the list is added to the result table. Return the table and you have a split function.”

Run the query below to follow the working example.

USE [TEST_DB];
GO

CREATE FUNCTION [dbo].[Split] (
	@List NVARCHAR(2000)
	,@SplitOn NVARCHAR(5)
	)
RETURNS @RtnValue TABLE (
	Id INT identity(1, 1)
	,Value NVARCHAR(100)
	)
AS
BEGIN
	WHILE (Charindex(@SplitOn, @List) > 0)
	BEGIN
		INSERT INTO @RtnValue (value)
		SELECT Value = ltrim(rtrim(Substring(@List, 1, Charindex(@SplitOn, @List) - 1)))

		SET @List = Substring(@List, Charindex(@SplitOn, @List) + len(@SplitOn), len(@List))
	END

	INSERT INTO @RtnValue (Value)
	SELECT Value = ltrim(rtrim(@List))

	RETURN
END

(For a line by line break down of the function please see the webpage.)

Now that the split function exits within the database it is now possible to use a Stored Procedure to SELECT from the Ireland table pulling back specific counties by way of a WHERE clause.

Run the query below to follow the working example.

USE Test_DB;
GO

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

ALTER PROCEDURE [dbo].[GetCounties] @County VARCHAR(MAX)
AS
	/*

NAME OF SP: GetCounties
Author:		Bloggins86
Date:		15/07/2015
Purpose:	Test multi-parameter select

*/
	------------------------------------------------------
	------------------------------------------------------
	--INSERT STORED PROCEDURE LOGIC HERE
	SELECT *
	FROM Ireland
	WHERE County IN (SELECT Value FROM dbo.Split(@County, ','))

	------------------------------------------------------
	------------------------------------------------------

Now that the populated table, split function and county select SP exists run the query below passing a string with multiple counties to return Dublin, Meath and Cork from the Ireland Table.

EXEC dbo.[GetCounties] 'Dublin, Meath, Cork'

You should now have returned the table with Dublin, Meath and Cork as separate row entries.

And that’s it, thanks for reading.

How to find columns from all Tables of a Database

March 19, 2015Excel, GDPR, Reporting, sql, SQL Server, SSRS, SSRS Reporting, T-SQLcolumns, database, find, guide, How To, howto, lesson, locate, Query, Query To Find Columns From All Tables Of Database, report, T-SQL, tables, tutorialTidbytez

If your job is to create reports using SQL chances are you have or will encounter this situation:

You’ve been asked to prepare a report, but the person who has asked for the report simply has a list of fields they want and they have no idea where those fields come from. They may have received previous reports in the past, so they know the fields exist, but they cannot provide any of the SQL queries used to create these reports as an example.

You, the developer, may not be familiar with that particular area of the business or associated data sources. Possibly because you typically prepared financial reports and this request has come from the operations or marketing departments.

So the first step is to locate these columns within the database.

The following query will return the Table Name, Schema Name and Column Name from the database.

In the example below all instances where the column name equals CustomerID, OrderID, OrderDate will be returned. Also Column names that contain the word Status or Promotion will also be return. Simply change or add additional columns names as needed.

USE [YourDatabaseName];
GO

SELECT T.NAME AS TableName
	,SCHEMA_NAME(SCHEMA_ID) AS SchemaName
	,C.NAME AS ColumnName
FROM SYS.TABLES AS t
INNER JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID
WHERE C.NAME = 'CustomerID'
	OR C.NAME = 'OrderID'
	OR C.NAME = 'OrderDate'
	OR C.NAME LIKE '%Status%'
	OR C.NAME LIKE '%Promotion%'
ORDER BY SchemaName
	,TableName;

That should help get you started in preparing the report.

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: