When Shit Hits The SQL Agent Jobstep (T-SQL Scripting Series)

Problems

Due to a bug in SQL Server, a SQL Agent jobstep for executing an SSIS package that has a DateTime parameter, can get unusable on a Windows server with European Regional Settings, and more specific, a dd-mm-yyyy dateformat.
The jobstep is stored with the datetime parameter value in a d-m-yyyy hh:nn:ss format.
This gives two problems:

  1. During execution the following error occurs: String was not recognized as a valid DateTime
  2. When the jobstep is opened for editing, the same error occurs: “Microsoft SQL Server Management Studio String was not recognized as a valid DateTime. (mscorlib)”

Solution

Correct the jobstep in the msdb database: convert the datetime to a yyyy-mm-dd format, which does not give this error when the job is executed or when the jobstep is opened for editing.
You have to run this script every time you have changed the jobstep using SQL Server Management Studio.

TSQL-S01E03-WhenShitHitsTheSQL…

--\
---) Author: Hans Michiels
---) With this script you can correct a SQL Agent jobstep that has a DateTime parameter
---) and would otherwise be uneditable due to a bug in SQL Server.
---) You have to run this script every time you have changed the jobstep using SQL Server Management Studio.
--/
/*
(c) Copyright 2016 - hansmichiels.com
 
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
 
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
 
You should have received a copy of the GNU General Public License
along with this program. If not, see http://www.gnu.org/licenses/.
*/

UPDATE js

  SET command = REPLACE(js.command, expr3.original_date, expr5.corrected_date)

FROM [msdb].[dbo].[sysjobsteps] AS js

  CROSS APPLY ( SELECT
      CHARINDEX('(DateTime)', js.command) AS [datetime_pos]
      ) AS expr1

  CROSS APPLY ( SELECT
      CHARINDEX(';”\”', js.command, expr1.datetime_pos + 1) AS [start_pos],
      CHARINDEX(' ', js.command, expr1.datetime_pos + 1) AS [end_pos]
      ) AS expr2

  CROSS APPLY ( SELECT
      SUBSTRING(js.command, expr2.start_pos + 4, expr2.end_pos - start_pos - 4) AS [original_date]
      ) AS expr3

  CROSS APPLY ( SELECT
      CASE
      WHEN SUBSTRING(expr3.[original_date], 2, 1) = '-' AND SUBSTRING(expr3.[original_date], 4, 1) = '-'
        THEN '0' + SUBSTRING(expr3.[original_date], 1, 1) + '-0' + SUBSTRING(expr3.[original_date], 3, 6)
      WHEN SUBSTRING(expr3.[original_date], 2, 1) = '-' AND SUBSTRING(expr3.[original_date], 5, 1) = '-'
        THEN '0' + expr3.[original_date]
      WHEN SUBSTRING(expr3.[original_date], 3, 1) = '-' AND SUBSTRING(expr3.[original_date], 5, 1) = '-'
        THEN SUBSTRING(expr3.[original_date], 1, 3) + '0' + SUBSTRING(expr3.[original_date], 4, 6)
      ELSE expr3.[original_date]
      END AS [fixedwidth_date]
      ) AS expr4

  CROSS APPLY ( SELECT
      CONVERT(CHAR(10),
        CONVERT(DATE, SUBSTRING(expr4.[fixedwidth_date], 7, 4)
                    + SUBSTRING(expr4.[fixedwidth_date], 4, 2)
                    + SUBSTRING(expr4.[fixedwidth_date], 1, 2), 112)
                    , 120) AS [corrected_date]
    ) expr5

WHERE js.subsystem = 'SSIS'
  AND expr1.datetime_pos > 0
  AND expr2.start_pos > 0
  AND expr2.end_pos > expr2.start_pos
  AND SUBSTRING(expr4.fixedwidth_date, 3, 1) = '-'
  AND SUBSTRING(expr4.fixedwidth_date, 6, 1) = '-'

Download the script here.

Known limitations

This script works only for jobsteps with only one DateTime parameter.

Conclusion / Wrap up

With this script you can correct a SQL Agent jobstep that has a DateTime parameter and would otherwise be uneditable due to a bug in SQL Server.
You have to run this script every time you have changed the jobstep using SQL Server Management Studio.

(c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

Hans Michiels

Hans Michiels

Hans is an Independent Business Intelligence and Datawarehouse Consultant & Microsoft SQL Server Consultant, working in the Netherlands. He has been working in the software industry since 1996, with SQL Server since the year 2001, and since 2008 he has a primary focus on datawarehouse- and business intelligence projects using Microsoft technology, using a Datavault and Kimball architecture. He has a special interest in Datawarehouse Automation and Metadata driven solutions. * Certified in Data Vault Modeling: Certified Data Vault 2.0 Practitioner, CDVDM (aka Data Vault 1.0) * Certified in MS SQL Server: * MCSA (Microsoft Certified Solutions Associate) SQL Server 2012 - MCITP Business Intelligence Developer 2005/2008 - MCITP Database Developer 2005/2008 - MCITP Database Administrator 2005/2008

More Posts

Leave a Reply

Your e-mail address will not be published. Required fields are marked *