Converting a datestring to datetime (yyyymmddhhmmss)

This uses STUFF to format a datestring correctly.

declare @datestring varchar(20) = '20170504103253'
-- Add colons and space to format datetime
SET @datestring = STUFF(STUFF(STUFF(@datestring,13,0,':'),11,0,':'),9,0,' ')
select @datestring
-- Convert ....
select convert(datetime,@datestring)

2017-05-04 10:32:53

2017-05-04 10:32:53.000

TSQL : ORDER BY in an UPDATE (Queue table example)

Implementing a queue table, I wanted to fetch just one 1 row from a queue table (the next job)

The method below uses one statement, one transaction :)
Mark that row as being dealt with - the UPDATE statement
Fetch all the data about the row - using the OUTPUT clause, rather than a further SELECT
Updates the next desired row in the queue - Using UPDATE against a CTE, which allows you to use ORDER BY

DECLARE @Output TABLE
 (
   id int
  ,procedurename varchar(200)
  ,tablename varchar(200)
  ,databasename varchar(200)

 )

;WITH updateCTE AS (
-- doing an update in a CTE lets you use an ORDER BY clause
 SELECT TOP(1) id, procedurename, tablename, databasename, load_started, spid, load_start
 FROM [ETL].[myLoadQueue] 
 WHERE batch_start = @batchstart
 AND load_started = 0
 ORDER BY id
)

UPDATE updateCTE
SET load_started = 1 , spid = @@SPID, load_start = GETDATE()
OUTPUT deleted.id, deleted.procedurename, deleted.tablename, deleted.databasename INTO @Output

COUNT without GROUP BY

I found a novel way of doing a record count today, without using a GROUP BY.
Normally I would have joined another results set containing a GROUP BY, but it appears you can use the OVER window function without a PARTITION BY / ORDER BY statement.

The syntax is COUNT(*) OVER () AS [RecordCount]

I use it below to return the count of addresses in AdventureWorks.

SELECT DISTINCT 
      a.[City]
   ,sp.Name As StateProvince
   ,cr.Name AS CountryRegion
   ,COUNT(*) OVER (PARTITION BY sp.[StateProvinceID]) AS AddressesInThisProvince
   ,COUNT(*) OVER (PARTITION BY cr.CountryRegionCode) AS AddressesInThisCountry
   ,COUNT(*) OVER () AS TotalAddresses 
  FROM [AdventureWorks2014].[Person].[Address] a
  INNER JOIN [AdventureWorks2014].[Person].[StateProvince] sp
  ON a.[StateProvinceID] = sp.[StateProvinceID]
  INNER JOIN [AdventureWorks2014].[Person].[CountryRegion] cr
  ON sp.CountryRegionCode = cr.CountryRegionCode
  WHERE cr.Name = 'Germany'
  ORDER BY 1,2,3

Arithmetic overflow error converting expression to data type int.

Just a quick note to myself, SUMming a column of ints produced a value that exceeded the range of int.

So, I had to convert on the fly.

The Error -

Msg 8115, Level 16, State 2, Line 9

Arithmetic overflow error converting expression to data type int.

The Solution -

SELECT SUM(CONVERT(bigint,myint)) FROM mytable

Blocking - Brute force approach

-- Find requests being blocked
SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO

-- Generate SQL to kill everything blocking session id 85
SELECT distinct 'kill ' +
    CONVERT(varchar,blocking_session_id )
FROM sys.dm_os_waiting_tasks
WHERE
   session_id = 85;

 

SSIS 2016 - Command Line Deployment for Projects

Project based deployment can be automated from the command line.

I create a batch file for this and put PAUSE at the end so that I can review the output before the window closes!

isdeploymentwizard.exe /Silent /ModelType:Project /SourcePath:"E:\Codebase\SSIS\SSIS Solutions\My Solution\ProductionPackages\bin\Development\My Solution.ispac" /DestinationServer:"LIVESERVER.DOMAIN.LOCAL" /DestinationPath:"/SSISDB/Folder/My Solution"

pause

CTE to create a sequence of numbers

DECLARE @start INT, @end INT, @increment INT
SELECT @start=0, @end=100000000, @increment = 50000
 
;WITH NumberList ( Number ) AS
(
    SELECT @start as Number
        UNION ALL
    SELECT Number + @increment
        FROM NumberList
        WHERE Number < @end
)
 
SELECT Number FROM NumberList OPTION (MAXRECURSION 0)