SQL Scripts – SQL Professionals https://www.jinyong.kim A Microsoft SQL Server Consultancy Tue, 21 Feb 2017 07:16:26 +0000 en-US hourly 1 https://wordpress.org/?v=4.7.3 Using T-SQL to Parse Address City, State and Zip Code https://www.jinyong.kim/blog/sql-scripts/2016/03/13/using-t-sql-parse-address-city-state-zip-code/ https://www.jinyong.kim/blog/sql-scripts/2016/03/13/using-t-sql-parse-address-city-state-zip-code/#comments Mon, 14 Mar 2016 04:00:24 +0000 http://www.jinyong.kim/?p=434 Recently there was a request to create a function that would parse out the city, state and postal code information from a single string into separate columns for both US…

The post Using T-SQL to Parse Address City, State and Zip Code appeared first on SQL Professionals.

]]> Recently there was a request to create a function that would parse out the city, state and postal code information from a single string into separate columns for both US and Canadian addresses. There are obviously many different ways to approach this problem, and in my first few attempts I tried using the PATINDEX function with regular expression to first extract the postal code and then to identify the state and city in that order. Since using regular expressions in T-SQL does not quite work the same as using regular expression in managed code, I was finding it difficult to account for all the different variations without explicitly defining each and everyone of them first. The comments in the code below explain some of these variations.

I wanted to keep it simple avoid solutions involving CLR or using OLE functions. Yes, a CLR solution would probably be the best in terms of both performance and robustness, but I wanted to keep the solution within the scope of a single T-SQL function and I think the solution below works quite well.

Below you’ll find the complete code for the function along with comments and usage documentation. Please take note of the assumptions being made about the data in the code comments notes section. I did test this against a large data set with many different variations which are also mentioned in the code comments. If you come across any examples that don’t parse correctly with this code, please let me know in a comment below. Hope you enjoy.
 

