Tag Archives: jobstep

Data Vault Series, Datawarehouse series, Miscellaneous, SQL Server Series, SSIS Series, T-SQL Scripting Series, Windows User Series

Free download: Blog Yearbook 2016

I have made a compilation of all my blog posts of the past year, and it was more work than you would think.
Especially because it also contains a non-published new article.

After filling in the form you can download the e-book for free.

    I promise you the following:

    • I will not give or sell your email address to any third party.
    • I will not spam you.
    • If I start with a newsletter, you can easily unsubscribe or change the frequency.

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

    T-SQL Scripting Series

    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.