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)

SQL Page Compression - Rollout script.

An update to my original script to apply PAGE Compression to all objects
Obviously, you need to weigh up the benefits of doing so first.

This version includes the rowcounts but orders the statements so you compress the table before it's associated indexes.

SELECT distinct rows,SCHEMA_NAME(schema_id) as SchemaName, Name AS TableName,'Table' AS ObjectType,1 AS ExecutionOrder,'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (MAXDOP=1,ONLINE=ON,DATA_COMPRESSION = PAGE);' AS [SQLCommand]
FROM sys.objects o
inner join sys.partitions p
on o.object_id = p.object_id
where o.TYPE = 'u'
and p.data_compression = 0
UNION ALL
SELECT distinct rows,SCHEMA_NAME(schema_id) as SchemaName, Name AS TableName,'Index' AS ObjectType,2 AS ExecutionOrder,'ALTER INDEX ALL ON [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (MAXDOP=1,ONLINE=ON,DATA_COMPRESSION = PAGE);' AS [SQLCommand] 
FROM sys.objects o
inner join sys.partitions p
on o.object_id = p.object_id
where o.TYPE = 'u'
and p.data_compression = 0
order by SCHEMA_NAME(schema_id),Name,ExecutionOrder