SET ANSI_NULLS ON; 
GO
SET QUOTED_IDENTIFIER ON; 
GO
IF EXISTS (SELECT * FROM [sys].[objects] WHERE [object_id] = OBJECT_ID(N'[dbo].[ParseAddress]') AND [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT')) 
BEGIN 
	DROP FUNCTION [dbo].[ParseAddress]; 
END; 
GO 

/*========================================================================================================= 
Script:		dbo.ParseAddress.sql 

Synopsis:	Function to parse an address string containing city, state and zip code information. This 
			should work for any United States or Canada address string. Tested using multiple variations 
			including 5-digit zip codes, 5-digit zip code + 4-digit extension separated with 1+ space(s),
			hyphen, or no separation and Canadian 6-character postal codes with the same variations. This
			function will also discard non-alpha characters and multiple spaces used to separate city, 
			state and zip code information.

Usage:		SELECT	 [Address] 
					,[City]  = [dbo].[ParseAddress]([Address], N'City') 
					,[State] = [dbo].[ParseAddress]([Address], N'State') 
					,[Zip]   = [dbo].[ParseAddress]([Address], N'Zip') 

Notes:		The following assumptions are made about the data for parsing: 

					- Zip code information is the last part of the string
					- Zip code will always contain 5+ characters. 
					- State will always be two letters. 
					- City will always end with a letter. 

=========================================================================================================== 
Revision History: 

Date			Author				Description 
----------------------------------------------------------------------------------------------------------- 


===========================================================================================================*/ 
CREATE FUNCTION [dbo].[ParseAddress] 
( 
	@String NVARCHAR(64), 
	@Get NVARCHAR(64) 
) 
RETURNS NVARCHAR(64) 
AS 
BEGIN 
	DECLARE @Address AS NVARCHAR(64); 
	DECLARE @City    AS NVARCHAR(25); 
	DECLARE @State   AS NVARCHAR( 2); 
	DECLARE @Zip     AS NVARCHAR(10); 
	DECLARE @Index   AS TINYINT     ; 
	DECLARE @Char    AS NCHAR(    1); 
	DECLARE @Value   AS NVARCHAR(64); 

	-- Remove any leading or trailing white space
 	SET @Address = LTRIM(RTRIM(@String)); 
	-- Initialize string index
	SET @Index = 1; 
	
	WHILE (@Index <= LEN(@Address)) 
	BEGIN 
		SET @Char = SUBSTRING(REVERSE(@Address), @Index, 1); 

		IF (@Zip IS NULL OR LEN(@Zip) < 5) 
		BEGIN 
			-- Continue reading valid characters for @Zip
			WHILE (PATINDEX(N'[a-zA-Z0-9]', @Char) = 1) 
			BEGIN 
				SET @Zip = ISNULL(STUFF(@Zip, 1, 0, UPPER(@Char)), UPPER(@Char)); 
				SET @Index = @Index + 1; 
				SET @Char = SUBSTRING(REVERSE(@Address), @Index, 1); 
			END; 
		END; 

		IF (@State IS NULL OR LEN(@State) <> 2) 
		BEGIN 
			-- Continue reading valid characters for @State
			WHILE (PATINDEX(N'[a-zA-Z]', @Char) = 1) 
			BEGIN 
				SET @State = ISNULL(STUFF(@State, 1, 0, UPPER(@Char)), UPPER(@Char)); 
				SET @Index = @Index + 1; 
				SET @Char = SUBSTRING(REVERSE(@Address), @Index, 1); 
			END; 
		END; 

		-- The last character of city should be an alpha character
		IF (PATINDEX(N'[a-zA-Z]', @Char) = 1) 
		BEGIN 
			-- Just assign the rest of the string to the @City variable
			SET @City = SUBSTRING(@Address, 1, LEN(@Address) - @Index + 1); 
			BREAK; 
		END; 

		SET @Index = @Index + 1; 
	END; 

	-- Removes double-spaces from the city name 
	WHILE (CHARINDEX(SPACE(2), @City) > 0) 
	BEGIN 
		SET @City = REPLACE(@City, SPACE(2), SPACE(1)); 
	END; 

	-- Format US Postal Codes that have 4 digit extension by stuffing the hyphen in correct position 
	IF (PATINDEX(N'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]', @Zip) = 1) 
	BEGIN 
		SET @Zip = STUFF(@Zip, 6, 0, N'-'); 
	END; 

	-- Format Canadian Postal Codes by stuffing a space in correct position 
	IF (PATINDEX(N'[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]', @Zip) = 1) 
	BEGIN 
		SET @Zip = STUFF(@Zip, 4, 0, N' '); 
	END; 

	IF (@Get = N'City')  SET @Value = @City; 
	IF (@Get = N'State') SET @Value = @State; 
	IF (@Get = N'Zip')   SET @Value = @Zip; 

	RETURN @Value; 
END; 
GO

The post Using T-SQL to Parse Address City, State and Zip Code appeared first on SQL Professionals.

]]> https://www.jinyong.kim/blog/sql-scripts/2016/03/13/using-t-sql-parse-address-city-state-zip-code/feed/ 2 Monitoring Disk Space with SQL Server and PowerShell https://www.jinyong.kim/blog/sql-scripts/2014/10/10/monitoring-disk-space-sql-server-powershell/ https://www.jinyong.kim/blog/sql-scripts/2014/10/10/monitoring-disk-space-sql-server-powershell/#comments Fri, 10 Oct 2014 17:48:22 +0000 http://www.jinyong.kim/?p=268 This article shows one possible solution for monitoring disk space usage using SQL Server and a simple PowerShell script executed via SQL Agent Job. In this first code section is…

The post Monitoring Disk Space with SQL Server and PowerShell appeared first on SQL Professionals.

]]> This article shows one possible solution for monitoring disk space usage using SQL Server and a simple PowerShell script executed via SQL Agent Job.

In this first code section is the table definition I will be using to store the data collected by the PowerShell script. Please note that I have opted to define the [UsedSpaceKB], [PercentFree] and [PercentUsed] fields as computed columns to simplify the reports which will be utilizing this data. I have also chosen to use kilobytes (kb) as my unit of measurement here because I have other solutions that monitor SQL Server table and index growth which also stores measurements in units of kb.

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_PADDING ON;
GO
CREATE TABLE [dbo].[DiskVolume]
(
	 [DiskVolumeID] INT IDENTITY(1, 1) NOT NULL
	,[CheckDate] DATETIME NOT NULL
	,[Name] VARCHAR(128) NULL
	,[Label] VARCHAR(128) NULL
	,[DriveLetter] VARCHAR(2) NOT NULL
	,[CapacityKB] BIGINT NOT NULL
	,[FreeSpaceKB] BIGINT NOT NULL
	,[UsedSpaceKB] AS ([CapacityKB] - [FreeSpaceKB])
	,[PercentFree] AS (CONVERT(DECIMAL(18, 2), CAST([FreeSpaceKB] AS FLOAT) / CAST([CapacityKB] AS FLOAT) * 100))
	,[PercentUsed] AS (CONVERT(DECIMAL(18, 2), CAST([CapacityKB] - [FreeSpaceKB] AS FLOAT) / CAST([CapacityKB] AS FLOAT) * 100))
) 
ON [PRIMARY];
GO
SET ANSI_PADDING OFF;
GO

