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.

Schedule Ola Hallengren’s Maintenance Solution (T-SQL Scripting Series)

Introduction

If you have you ever struggled with setting up a maintenance plan for SQL Server, you’ll be glad that Ola Hallengren came around. This very clever SQL Server DBA thought, well the SQL Server Maintenance Plans aren’t that great. It does the job, but out of the box some jobs are scheduled at exactly the same time, amongst other inefficiencies. We all can be thankful that he wrote his own MaintenanceSolution.sql, which has won several awards and can be downloaded for free here.

F5 and then?

If you have downloaded Ola Hallengren’s MaintenanceSolution.sql, you still have to schedule the SQL Agent Jobs. But it is logical that this is not included, as every company, every server could have different requirements for backup, integrity checks, index optimization and statistics maintenance.

.. Configure!

In the script below you will find a CONFIGURATION section where you can configure how the SQL Agent Jobs created by Ola Hallengren should be scheduled. Just for your convenience, hope you like it.

Ola_Scheduling.sql

--\
---) Author: Hans Michiels
---) Creates jobschedules in SQL Server Agent jobs that
---) were created by Ola Hallengren's maintenance solution.
--/
/*
(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/.
*/

USE [msdb]
GO

--\-------------------------------------------------------------------------------------
---) DECLARATIONS
--/-------------------------------------------------------------------------------------
DECLARE @cur_days VARCHAR(50)
     -- @cur_days contains a space separated list of weekdays, e.g. 'mon tue wed thu fri sat sun',
     -- or use 'all' for all days.
     -- Please note that using 'mon tue wed thu fri sat sun' will create a weekly job with
     -- all days checked while using 'all' will create a daily job.

DECLARE @cur_start_time INT
     -- @cur_start_time: the starttime of the job in integer format (hhmmss)

DECLARE @cur_subday_interval INT
     -- @cur_subday_interval: N in 'every N seconds/minutes/hours (for seconds max N = 100)
     -- Use 0 when N/A

DECLARE @cur_intraday_unit VARCHAR(7)
     -- @cur_intraday_unit: seconds (s), minutes (m) or hours (h).
     -- For configuration you can use both the full name or the first letter.

DECLARE @cur_delete_any_schedule CHAR(1)
     -- @cur_delete_any_schedule: indicates if any schedule for the given jobname should be deleted.
     -- Use NULL if you want to use the default value (recommended).
     -- It is recommended only to use 'N' if you use multiple schedules for the same job.

DECLARE @default_delete_any_schedule CHAR(1)
     -- @default_delete_any_schedule: valued used if in the configuration below
     -- [delete_any_schedule] is NULL for a specific job.

DECLARE @delete_schedules_only CHAR(1)
     -- @delete_schedules_only: if set to 'Y', only the existing schedules are deleted.
     -- No new schedules are made.
     -- If you use this setting, make sure to set @default_delete_any_schedule also to 'Y'
     -- without overruling it to 'N' for specific jobs.

DECLARE @cur_schedule_id int
DECLARE @cur_job_name SYSNAME
DECLARE @cur_schedule_name SYSNAME
DECLARE @cur_freq_type INT
DECLARE @cur_freq_interval INT
DECLARE @cur_freq_subday_type INT

DECLARE @active_start_date INT
DECLARE @message VARCHAR(500)

SET @active_start_date = CONVERT(INT, CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112));

--\-------------------------------------------------------------------------------------
---) CONFIGURATION
--/-------------------------------------------------------------------------------------
SET @default_delete_any_schedule = 'Y';
-- If you DO NOT want to delete all existing schedules for all jobs in your configuration,
-- change @default_delete_any_schedule to 'N'.
-- Keeping this value on 'Y' is usually safe to do and this prevents that old schedules
-- might in some cases not be deleted which can give trouble due to multiple (conflicting)
-- schedules.
-- !! ONLY IF you have more than one schedule for a job, you should set [delete_any_schedule]
-- in the configuration above to 'N' for that specific job.

SET @delete_schedules_only = 'N';

DECLARE daily_weekly_cursor CURSOR LOCAL STATIC FOR
SELECT CONVERT(SYSNAME, NULL) AS [job_name], CONVERT(SYSNAME, NULL) AS [schedule_name],
        CONVERT(INT, NULL) AS [start_time], CONVERT(VARCHAR(50), NULL) AS [days],
        CONVERT(INT, NULL) AS [subday_interval],
        CONVERT(VARCHAR(7), NULL) AS [intraday_unit],
        CONVERT(CHAR(1), NULL) AS [delete_any_schedule]

-------------[job_name]------------------------------------[schedule]--[start]-[days]-----------------------[subday ]-[intraday]-[delete_any]
-----------------------------------------------------------[name ]--[time ]------------------------------[interval]-[unit ]-[schedule ]
UNION SELECT 'CommandLog Cleanup' , 'Schedule', 180000, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'Output File Cleanup' , 'Schedule', 180500, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'sp_delete_backuphistory' , 'Schedule', 181000, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'sp_purge_jobhistory' , 'Schedule', 181500, 'wed sun' , 0 , NULL , NULL

UNION SELECT 'DatabaseIntegrityCheck - SYSTEM_DATABASES' , 'Schedule', 182000, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'DatabaseIntegrityCheck - USER_DATABASES' , 'Schedule', 191500, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'DatabaseBackup - SYSTEM_DATABASES - FULL' , 'Schedule', 001500, 'all' , 0 , NULL , NULL
UNION SELECT 'DatabaseBackup - USER_DATABASES - FULL' , 'Schedule', 003000, 'all' , 0 , NULL , NULL

UNION SELECT 'IndexOptimize - USER_DATABASES' , 'Schedule', 081000, 'mon tue wed thu fri sat sun', 0 , NULL , NULL

/* -- Weekly full, other days diff example:
UNION SELECT 'DatabaseBackup - USER_DATABASES - FULL' , 'Schedule', 201500, 'fri' , 0 , NULL , NULL
UNION SELECT 'DatabaseBackup - USER_DATABASES - DIFF' , 'Schedule', 201500, 'mon tue wed thu sat sun' , 0 , NULL , NULL
*/

/* -- Transaction log example / Example of use of [delete_any_schedule] with more than one schedule
UNION SELECT 'DatabaseBackup - USER_DATABASES - LOG' , 'Weekdays', 000001, 'mon tue wed thu fri' , 10 , 'minutes', 'Y'
UNION SELECT 'DatabaseBackup - USER_DATABASES - LOG' , 'Weekend' , 000001, 'sat sun' , 2 , 'hours', 'N'
*/

  

--\-------------------------------------------------------------------------------------
---) CREATION OF THE SCHEDULES
--/-------------------------------------------------------------------------------------
OPEN daily_weekly_cursor
FETCH NEXT FROM daily_weekly_cursor
INTO @cur_job_name, @cur_schedule_name, @cur_start_time, @cur_days,
     @cur_subday_interval, @cur_intraday_unit, @cur_delete_any_schedule