This next code section shows the definition of the procedure used to insert data into the table above. The idea is to pass in one XML parameter as data, containing all the information collected from all the disk drives. The procedure then reads the XML text provided as input, parses the text by using the MSXML parser, and then inserts the parsed data into the table.

The second parameter specifies how many days of data to retain in the table and then the procedure purges any expired data after completing the insert.

Reference: http://msdn.microsoft.com/en-us/library/ms187367.aspx

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[InsertDiskVolume]') AND [type] IN (N'P', N'PC'))
BEGIN
	EXECUTE ('CREATE PROCEDURE [dbo].[InsertDiskVolume] AS RAISERROR(''UNDEFINED!'', 16, 1);');
END;
GO

ALTER PROCEDURE [dbo].[InsertDiskVolume]
(
	@XmlData XML,
	@HistoryDays SMALLINT = 90
)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @hdoc AS INT;

	--Create an internal representation of the XML document.
	EXECUTE [dbo].[sp_xml_preparedocument] @hdoc OUTPUT, @XmlData;

	INSERT INTO [dbo].[DiskVolume] 
	(
			 [CheckDate]
			,[Name] 
			,[Label] 
			,[DriveLetter] 
			,[CapacityKB] 
			,[FreeSpaceKB] 
	)
	SELECT	 [CheckDate] = CURRENT_TIMESTAMP
			,[Name] = [Volume].[Name]
			,[Label] = [Volume].[Label]
			,[DriveLetter] = [Volume].[DriveLetter]
			,[CapacityKB] = [Volume].[Capacity] / 1024
			,[FreeSpaceKB] = [Volume].[FreeSpace] / 1024
	FROM	 OPENXML(@hdoc, '/root/cimv2/Win32_Volume', 0) 
			 WITH 
			 (
				 [Name] VARCHAR(128) '@Name'
				,[Label] VARCHAR(128) '@Label'
				,[DriveLetter] VARCHAR(2) '@DriveLetter'
				,[Capacity] BIGINT '@Capacity'
				,[FreeSpace] BIGINT '@FreeSpace'
			 )
			 AS [Volume];

	EXECUTE [dbo].[sp_xml_removedocument] @hdoc;
	
	DELETE	
	FROM	[dbo].[DiskVolume]
	WHERE	[CheckDate] < DATEADD(d, -@HistoryDays, CURRENT_TIMESTAMP);
END;
GO

So, now that we have our table and our insert procedure defined, the next thing to do is to test the insert procedure by passing in an XML parameter just as our PowerShell script would. I found this step to be a very helpful in the development process because the XML data needs to adhere to a specific format in the PowerShell code.

To test the procedure I have created the following T-SQL code to build an XML structured string, CAST it is as an XML data type and then call the procedure and see if the table was populated with any records.

SET NOCOUNT ON;

DECLARE @XmlData AS XML = N''
DECLARE @VarData AS VARCHAR(MAX) = '';

--Create xml structured string to test our insert procedure
SET @VarData = @VarData + N'<root>';
SET @VarData = @VarData + N'<cimv2>';
SET @VarData = @VarData + N'<Win32_Volume Name="E:\" Label="SQL" DriveLetter="E:" Capacity="214612045824" FreeSpace="213974716416"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="N:\" Label="MSDTC-SQL" DriveLetter="N:" Capacity="1038086144" FreeSpace="999579648"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="M:\" Label="MSDTC" DriveLetter="M:" Capacity="1038086144" FreeSpace="999579648"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="Q:\" Label="Quorum" DriveLetter="Q:" Capacity="1038086144" FreeSpace="991666176"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="X:\" Label="SQL-Audit" DriveLetter="X:" Capacity="536734593024" FreeSpace="536624115712"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="F:\" Label="SQL-Data" DriveLetter="F:" Capacity="2684218179584" FreeSpace="1120017907712"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="J:\" Label="SQL-Index" DriveLetter="J:" Capacity="536734531584" FreeSpace="339802390528"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="G:\" Label="SQL-Log" DriveLetter="G:" Capacity="1073605443584" FreeSpace="501874098176"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="L:\" Label="SQL-Perf" DriveLetter="L:" Capacity="107237863424" FreeSpace="90478137344"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="R:\" Label="SQL-TempDBData1" DriveLetter="R:" Capacity="107237801984" FreeSpace="46044217344"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="S:\" Label="SQL-TempDBData2" DriveLetter="S:" Capacity="107237801984" FreeSpace="107143036928"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="T:\" Label="SQL-TempDBLog" DriveLetter="T:" Capacity="53550710784" FreeSpace="49429544960"></Win32_Volume>';
SET @VarData = @VarData + N'<Win32_Volume Name="C:\" Label="" DriveLetter="C:" Capacity="214640357376" FreeSpace="98290384896"></Win32_Volume>';
SET @VarData = @VarData + N'</cimv2>';
SET @VarData = @VarData + N'</root>';

--Cast our xml structured string to an xml type
SET @XmlData = CAST(@VarData AS XML);

--Call our procedure
EXECUTE [dbo].[InsertDiskVolume] @XmlData;

--Was the data inserted?
SELECT *
FROM [dbo].[DiskVolume];

If everything was done correctly, you should have results similar to the following screenshot:

screenshot_01_monitoring-disk-space-sql-server-powershell

Now all we need is the Powershell script to generate the same XML structured data to pass into the stored procedure. The following is the PowerShell code, created to generate the XML data and calls the insert procedure. You should be able to copy/paste the PowerShell code below into your IDE of choice and run it without error. I’ve highlighted line #6 where you will need to change the connection string parameters and line #11 where you will need to changed the procedure name if needed.