WHILE @@FETCH_STATUS = 0
BEGIN

    --\
    ---) Check if it is the dummy row to specify datatypes:
    --/
    IF @cur_job_name IS NULL
    BEGIN
      -- RAISERROR('NULL', 0, 1) WITH NOWAIT;
      FETCH NEXT FROM daily_weekly_cursor
      INTO @cur_job_name, @cur_schedule_name, @cur_start_time, @cur_days,
           @cur_subday_interval, @cur_intraday_unit, @cur_delete_any_schedule
    END

    SET @message = 'Job: ' + @cur_job_name
    RAISERROR(@message , 0, 1) WITH NOWAIT;

    --\
    ---) Get the current schedule_id, if any.
    --/
    SELECT @cur_schedule_id = -1;
    WHILE @cur_schedule_id IS NOT NULL
    BEGIN
        SELECT @cur_schedule_id = (
            SELECT TOP 1 sch.schedule_id
            FROM msdb.dbo.sysschedules sch
              JOIN msdb.dbo.sysjobschedules js
                ON js.schedule_id = sch.schedule_id
              JOIN msdb.dbo.sysjobs jobs
                ON jobs.job_id = js.job_id
             WHERE jobs.name = @cur_job_name
               AND ( ISNULL(@cur_delete_any_schedule, @default_delete_any_schedule) = 'Y' OR sch.name = @cur_schedule_name )
            );

        --\
        ---) If schedule exists, delete it first.
        --/
        IF @cur_schedule_id IS NOT NULL
        BEGIN
            EXEC msdb.dbo.sp_delete_schedule @schedule_id = @cur_schedule_id, @force_delete = 1;
            SET @message = ' ' + 'Schedule deleted with @schedule_id = ' + CONVERT(VARCHAR, @cur_schedule_id)
            RAISERROR(@message , 0, 1) WITH NOWAIT;
        END
    END

    --\
    ---) Set @cur_freq_type depending on days specification.
    --/
    SELECT @cur_freq_type = CASE @cur_days WHEN 'all' THEN 4 ELSE 8 END;

    --\
    ---) Set @cur_freq_interval depending on days specification.
    --/
    SELECT @cur_freq_interval = 0, @cur_days = ' ' + @cur_days + ' ';
    IF CHARINDEX(' all ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 1;
    IF CHARINDEX(' sun ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 1;
    IF CHARINDEX(' mon ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 2;
    IF CHARINDEX(' tue ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 4;
    IF CHARINDEX(' wed ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 8;
    IF CHARINDEX(' thu ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 16;
    IF CHARINDEX(' fri ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 32;
    IF CHARINDEX(' sat ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 64;
        
    IF @cur_freq_interval = 0
    BEGIN
      RAISERROR('Days string does not contain any valid day abbreviations', 16, 1);
    END

    IF @delete_schedules_only != 'Y'
    BEGIN
        IF @cur_subday_interval = 0
        BEGIN
            EXEC msdb.dbo.sp_add_jobschedule @job_name=@cur_job_name, @name=@cur_schedule_name,
          @enabled=1,
          @freq_type=@cur_freq_type,
          @freq_interval=@cur_freq_interval,
          @freq_subday_type=1,
          @freq_subday_interval=0,
          @freq_relative_interval=0,
          @freq_recurrence_factor=1,
          @active_start_date=@active_start_date,
          @active_end_date=99991231,
          @active_start_time=@cur_start_time,
          @active_end_time=235959, @schedule_id = @cur_schedule_id OUTPUT;

            SET @message = ' ' + 'Schedule created for days' + @cur_days + 'at ' + CONVERT(VARCHAR, @cur_start_time)
            RAISERROR(@message , 0, 1) WITH NOWAIT;

        END ELSE BEGIN

            SELECT @cur_freq_subday_type =
            CASE LOWER(LEFT(@cur_intraday_unit, 1))
            WHEN 'h' THEN 8
            WHEN 'm' THEN 4
            WHEN 's' THEN 2
            ELSE 0
            END

            IF @cur_freq_subday_type = 0
            BEGIN
              RAISERROR('''intraday_unit'' must contain a valid value when ''subday_interval'' contains a number greater than 0. Please check your configuration.', 16, 1);
            END

            EXEC msdb.dbo.sp_add_jobschedule @job_name=@cur_job_name, @name=@cur_schedule_name,
          @enabled=1,
          @freq_type=@cur_freq_type,
          @freq_interval=@cur_freq_interval,
          @freq_subday_type=@cur_freq_subday_type,
          @freq_subday_interval=@cur_subday_interval,
          @freq_relative_interval=0,
          @freq_recurrence_factor=1,
          @active_start_date=@active_start_date,
          @active_end_date=99991231,
          @active_start_time=@cur_start_time,
          @active_end_time=235959, @schedule_id = @cur_schedule_id OUTPUT;

            SET @message = ' ' + 'Schedule created for days' + @cur_days + 'every ' + CONVERT(VARCHAR, @cur_subday_interval) + ' ' + @cur_intraday_unit
            RAISERROR(@message , 0, 1) WITH NOWAIT;

        END
    END
    RAISERROR('-----' , 0, 1) WITH NOWAIT;
    
    SET @cur_schedule_id = NULL;
    FETCH NEXT FROM daily_weekly_cursor
    INTO @cur_job_name, @cur_schedule_name, @cur_start_time, @cur_days,
         @cur_subday_interval, @cur_intraday_unit, @cur_delete_any_schedule
END

CLOSE daily_weekly_cursor
DEALLOCATE daily_weekly_cursor

Download the script here.

(Note: the downloadable script has slightly better indenting than the html version above).

TSQL-S01E02-Schedule

Conclusion / Wrap up

With this script you can easily schedule the SQL Agent Jobs that Ola Hallengren’s MaintenanceSolution-script creates.

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

How to copy a database table structure (T-SQL Scripting Series)

Problem

Do you recognize the need to copy a table structure in MS SQL Server, for temporary or permanent usage?

Most of you will know the famous

Insert Into Example.sql
SELECT TOP 0 *
INTO [dbo].[some unexisting table]
FROM [dbo].[some existing table]

But this approach has some limitations: Computed columns change into regular columns, and primary and foreign keys are not copied. Sometimes you just need more than this.

Solution

So I have made a script to copy a SQL server table structure with foreign keys but without indexes.
This script handles user defined types and computed columns gracefully.

My credits go to Aaron Bertand (see his post on mssqltips). I used his script for copying the foreign keys and added my own parts to it (download it here).

TSQL-S01E01-copy_table

TSQL-S01E01-CopyTableStructure.sql

--\
---) Author: Hans Michiels
---) Script to copy a sql server table structure with foreign keys but without indexes.
---) This script handles user defined types and computed columns gracefully.
--/
/*
(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 .
*/

--\
---) VARIABLES DECLARATIONS
--/
DECLARE @CrLf NVARCHAR(2)
DECLARE @Indent NVARCHAR(2)
DECLARE @nsql NVARCHAR(MAX)
DECLARE @SimulationMode CHAR(1)
DECLARE @SourceSchemaAndTable NVARCHAR(260)
DECLARE @TargetSchemaAndTable NVARCHAR(260)
DECLARE @FkNameSuffix NVARCHAR(128)
DECLARE @TableOptions NVARCHAR(500)

--\
---) CONFIGURATION: set the source and target schema/tablename here, and some other settings.
--/
SELECT
    @SimulationMode = 'Y' -- Use Y if you only want the SQL statement in the output window without it being executed.
  , @SourceSchemaAndTable = '[dbo].[Order]'
  , @TargetSchemaAndTable = '[dbo].[OrderCopy]'
  , @TableOptions = ' WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]'
  , @FkNameSuffix = '_' + REPLACE(CAST(NEWID() AS VARCHAR(40)), '-', '') -- A Guid is added to the foreign key name to make it unique.
  , @CrLf = CHAR(13) + CHAR(10)
  , @Indent = SPACE(2)
  -- For 'min' script use this (in case sql is near 4000 characters):
  -- , @CrLf = ' '
  -- , @Indent = ''

--\
---) BUILD SQL FOR CLONING TABLE
--/
SELECT @nsql
    = ISNULL(@nsql, '')
    + CASE col_sequence WHEN 1 THEN
      @CrLf + 'IF OBJECT_ID(N''' + @TargetSchemaAndTable + ''', ''U'') IS NOT NULL DROP TABLE ' + @TargetSchemaAndTable + ';'
    + @CrLf + 'CREATE TABLE ' + @TargetSchemaAndTable + @CrLf + @Indent + '( ' ELSE @CrLf + @Indent + ', ' END
    + [definition]
FROM (
      SELECT ROW_NUMBER() OVER (PARTITION BY tb.object_id ORDER BY tb.object_id, col.column_id) AS col_sequence
        , QUOTENAME(col.name) + ' '
        + COALESCE(
            'AS ' + cmp.definition + CASE ISNULL(cmp.is_persisted, 0) WHEN 1 THEN ' PERSISTED ' ELSE '' END,
            CASE
              WHEN col.system_type_id != col.user_type_id THEN QUOTENAME(usr_tp.schema_name) + '.' + QUOTENAME(usr_tp.name)
              ELSE
                QUOTENAME(sys_tp.name) +
                CASE
                  WHEN sys_tp.name IN ('char', 'varchar', 'binary', 'varbinary') THEN '(' + CONVERT(VARCHAR, CASE col.max_length WHEN -1 THEN 'max' ELSE CAST(col.max_length AS varchar(10)) END) + ')'
                  WHEN sys_tp.name IN ('nchar', 'nvarchar') THEN '(' + CONVERT(VARCHAR, CASE col.max_length WHEN -1 THEN 'max' ELSE CAST(col.max_length/2 AS varchar(10)) END) + ')'
                  WHEN sys_tp.name IN ('decimal', 'numeric') THEN '(' + CAST(col.precision AS VARCHAR) + ',' + CAST(col.scale AS VARCHAR) + ')'
                  WHEN sys_tp.name IN ('datetime2') THEN '(' + CAST(col.scale AS VARCHAR) + ')'
                  ELSE ''
                END
            END
            )
        + CASE col.is_nullable
            WHEN 0 THEN ' NOT NULL'
            ELSE CASE WHEN cmp.definition IS NULL THEN ' NULL' ELSE ' ' END
          END AS [definition]
       FROM sys.tables tb
       JOIN sys.schemas sch
         ON sch.schema_id = tb.schema_id
       JOIN sys.columns col
         ON col.object_id = tb.object_id
       JOIN sys.types sys_tp
         ON col.system_type_id = sys_tp.system_type_id
        AND col.system_type_id = sys_tp.user_type_id
       LEFT JOIN
            (
            SELECT tp.*, sch.name AS [schema_name]
            FROM sys.types tp
            JOIN sys.schemas sch
            ON tp.schema_id = sch.schema_id
            ) usr_tp
         ON col.system_type_id = usr_tp.system_type_id
        AND col.user_type_id = usr_tp.user_type_id
       LEFT JOIN sys.computed_columns cmp
         ON cmp.object_id = tb.object_id
        AND cmp.column_id = col.column_id
      WHERE tb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U')
      ) subqry
;
SELECT @nsql
    = ISNULL(@nsql, '')
    + CASE col_sequence
        WHEN 1 THEN @CrLf + ', PRIMARY KEY ' + CASE is_clustered_index WHEN 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END
           + @CrLf + @Indent + '( '
        ELSE @CrLf + @Indent + ', '
      END
    + QUOTENAME(pk_cols.column_name)
    + CASE is_descending_key
        WHEN 1 THEN ' DESC'
        ELSE ' ASC'
      END
FROM (
      SELECT TOP 2147483647 sch.name as schema_name, tb.name as table_name, col.name as column_name
               , ROW_NUMBER() OVER (PARTITION BY tb.object_id ORDER BY tb.object_id, ic.key_ordinal) AS col_sequence
               , ic.is_descending_key
               , CASE WHEN idx.index_id = 1 THEN 1 ELSE 0 END AS [is_clustered_index]
       FROM sys.tables tb
       JOIN sys.schemas sch
         ON sch.schema_id = tb.schema_id
       JOIN sys.indexes idx
         ON idx.is_primary_key = 1
        AND idx.object_id = tb.object_id
       JOIN sys.index_columns ic
         ON is_included_column = 0
        AND ic.object_id = tb.object_id
        AND ic.index_id = idx.index_id
       JOIN sys.columns col
         ON col.column_id = ic.column_id
        AND col.object_id = tb.object_id
      WHERE tb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U')
      ORDER BY tb.object_id, ic.key_ordinal
      ) pk_cols
 
SELECT @nsql = @nsql + @CrLf + @indent + ') ' + @TableOptions

IF @SimulationMode = 'Y'
BEGIN
  PRINT '-- Simulation mode: script is not executed.'
END
PRINT @nsql;
IF @SimulationMode != 'Y'
BEGIN
  EXEC(@nsql);
END
 
--\
---) Copy foreign key constraints
---) A guid is added to the foreign key name to make it unique within the database.
--/
SET @nsql = N'';
SELECT @nsql += N'
ALTER TABLE '

      + @TargetSchemaAndTable
      + ' ADD CONSTRAINT [' + LEFT(fk.name + @FkNameSuffix, 128) + '] '
      + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(col.name)
      -- get all the columns in the constraint table
      FROM sys.columns AS col
      JOIN sys.foreign_key_columns AS fkc
        ON fkc.parent_column_id = col.column_id
        AND fkc.parent_object_id = col.[object_id]
      WHERE fkc.constraint_object_id = fk.[object_id]
      ORDER BY fkc.constraint_column_id
      FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
      + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rtb.name)
      + '('
      + STUFF((SELECT ',' + QUOTENAME(col.name)
      -- get all the referenced columns
      FROM sys.columns AS col
      JOIN sys.foreign_key_columns AS fkc
        ON fkc.referenced_column_id = col.column_id
       AND fkc.referenced_object_id = col.[object_id]
      WHERE fkc.constraint_object_id = fk.object_id
      ORDER BY fkc.constraint_column_id
      FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
JOIN sys.tables AS rtb -- referenced table
   ON fk.referenced_object_id = rtb.[object_id]
JOIN sys.schemas AS rs
   ON rtb.[schema_id] = rs.[schema_id]
JOIN sys.tables AS ctb -- constraint table
   ON fk.parent_object_id = ctb.[object_id]
WHERE rtb.is_ms_shipped = 0 AND ctb.is_ms_shipped = 0
  AND ctb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U');

IF @SimulationMode = 'Y'
BEGIN
  PRINT '-- Simulation mode: script is not executed.'
END
PRINT @nsql;
IF @SimulationMode != 'Y'
BEGIN
  EXEC(@nsql);
END

Download the script here.

Conclusion / Wrap up

With this script you can copy the structure of a table including primary and foreign keys, computed columns and columns using a user defined datatype.
Last updated: 14 July, 2016.

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

Connect to a removable drive in a Hyper V Virtual Machine (Windows User Series)

Introduction

This post is based on using Windows 10 on the host and Windows Server 2012 R2 on the guest (Virtual Machine), but on Windows 7, 8 and 8.1 things work similarly.

People coming from a VMWare environment might wonder what this is all about. What is the point, right?

I must admit, sharing or mapping a drive in a Hyper V virtual machine (a.k.a. “guest”) is not such a nobrainer as in VMWare. But no worries, with a little bit of effort it can be done easily.

So, what is the problem?

Well, I mount a VeraCrypt container on my host machine, and want to see the drive in my guest machine.  Or plug in a USB pen drive or removable harddisk, and use it on the guest machine.

Common ways to connect to a (removable) drive

I did it my way

I was looking for a more convenient way, that is, one I have to set up only once, and does not depend on the RDP protocol.

In my opinion a networkshare was the way to go.

But there were a few challenges:

  1. Windows tends to “forget” a shared drive or folder once this drive has been gone (usb media removed or VeraCrypt container unmounted). This is a problem, because then you have to configure the removable drive for sharing every time it is mounted.
  2. I did not want to do a round trip to my router, I want it to be a host/guest only party.

But I managed to get it working, and in the next paragraph I will tell you how.

myway3

Steps to do it “my way”

Again (see my previous post), the magic word to get this working is .. a junction!

Alright, having said that, where do we start?

We need set up a few things;

  1. An internal network connection between guest and host OS.
  2. A junction pointing to a removable drive letter on the host.
  3. Share that junction to one or more Windows users on the host.
  4. Create a mapped network drive on the guest.

Important note : if you want to map more than one drive on the host, you have to edit the hosts file on the virtual machine. After Step 4 below, I will explain how to do this.

Are you ready? Let’s roll up our sleeves and get going!

Step 1 – Set up an internal network connection between guest and host OS

1a – Create a Virtual Network Switch

Start Hyper V Manager, and select Virtual Switch Manager from the Actions pane.

WinS01E02_Step1a1

Create a new virtual network switch of type Internal.

WinS01E02_Step1a2

Give the Virtual Switch a name (I couldn’t think of a better name than “Internal”), leave other options as they are by default and press OK.

WinS01E02_Step1a3

1b – Add a network adapter to the virtual machine and configure it to use the Internal Switch

Now, open the Settings for the virtual machine.

WinS01E02_Step1b1

Choose Add HardwareNetwork AdapterWinS01E02_Step1b2

Configure the network adapter to use the Virtual switch Internal (or any other name you gave it). Press OK.

WinS01E02_Step1b3

1c – Find the Internal IP Address of the host

Still on the host, start a command prompt and type ipconfig. 

Then search for an Ethernet Adapter vEthernet (Internal) and write down the Autoconfiguration IPv4 Address. You will need it in a few minutes.

WinS01E02_Step1c1

 

Step 2 – Create a junction pointing to a removable drive letter on the host

Insert your USB storage media or mount a VeraCrypt File container.

For the example I use drive P on the host. Please replace P everywhere with the driveletter of your removable media.

Now find a folder on a fixed drive, e.g. where you store your Hyper V Virtual Machines. In the example I will use E:\HYPER_V\SHARES

Start a command prompt and type

mklink /j “E:\HYPER_V\SHARES\P” “P:\”

 

Replace the foldername and the drive letter with your values.
The output of the command window should confirm that the junction was made:
WinS01E02_Step2a

People who have read my previous post in my Windows User Series might remember that I preached that it is not recommended to make junctions pointing to removable drives. Well, this post proves that there is at least one exception to this rule of thumb!

Step 3 – Share that junction to one or more Windows users on the host.

Open the Properties of the junction E:\HYPER_V\SHARES\P and select tab Sharing. Press Advanced Sharing.

WinS01E02_Step3a

 

 

Select Share this folder and press Permissions.

WinS01E02_Step3b

Remove Everyone from Share Permissions and add at least one user from the host. Give this user the appropriate permissions.

Tip: if possible, use a (special) account from the host of which the password never changes, for reasons I will tell below.

WinS01E02_Step3c

 

Step 4 – Create a mapped network drive on the guest

Now, in Hyper V Manager, start the Virtual machine.

Once the Virtual machine is started, log in, start a Windows Explorer and choose Map Network Drive from the menu.

Choose a Drive (can be the same letter as on the host, but this is not necessary).

For Folder, type \\<Host Internal IP Address>\<Share Name>

(or \\<Host name from hosts file>\<Share Name> if you have modified the hosts file)

Check Reconnect at sign-in (if you think this is convenient).

If you do not want a permanently mapped drive, you could create a cmd file with a net use command, and execute it when you need to map the drive. 

Check Connect using different credentials.

WinS01E02_Step4a

In the Windows Security screen, enter a username of the host that has permissions on the share you created earlier.

Note: to indicate that you use a username of the host machine, type the host computer name and a backslash in front of the user name .

If you check Remember my credentials, the credentials are stored in the Windows Credential Manager of the guest (Virtual Machine), so you don’t have to provide them every time you access the mapped drive. But don’t forget, that when you change the password from the account on the host, you have to change the saved password also in the Credential Manager of the guest (accessible through Control Panel). When you are using names from the hosts file instead of the IP address, the same credentials will even occur multiple times, e.g. for “host1”, “host2”, etc! Therefore, it is best to use a (special) account from the host of which the password never changes.

WinS01E02_Step4b

That’s it!

WinS01E02_Step4c

Remember, as soon as you plug in the removable drive on the host, it will be available immediately in the Virtual Machine.

If you get this message, you know that the removable drive is not available on the host (yet). You have to plug it in first.

WinS01E02_Step4d

Appendix – Only needed if you want to map more than one drive: edit the hosts file.

Following steps 1 to 4 you can map only one drive, because if you try to connect a second drive, your will get a “Multiple connections to a server or shared resource by the same user, using more than one user name, are not allowed… ” error as shown below.

WinS01E02_Limitations

To map multiple drives, you have to do a little trick so that your guest thinks you are connecting to different computers ..

Start Windows explorer on the guest

Go to your virtual machine, start Windows Explorer, and go to the folder C:\Windows\System32\drivers\etc.

 Copy and adjust the hosts file

Copy the file “hosts” (it does not have a file name extension) to a different temporary folder, e.g. your desktop folder.

Open the file with a text editor, e.g. Notepad and add the Internal IPv4 adress of the host, followed by a space and then a name. The trick really is to add multiple lines with the same IP adress but a different name. I just keep it simple by numbering the names but you can do whatever you want, as long as the names are different.

169.254.151.11 host1
169.254.151.11 host2

Finally your hosts file should look similar to the one below (I have added a few more so my hosts file is “future proof”):

WinS01E02_Step5b

Save and close the file and copy it back to the original folder C:\Windows\System32\drivers\etc. Confirm to replace the existing file.

Now walk trough Steps 2 to 4 for each network drive you want to map. Instead of using the IP address, use a name from the hosts file, for each mapped drive a different name, and you will not get the “Multiple connections” error.

Here you will see an example of the endresult: I mapped drives H, P and Z using names host1, host2 and host3.

WinS01E02_Step53

Conclusion / wrap up

In this blog post, I have explained how to map a removable drive on the host operating system as a network drive in a Hyper V Virtual Machine.

By using a junction on a fixed drive on the host, a Windows problem forgetting that a removable drive was shared, can be circumvented.

By using an Internal network connection, there is no router round trip, nor a dependency to use a remote desktop connection to start up the guest. Also multiple virtual machines could have access to the same drive at the same time.

Finally by changing the hosts file, you can connect to multiple (removable) drives on the host computer.

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

Windows drive full? Use junctions! (Windows User Series)

Introduction

Microsoft Windows is not so bad, is it? But in my experience it can be greedy as it comes to disk usage. Not that this is necessarily always Windows to blame, but also third party software or other reasons.

Whatever the reason is, if it happens you have a problem. But don’t panic, luckily there is something you can do about it!

Disk consumption in Windows is like a glass of water being filled until it would overflow ..
Disk consumption in Windows can be like a glass of water being filled until it would overflow ..

 

Use cases

But what situations are we talking about? I can give you some examples:

  • Windows updates eating disk space (does the folder “SoftwareDistribution” ring a bell?)
  • Visual Studio 2015 with full mobile development installation taking 40GB for that part only!
  • Software for navigation equipment that does need temporary space for downloading an update but has no way to choose the folder to download it to (my experience was with Garmin).
  • Just lots of huge files in “My music” or “My videos”, where basicly of course, there is no one to blame but you.
  • On a computer with a large amount of memory, having “hybernate” enabled. If you put a computer in “hybernate” all the memory contents is saved to disk, so when you have 16GB of RAM, you will need this amount of free disk space.
  • You have partitioned your disk to a “Windows” and a “Data” partition (aka a C- and a D-drive), but your underestimated the disk space required for the C-drive.
  • Your disk is not full but you want to store user data (documents, pictures, music and so on) on a different drive, e.g. so you can easily restore an image of Windows without copying your user data back after the restore.

 

Ways to free up disk space on drive C

The are more ways to free up disk space. However I want to focus on one solution, here is a (non-complete) list of things you could do:

  • Compress folders that you do not use frequently. This will save disk space but will cost cpu power when accessing the files in that folder(s).
  • If you have partitioned your disk, increase the size of the C-drive (and therewith decrease the size of the D-drive).
  • Using Windows utilities to free up disk space (can help but is not always you can regain enough diskspace with this)
  • Uninstall programs you do no longer need.
  • Use junctions. This is the solution I will dive into in this blog post.

 

Using junctions – prerequisites

There is one important requirement when it comes to junctions: you need to have more than one (fixed) disk or partition in your computer. If it is a second physical disk or a second partition on one disk does not matter. And of course you need to have free disk space on that second disk or partition.

So when you have a notebook with only a small sized SSD, and it is full, junctions can not do much for you.

WARNING! Don’t make junctions pointing to folders on removable drives, like USB pen drives, SD cards and so on for system-critical folders, otherwise applications might stop working or Windows would not even start!

Using junctions – how it works

Definition

First of all, what the heck is a junction?

I got to know junctions (also called a soft link) as a shortcut for a folder. This shortcut behaves like a folder but redirects all applications that access that folder to a different folder. Cool, hey?

If you want to know more: read more on MSDN

A junction in the Netherlands (Rotterdam)
A traffic junction in the Netherlands (Rotterdam)

Examples of use

An application taking up too much space

Okay, suppose you have Garmin software installed, you have only a few gigabytes free disk space and want to install a Garmin update on your device. Before starting to download and install the update, this is what you can do:

  1. Create a cmd script and store it on your computer (call it e.g. create_junctions_garmin.cmd)
  2. Paste this into the empty cmd file. Adjust the bold folder names if needed.

@echo off
echo — STEP 1 – CREATE TARGET FOLDER(S) —
pause
mkdir D:\Junctions
mkdir D:\Junctions\ProgramData
mkdir D:\Junctions\ProgramData\Garmin
echo Please check that the previous step was completed succesfully . . .
echo If errors occurred close the command window WITHOUT PRESSING ENTER ! ! !
pause
echo — STEP 2 – COPY FOLDER AND ALL SUBFOLDERS AND FILES —
pause
xcopyC:\ProgramData\Garmin” “D:\Junctions\ProgramData\Garmin” /E /K /X
echo Please check that the previous step was completed succesfully . . .
echo If errors occurred close the command window WITHOUT PRESSING ENTER ! ! !
pause
echo — STEP 3 – REMOVE SOURCE FOLDER —
pause
rmdir /s /q “C:\ProgramData\Garmin
echo Please check that the previous step was completed succesfully . . .
echo If errors occurred close the command window WITHOUT PRESSING ENTER ! ! !
pause
echo — STEP 4 – CREATE JUNCTION FOR SOURCE FOLDER —
pause
mklink /j “C:\ProgramData\Garmin” “D:\Junctions\ProgramData\Garmin
pause

3. Run the cmd file as Administrator and check that it runs without errors. Alternatively you could also move the folder including subfolders and files manually and create a cmd script that only contains STEP 4 – CREATE JUNCTION FOR SOURCE FOLDER

If files are locked, maybe you have to reboot and start Windows in Safe mode, then run the cmd script again.

Moving userdata to a different drive

However Windows offers to some extend the possibility to move these folders, in my experience still downloaded files, pictures or documents sometimes are stored on the C-drive. By creating junctions this is not the case.

I recommend to move this user folders manually and create only the junctions with a cmd script.

Warning / disclaimer: However this works fine for me on Windows 7 and 10 and I do not expect any problems if you follow the procedure, please try this at your own risk.

Note: this works also on non-English versions of Windows as the displayed localized folder names (e.g. in Dutch: “Documenten” for “Documents”) are not the actual folder names. So for the junctions you need to use the English names.

This is what you can do (adjust the bold folder names: replace Hans with your Windows username and replace D:\USERDATA with some other folder if you like)

  • Create a folder D:\USERDATA\Hans
  • Create a cmd script and store it on your computer (call it e.g. create_junctions_userdata.cmd)
  • Paste this into the empty cmd file

Note that you can select the folders of your choice, this list is just an example.

mklink /j “C:\Users\Hans\Downloads” “D:\USERDATA\Hans\Downloads”
mklink /j “C:\Users\Hans\Contacts” “D:\USERDATA\Hans\Contacts”
mklink /j “C:\Users\Hans\Favourites” “D:\USERDATA\Hans\Favourites”
mklink /j “C:\Users\Hans\Music” “D:\USERDATA\Hans\Music”
mklink /j “C:\Users\Hans\OneDrive” “D:\USERDATA\Hans\OneDrive”
mklink /j “C:\Users\Hans\Pictures” “D:\USERDATA\Hans\Pictures”
mklink /j “C:\Users\Hans\Videos” “D:\USERDATA\Hans\Videos”
mklink /j “C:\Users\Hans\Searches” “D:\USERDATA\Hans\Searches”
pause

  • Use Windows Explorer to go to the folder C:\Users\Hans
  • Select the following folders while pressing the CTRL key: Contacts, Documents, Downloads, Favourites, Music, OneDrive, Pictures, Searches, Videos.

Note that you can select the folders of your choice, but make sure every folder selected is in your cmd script.

  • Press CTRL+X (Cut)
  • Navigate to the folder D:\USERDATA\Hans
  • Press CTRL+V (Paste)
  • Run the cmd file as Administrator and check that the junctions are created.

User_junctions_cmd

For your interest, on the Windows drive the little arrow in the corner of the folder icon reveals that these “folders” are now actually junctions.

User_junctions

Conclusion / Wrap up

With junctions you can replace a Windows folder with a “shortcut” to a different folder.

This can amongst others be handy when you have lack of disk space on the Windows drive.

It is strongly recommended to create only junctions that redirect to folders on fixed drives.

I have explained some examples of how you can use junctions, but you can use junctions for other applications as well. Beware that in some cases files in folders you want to make a junction for can be locked. Stopping the Windows Services or applications accessing those files can help, or starting Windows in Safe Mode (I must admit I did not try this but in theory it should work).

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

The CONTROLLER – TASK design pattern for stored procedures

(This blog post was first published on 25-nov-2013 on my former blog)

The challenges

Working with SQL Server and stored procedures for several years, I have faced several challenges like:

  1. reuse of stored procedures: make sure stored procedures are small components that you can use for different purposes.
  2. maintainability of stored procedures: make sure stored procedures do not become large ‘procedural written’, otherwise they can become hard to maintain due to this.
  3. generating code versus manual maintenance: how to merge generated and manual SQL code easily.
  4. where to implement error handling and the automic unit using a transaction: how to ensure these without harming the needs above.

And I have found a way to deal with this: The CONTROLLER - TASK design pattern for stored procedures. This pattern solves my challenges into a working solution!

1) Reuse of stored procedures

Sometimes you want to reuse a particular INSERT, UPDATE or DELETE statement. When it is in a stored procedure you can simply call this stored procedure! But what if this stored procedure also contains other code, uses its own transaction or implements its own error handling? Then this might be not that simple!

The CONTROLLER-TASK design pattern for stored procedures makes reuse of SQL code very easy.

2) Maintainability of stored procedures

A best practice in C# programming (or any type of programming really) that has become even more popular over the last years, is to create short methods containing only a small amount of code, and having a “main” method to call all the short methods.

Advantages: the main method is very readable (when method names are self-explaining), and code stays really maintainable. Also, short methods can be unittested easier.

Why not do the same in SQL code then?

Why not have an inventory of short stored procedures, either generated or manually made, that do only one task, and shop around to glue together what you need in a “main” stored procedure?

When working like this in a team:

- tasks can be divided easier when stored procedures are short

- stored procedures are easier to understand for new developers when they are short

The CONTROLLER-TASK design pattern for stored procedures makes your SQL code better maintainable.

3) Generating code versus manual maintenance

How convenient would it be to generate generic stored procedures, for instance UPDATE stored procedures for each table, a LOAD stored procedure for each hub in a datavault datawarehouse, and so on? Generating is not extremely more difficult than creating normal stored procedures. Just use tables like sys.objecs, sys.columns, and so on, and a lot is possible.

Imagine these stored procedures populate your inventory of “tasks”, ready to use and glue together in  a “main” stored procedure?

The CONTROLLER-TASK design pattern makes combining both manually made and generated SQL code to a working solution extremely easy.

4) Where to implement the error handling and automic unit using a transaction?

Is a stored procedure still reusable, if it is running in its own transaction? Maybe, if you use nested transactions. But there are drawnbacks and risks. Code can become messy when not implemented in the same way in different stored procedures.

Is a stored procedure still reusable, if it implements its own error handling? Maybe, but you have to be careful. If it ‘swallows’ the error, the stored procedure that calls it will not notice it and will continue after the error. This is almost never what you want. Besides that, implementing error handling in every stored procedure, makes your code base consist of many more lines.

So .. why not divide the “core” SQL code to run, and the “housekeeping” (automic unit, error handling)? No more confusion, it is clear where things are done, and code is easier reusable and maintainable.

The CONTROLLER-TASK design pattern divides core functionality and housekeeping code, therewith keeping your code cleaner and again .. better maintainable!

My solution, easy to implement

My answer to the challenges above is the CONTROLLER-TASK design pattern for stored procedures. As a picture says more than a thousand words, take a close look at the one below.

controller_task_design_pattern

Getting started

Allright, if you see the value of it, how can you implement the CONTROLLER-TASK design pattern?

In its basic form, you’ll need only a few things: a naming convention and two stored procedure templates.

I will tell you how I did this, so you can use or modify it in the way you prefer.

Choosing a naming convention

As explained, you have three types of stored procedures:

  • Task stored procedures (manually made)
  • Task stored procedures (generated)
  • Controller stored procedures

I just prefix my stored procedures with the typename as follows:

  • TASK_<BaseStoredProcName> for stored procedures that are manually made
  • GTSK_<BaseStoredProcName> for stored procedures that are generated
  • CTLR_<BaseStoredProcName> for controller stored procedures

Of course you can invent your own different naming convention, as long as you use it consequently and document it for new developers working on your project.

Using two templates

Here are my templates, you can modify for your own needs

Prerequisites

prerequisites.sql
CREATE SCHEMA [sp] AUTHORIZATION [dbo];

TASK stored procedures

TASK stored procedures.sql
PRINT ‘ScriptFile P10.sp.TASK_ReplaceWithStoredProcBaseName.sql’
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N’sp.TASK_ReplaceWithStoredProcBaseName’) AND type in (N’P’, N’PC’))
    DROP PROCEDURE sp.TASK_ReplaceWithStoredProcBaseName
GO
/* PRESS CTRL-SHIFT-M in SSMS to fill parameters
==========================================================================================
Author :
Create date :
Parameters:
- Input : none
- Output : none
Returnvalue : 0 if executed successfully.
Description : >
==========================================================================================
*/

CREATE PROCEDURE sp. TASK_ReplaceWithStoredProcBaseName
AS
BEGIN
  ---------------------------------------------------------------------------------------------------
  -- DECLARATIONS
  ---------------------------------------------------------------------------------------------------
  --N/A
  ---------------------------------------------------------------------------------------------------
  -- INITIALIZATION
  ---------------------------------------------------------------------------------------------------
  --N/A
  ---------------------------------------------------------------------------------------------------
  -- MAIN
  ---------------------------------------------------------------------------------------------------
  SELECT 1 AS [Replace with your own code]

END
GO

 

Controller stored procedures

Controller stored procedures.sql
PRINT ‘ScriptFile P20.sp.CTLR_ReplaceWithStoredProcBaseName.sql’
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N’sp.CTLR_ReplaceWithStoredProcBaseName’) AND type in (N’P’, N’PC’))
    DROP PROCEDURE sp.CTLR_ReplaceWithStoredProcBaseName
GO
/* PRESS CTRL-SHIFT-M in SSMS to fill parameters
==========================================================================================
Author :
Create date :
Parameters:
- Input : none
- Output : none
Returnvalue : 0 if executed successfully.
Description : >
==========================================================================================
*/

CREATE PROCEDURE sp.CTLR_ReplaceWithStoredProcBaseName
AS
BEGIN
  ---------------------------------------------------------------------------------------------------
  -- TEMPLATE CODE: CONFIGURATION OF CONTROLLER, BEGIN TRANSACTION, INFO LOGGING
  ---------------------------------------------------------------------------------------------------
  BEGIN TRY

    SET XACT_ABORT ON
    BEGIN TRANSACTION

    ---------------------------------------------------------------------------------------------------
    -- (NON-TEMPLATE) DECLARATIONS
    ---------------------------------------------------------------------------------------------------
    -- N/A
    ---------------------------------------------------------------------------------------------------
    -- EXECUTE TASK STORED PROCEDURES
    ---------------------------------------------------------------------------------------------------
    -- Example call:
    EXECUTE sp.TASK_ReplaceWithStoredProcBaseName
    --

    ---------------------------------------------------------------------------------------------------
    -- TEMPLATE CODE: COMMIT TRANSACTION
    ---------------------------------------------------------------------------------------------------
    COMMIT TRANSACTION

  END TRY
  BEGIN CATCH
    ---------------------------------------------------------------------------------------------------
    -- TEMPLATE CODE: CATCH BLOCK WITH ROLLBACK AND THROW
    ---------------------------------------------------------------------------------------------------
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;
    END
    -- Options here:
    --\
    ---> 1) Log the error (optional)
    --/
    -- Not part of the BASIC template

    --\
    ---> 2) Rethrow the error (optional)
    --/
    -- Syntax for SQL Server 2012:
    -- ; THROW

    -- Syntax for SQL Server 2008 and earlier:
    DECLARE @ORIGINAL_ERROR NVARCHAR(MAX)
          , @ERROR_SEVERITY INT = ERROR_SEVERITY()
          , @ERROR_STATE INT = ERROR_STATE()

    SELECT @ORIGINAL_ERROR = + ERROR_MESSAGE()
                        + ‘ (ERROR_NUMBER=’ + CAST(ERROR_NUMBER() AS NVARCHAR)
                        + ‘, ERROR_LINE=’ + CAST(ERROR_LINE() AS NVARCHAR)
                        + ‘, ERROR_PROCEDURE=’ + ERROR_PROCEDURE() + ‘)’
    RAISERROR(@ORIGINAL_ERROR, @ERROR_SEVERITY, @ERROR_STATE)

  END CATCH

  ---------------------------------------------------------------------------------------------------
  -- RETURN THE RESULT WHEN EXECUTED SUCCESFULLY
  ---------------------------------------------------------------------------------------------------
  RETURN 0;
    
END
GO

 

Summary

In this blogpost I have explained to you the advantages of the CONTROLLER - TASK design pattern for stored procedures, which are:

  • makes reuse of SQL code very easy.
  • makes your SQL code better maintainable.
  • makes combining both manually made and generated SQL code to a working solution extremely easy.
  • divides core functionality and housekeeping code, therewith keeping your code cleaner and again .. better maintainable!

After that you were shown how to get started with a basic implementation of this design pattern.

Thank you for reading my blogpost and I hope you enjoyed it and found it useful.

How to add a rownumber to a Data Flow Task in SSIS 2012

(This blog post was first published on 20-jun-2013 on my former blog)

The problem

For one of my projects I needed to add the file line number to the staging table, so that the raw data that was inserted into the database, would be tracable back to the source files exact line.

First I wanted to use the RowCount component in the Data Flow Task, it was easy to configure (only assign a variable) but it appeared that the variable only got the value of the current row after the Data Flow Task had finished, so you knew afterwards how many rows had gone through the dataflow. But that was not what I needed! I want an instant row number, telling me which row is flowing through right now!

The solution: a script component

So what we have to do is add a script component to the Data Flow Task.

20130620_pscr1

 

But remember, if we work with a ReadWrite variable, we will have the same problem as with the RowCount component: the value of the variable is not updated until the dataflow task has finished, so in the flow it will have the initial value for every row.

To work around this, we have to add an output column to the script component as follows:

20130620_pscr2

 

Now we have to edit the script. No ReadOnlyVariables or ReadWriteVariables need to be set, just press the <Edit Script> button.

20130620_pscr3

As the script really is just a C# class, all we have to do is add a private variable, initialize it, increase it for every row and assign the value to the outputcolumn “RowNumber”.

I will show you how:

Simply add the parts in red boxes to the script:

20130620_pscr4

20130620_pscr5

That’s it. Simple, hey?

Now, there is one more thing to do. Open your OLE DB Destination and make a mapping for the RowNumber column. In my example the database field is also called RowNumber, but this is of course not required, you can give it any name you like.

20130620_pscr6

Now you’re done, run the script and you will see that the RowNumber column in the destination table will increase for every row that is inserted!

Query hints WITH (NOLOCK) and WITH (READPAST) in action

(This blog post was first published on 18-jan-2013 on my former blog)

Download all used scripts here.

Query hints WITH (NOLOCK) and WITH (READPAST) are a great way to increase performance on (readonly) queries that run on one or more tables in which data is added , edited or deleted at the same time.

However those query hints have also some drawbacks, to be aware of, when using them.

In this article I will demonstrate how these query hints work, and when to use them.

Let’s start with some theory: what do these query hints do?

It makes no sense to copy the MSDN description here. If you need more detailed information you can read it yourself on msdn

I will just summarize what these query hints can do for me.

WITH (READPAST) – shows only committed data. When a record gets updated, it will not be selected at all, not the previous version of the record, nor the updated but uncommitted version.

WITH (NOLOCK) – shows both committed and uncommitted (dirty) data. When a record gets updated, the new uncommitted values of the record will be selected.

When to use

In general, do not use these queryhints, when you retrieve data to edit, e.g. in disconnected scenario’s like editing in a webapplication. Working with uncommitted data will in this case seldom be what you want.

When it comes to read only access, it depends. You have to decide what is more important: showing always the right (committed) data, or showing data fast. If the risk that uncommitted data is shown is acceptable, WITH (NOLOCK) is the query hint for you, if the risk that data being changed is not shown at all, WITH (READPAST) is the query hint for you.

Also for semi-static data (dimension- or system tables), that is seldom changed, the risk of the drawbacks of both queryhints, is usually acceptable.

Executing the demo

Step 1

First of all we have to do some setup work. I have made a script for that. You can run it in SQL Server Management Studio (see link to download all scripts at the top of this article).

Note that for simplicity of the demo I have created a CHAR column to be primary key. I know it is not a best practice, but for the demo it is sufficient. Normally you would always strive for numeric primary keys.

step1_setup.sql

— Step 1: Create a QueryHintsDemo database with one table, DemoData
USE [master]
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N’QueryHintsDemo’)
  CREATE DATABASE [QueryHintsDemo]
GO
USE [QueryHintsDemo]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DemoData]’) AND type in (N’U’))
DROP TABLE [dbo].[DemoData]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DemoData](
 [DemoDataCode] CHAR(6) NOT NULL,
 [DemoDataDescription] VARCHAR(100) NOT NULL,
 CONSTRAINT [PK_dbo_DemoData] PRIMARY KEY CLUSTERED
(
 [DemoDataCode] 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

Download all used scripts here.

Step 2

What is next?

Now you have to make sure you have two query windows open in Management Studio, so you can execute one script, and before that is finished, execute another script as well.

The first script will insert some initial data in the DemoData table, and then update it in a transaction. To be able to do this demonstration properly, the WAITFOR DELAY feature is used to wait 20 seconds before the transaction is rolled back.

In the first query window, use this script:

step2a_keep_transaction_open.sql

TRUNCATE TABLE [QueryHintsDemo].[dbo].[DemoData]
GO

INSERT INTO [QueryHintsDemo].[dbo].[DemoData]
           ([DemoDataCode]
           ,[DemoDataDescription])
     VALUES
           (‘DEMO01’
           ,‘This is the original text.’)
GO

BEGIN TRANSACTION

  UPDATE [QueryHintsDemo].[dbo].[DemoData]
     SET [DemoDataDescription] = ‘This is the updated text that gets rolled back later.’
   WHERE [DemoDataCode] = ‘DEMO01’

  WAITFOR DELAY ’00:00:20′

ROLLBACK TRANSACTION
GO

BEGIN TRANSACTION

  UPDATE [QueryHintsDemo].[dbo].[DemoData]
     SET [DemoDataDescription] = ‘This is the updated text that gets committed.’
   WHERE [DemoDataCode] = ‘DEMO01’
  
COMMIT TRANSACTION
GO

 

This is the script for the second querywindow:

step2b_select.sql

— WITH READPAST, will not show the updated, non committed record
— but also not the ‘old’ version of the record, before it got updated.
— So you potentially miss out some data when using this query hint.
SELECT ‘WITH (READPAST)’ AS [READPAST], *
FROM [QueryHintsDemo].[dbo].[DemoData] WITH (READPAST)
GO

— WITH NOLOCK, will show the updated, but uncommitted record. This is also called a “dirty read”.
— The risk here is that you use/display data, that in the end will
— not be committed and therefor never became ‘official’.
SELECT ‘WITH (NOLOCK)’ AS [NOLOCK], *
FROM [QueryHintsDemo].[dbo].[DemoData] WITH (NOLOCK)
GO

— Without using any queryhint, the select statement will wait until the
— first transaction is rolled back, and then show the original value.
— This is the most reliable, but also the slowest.
— You will notice that the first two SELECT statements will return
— a result immediately, but that the query below will only show result after almost
— 20 seconds, caused by the 20 seconds delay in the previous script.
SELECT ‘Without queryhint (1)’ AS [No hint 1], *
FROM [QueryHintsDemo].[dbo].[DemoData]

— Wait a few seconds, so that the update to be committed in the other script, is completed.
WAITFOR DELAY ’00:00:02′
— Without using any queryhint, the select statement will wait until the
— update is finished, and then show the final value.
SELECT ‘Without queryhint (2)’ AS [No hint 2], *
FROM [QueryHintsDemo].[dbo].[DemoData]

 

As you might already have understood, you should execute both scripts in SQL Server Management Studio: execute the first script, and while it is still running, start the second script in a seperate query window as soon as possible (but at least within 20 seconds after starting the first script).

Analyzing the query result

20130118_analyze_result2

 
Clean up
Use this script to clean up.

cleanup.sql

USE [master]
GO
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N’QueryHintsDemo’
GO
ALTER DATABASE [QueryHintsDemo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [QueryHintsDemo]
GO

 

Conclusion

The queryhints WITH (NOLOCK) and WITH (READPAST) are a great way to improve performance.

However they both have their own drawnbacks, to be aware of, when using them:

  • When using WITH (READPAST), you potentially miss out some data that is being updated, when your select query runs.
  • When using WITH (NOLOCK), the risk here is that you use/display data, that in the end will not be committed and therefor never became ‘official’.

It depends on the situation, when it is acceptable to use these queryhints.