[object]$Volumes = Get-WmiObject -NameSpace "root/cimv2" -ComputerName "." Win32_Volume -Filter "DriveType = 3 AND Label != 'System Reserved'";
[string]$XmlData = "<root><cimv2>"; 
$Volumes | % { $XmlData = $XmlData + "<Win32_Volume Name=`"" + $_.Name + "`" Label=`"" + $_.Label + "`" DriveLetter=`"" + $_.DriveLetter + "`" Capacity=`"" + $_.Capacity + "`" FreeSpace=`"" + $_.FreeSpace + "`"></Win32_Volume>" }; 
$XmlData = $XmlData + "</cimv2></root>";
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = "Server=<ServerName/Instance>;Database=<DatabaseName>;Integrated Security=TRUE;"; 
$SqlConnection.Open();
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand;
$SqlCommand.CommandTimeout = 120;
$SqlCommand.Connection = $SqlConnection;
$SqlCommand.CommandText = "EXECUTE [dbo].[InsertDiskVolume] @XmlData = N'$XmlData';";
$Result = $SqlCommand.ExecuteNonQuery();
$SqlConnection.Close();

The last step is to create a SQL Agent Job to run the PowerShell script on a recurring schedule. The following code will create such a job that runs every 24 hours and I’ve highlighted a few lines where changes have been made to the PowerShell code from above:

Line #45 is highlighted because I’m using the SQL Agent Token “MACH” and Escape Macro for the “Computer Name” and added a an extra set of single-quotes around “System Reserved”.

Line #48 is highlighted because I’m using the SQL Agent Token “SRVR” and Escape Macro for the Server Name running SQL Server. If the SQL Server instance is a named instance, this includes the instance name. You will still need to specify the appropriate database name and security options for the connection string.

Line #53 is highlighted because, again, you need to make sure the correct procedure name is specified and I’ve also added an extra set of single-quotes around the $XmlData PowerShell variable.

USE [msdb]; 
GO 
BEGIN TRANSACTION; 
	DECLARE @ReturnCode AS INT = 0; 
	DECLARE @jobId AS BINARY(16);
	
	IF NOT EXISTS (SELECT [name] FROM [msdb].[dbo].[syscategories] WHERE [name] = N'[Uncategorized (Local)]' AND [category_class] = 1) 
	BEGIN 
		EXECUTE @ReturnCode = [msdb].[dbo].[sp_add_category] 
				@class=N'JOB', 
				@type=N'LOCAL', 
				@name=N'[Uncategorized (Local)]'; 
				
		IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback; 
	END 

	EXECUTE @ReturnCode = [msdb].[dbo].[sp_add_job] 
			@job_name=N'Collect Disk Space Data', 
			@enabled=1, 
			@notify_level_eventlog=0, 
			@notify_level_email=0, 
			@notify_level_netsend=0, 
			@notify_level_page=0, 
			@delete_level=0, 
			@description=N'No description available.', 
			@category_name=N'[Uncategorized (Local)]', 
			@owner_login_name=N'sa', 
			@job_id = @jobId OUTPUT; 

	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback; 

	EXECUTE @ReturnCode = [msdb].[dbo].[sp_add_jobstep] 
			@job_id=@jobId, 
			@step_name=N'Query WMI', 
			@step_id=1, 
			@cmdexec_success_code=0, 
			@on_success_action=1, 
			@on_success_step_id=0, 
			@on_fail_action=2, 
			@on_fail_step_id=0, 
			@retry_attempts=0, 
			@retry_interval=0, 
			@os_run_priority=0, 
			@subsystem=N'PowerShell', 
			@command=N'[object]$Volumes = Get-WmiObject -NameSpace "root/cimv2" -ComputerName "$(ESCAPE_NONE(MACH))" Win32_Volume -Filter "DriveType = 3 AND Label != ''System Reserved''";
	[string]$XmlData = "<root><cimv2>"; $Volumes | % { $XmlData = $XmlData + "<Win32_Volume Name=`"" + $_.Name + "`" Label=`"" + $_.Label + "`" DriveLetter=`"" + $_.DriveLetter + "`" Capacity=`"" + $_.Capacity + "`" FreeSpace=`"" + $_.FreeSpace + "`"></Win32_Volume>" }; $XmlData = $XmlData + "</cimv2></root>";
	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
	$SqlConnection.ConnectionString = "Server=$(ESCAPE_NONE(SRVR));Database=<DatabaseName>;Integrated Security=TRUE;"; 
	$SqlConnection.Open();
	$SqlCommand = New-Object System.Data.SqlClient.SqlCommand;
	$SqlCommand.CommandTimeout = 120;
	$SqlCommand.Connection = $SqlConnection;
	$SqlCommand.CommandText = "EXECUTE [dbo].[InsertDiskVolume] @XmlData = N''$XmlData'';";
	$Result = $SqlCommand.ExecuteNonQuery();
	$SqlConnection.Close();', 
			@database_name=N'master', 
			@flags=0; 

	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback; 

	EXECUTE @ReturnCode = [msdb].[dbo].[sp_update_job] 
			@job_id = @jobId, 
			@start_step_id = 1; 

	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback; 

	EXECUTE @ReturnCode = [msdb].[dbo].[sp_add_jobschedule] 
			@job_id=@jobId, 
			@name=N'Daily - Every 24 hour(s)', 
			@enabled=1, 
			@freq_type=4, 
			@freq_interval=1, 
			@freq_subday_type=1, 
			@freq_subday_interval=0, 
			@freq_relative_interval=0, 
			@freq_recurrence_factor=0, 
			@active_start_date=20140707, 
			@active_end_date=99991231, 
			@active_start_time=0, 
			@active_end_time=235959; 

	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback; 

	EXECUTE @ReturnCode = [msdb].[dbo].[sp_add_jobserver] 
			@job_id = @jobId, 
			@server_name = N'(local)'; 

	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback; 

COMMIT TRANSACTION; 

GOTO EndSave 

QuitWithRollback: 
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION; 

EndSave:

Comments or suggestions are always welcome. The next blog post will be a follow-up on generating a weekly report and low-disk space alert emails. Please check back at a later time or follow us on twitter.

.

The post Monitoring Disk Space with SQL Server and PowerShell appeared first on SQL Professionals.

]]> https://www.jinyong.kim/blog/sql-scripts/2014/10/10/monitoring-disk-space-sql-server-powershell/feed/ 2 Insight into the SQL Server Agent Job Schedules https://www.jinyong.kim/blog/sql-scripts/2014/10/06/insight-into-sql-agent-job-schedules/ https://www.jinyong.kim/blog/sql-scripts/2014/10/06/insight-into-sql-agent-job-schedules/#comments Mon, 06 Oct 2014 16:00:37 +0000 http://www.jinyong.kim/?p=126 Every once in a while, I get a request to create a report of all the SQL Agent jobs and their schedules for a particular SQL Instance. The following script was…

The post Insight into the SQL Server Agent Job Schedules appeared first on SQL Professionals.

]]> Every once in a while, I get a request to create a report of all the SQL Agent jobs and their schedules for a particular SQL Instance. The following script was created by combining features from a handful of other similar types of scripts found on the internet which will query this information from the [msdb] database. In this script, I’ve added a feature to calculate an average run-time for each job based on any related job history information found. The script also does a nice job of showing how to convert the [msdb] integer-type date format: YYYYMMDD to a more readable format. I find this script particularly useful to use as a view in an administration type of database.

SELECT	 [JobName] = [jobs].[name]
		,[Category] = [categories].[name]
		,[Owner] = SUSER_SNAME([jobs].[owner_sid])
		,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
		,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
		,[Description] = [jobs].[description]
		,[Occurs] = 
				CASE [schedule].[freq_type]
					WHEN   1 THEN 'Once'
					WHEN   4 THEN 'Daily'
					WHEN   8 THEN 'Weekly'
					WHEN  16 THEN 'Monthly'
					WHEN  32 THEN 'Monthly relative'
					WHEN  64 THEN 'When SQL Server Agent starts'
					WHEN 128 THEN 'Start whenever the CPU(s) become idle' 
					ELSE ''
				END
		,[Occurs_detail] = 
				CASE [schedule].[freq_type]
					WHEN   1 THEN 'O'
					WHEN   4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)'
					WHEN   8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' + 
						LEFT(
							CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END , 
							LEN(
								CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END 
							) - 1
						)
					WHEN  16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
					WHEN  32 THEN 'The ' + 
							CASE [schedule].[freq_relative_interval]
								WHEN  1 THEN 'First'
								WHEN  2 THEN 'Second'
								WHEN  4 THEN 'Third'
								WHEN  8 THEN 'Fourth'
								WHEN 16 THEN 'Last' 
							END +
							CASE [schedule].[freq_interval]
								WHEN  1 THEN ' Sunday'
								WHEN  2 THEN ' Monday'
								WHEN  3 THEN ' Tuesday'
								WHEN  4 THEN ' Wednesday'
								WHEN  5 THEN ' Thursday'
								WHEN  6 THEN ' Friday'
								WHEN  7 THEN ' Saturday'
								WHEN  8 THEN ' Day'
								WHEN  9 THEN ' Weekday'
								WHEN 10 THEN ' Weekend Day' 
							END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)' 
					ELSE ''
				END
		,[Frequency] = 
				CASE [schedule].[freq_subday_type]
					WHEN 1 THEN 'Occurs once at ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 2 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 4 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 8 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					ELSE ''
				END
		,[AvgDurationInSec] = CONVERT(DECIMAL(18, 2), [jobhistory].[AvgDuration])
		,[Next_Run_Date] = 
				CASE [jobschedule].[next_run_date]
					WHEN 0 THEN CONVERT(DATETIME, '1900/1/1')
					ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ' ' + 
						 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ':'), 3, 0, ':'))
				END
FROM	 [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK) 
		 LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK) 
				 ON [jobs].[job_id] = [jobschedule].[job_id] 
		 LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK) 
				 ON [jobschedule].[schedule_id] = [schedule].[schedule_id] 
		 INNER JOIN [msdb].[dbo].[syscategories] [categories] WITh(NOLOCK) 
				 ON [jobs].[category_id] = [categories].[category_id] 
		 LEFT OUTER JOIN 
					(	SELECT	 [job_id], [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) + 
																(([run_duration] % 10000) / 100 * 60) + 
																 ([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])
						FROM	 [msdb].[dbo].[sysjobhistory] WITh(NOLOCK)
						WHERE	 [step_id] = 0 
						GROUP BY [job_id]
					 ) AS [jobhistory] 
				 ON [jobhistory].[job_id] = [jobs].[job_id];
GO

Reference: http://msdn.microsoft.com/en-us/library/ms178644.aspx

The post Insight into the SQL Server Agent Job Schedules appeared first on SQL Professionals.

]]> https://www.jinyong.kim/blog/sql-scripts/2014/10/06/insight-into-sql-agent-job-schedules/feed/ 18