Check Agent Job Status From Different Servers with Powershell

Rob Swell has a handy script to retrieve agent job running status from multiple servers.

You can find the script from his bolg.

How I Check Hundreds of SQL Agent Jobs in 60 Seconds with Powershell

You just need to update the file location  and server list (one server in one line text file) and run the script from Powershell window. You will get an Excel file open to show your the slow moving progress.

If you want to use this script to run as an agent job, you may need to set proper permission to allow powershell user through agent job to access Excel COM.

Here is how to change the Identity to The interactive user:
From Administrative Tools>>Component Services > Computers > My Computer > DCOM Config >> Microsoft Excel application (right click ) > Properties
> Identity > Select The Interactive User  (by default, it was The launching user) >  Click OK.

Dynamic Change Matrix (Unpivot and Pivot)

  

  CREATE TABLE inputs ([SampleNo] VARCHAR (50) NULL,[Col1] VARCHAR (50) NULL,[Col2] VARCHAR (50) NULL,[Col3] VARCHAR (50) NULL,[Col4] VARCHAR (50) NULL,[Col6] VARCHAR (50) NULL,[Col7] VARCHAR (50) NULL); 

INSERT INTO inputs ([SampleNo], [Col1], [Col2], [Col3], [Col4], [Col6], [Col7]) VALUES
('Sample 1', 'x.xx', 'xx.xx', 'x.xx', 'xx.xx', 'x.xx', 'xx.xx')
,('Sample 2', 'x.xx', 'xx.xx', 'x.xx', 'xx.xx', 'x.xx', 'xx.xx')

--select  * from inputs

Declare @sqlUnpivot as NVarchar(4000)
Declare @ColsUnpivot as NVarchar(4000)

Declare @sqlPivot as NVarchar(4000)
Declare @ColsPivot as NVarchar(4000)

Set @ColsUnpivot=null

Select @ColsUnpivot =  COALESCE(@ColsUnpivot + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+',' + QUOTENAME(COLUMN_NAME)+')'
FROM   [INFORMATION_SCHEMA].[COLUMNS]
WHERE TABLE_NAME='inputs' and COLUMN_NAME Like 'Col%'
   --print @ColsUnpivot

Set @ColsPivot=null
Set @ColsPivot = STUFF((Select DISTINCT ', ' + quotename(SampleNo ,']') FROM inputs FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')
   --print @@ColsPivot

--- drop temp table if it exists
 if object_id('tempMatrix','U') is not null
 drop table tempMatrix

 ----Unpivot query and load the result into a temp table

Set @sqlUnpivot='Select [SampleNo],Cols,Vals

into tempMatrix
FROM inputs t
CROSS APPLY (Values ' + @ColsUnpivot + ' )  d(Cols,Vals) '

--Print @sqlUnpivot;
exec (@sqlUnpivot)

Set @sqlPivot = 'Select * from tempMatrix Pivot (max(vals) for SampleNo in ('+ @ColsPivot + ')) pvt'

--Print @sqlPivot;
exec (@sqlPivot)

drop table inputs

---Another sample:
--  https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bf52f787-3d17-4916-b6c8-f2337853b154/convert-column-into-row-and-row-in-column-in-sql-server-2008r2?forum=transactsql#d760866f-da81-486a-b2ed-0cfbf2e7f533

 

SQL Server 2016 Released.

SQL Server 2016 RTM was released on June 1st, 2016.
Today, I setup a VM with a standard version running on Windows 2012 R2.

A few numbers I came across:
The version number is 13.01601.5 (select @@version) and the comparability level is 130.

The internal version is 852. (SELECT name,version,* FROM sys.sysdatabases)

The SQL Server 2014 SP1 has a version number 12.0.4449.0, the comparability level 120 and internal version 782.

When I create the first object in a new database on the new instance, I found the first object’s object_id is 565577053 and the second one is 581577110 (select 565577053+ 16000057 ).

The magic prime number: 16000057.
In previous two versions (2012 and 2014) , the first object_id in a new database was 245575913.
It seems this starting number was pushed up 20 times to start. (select (565577053-245575913) /16000057).

Edit:
SQL Server 2016 (SP1) – 13.0.4001.0 :
The first object’s id in SQL Server 2016 SP1 is : 565577053

Download links —- SQL Power Doc and CentralDB

SQL Power Doc:

https://blogs.technet.microsoft.com/heyscriptingguy/2013/05/07/use-powershell-to-discover-diagnose-and-document-sql-server/

Download:

https://sqlpowerdoc.codeplex.com/releases/view/610898

CentralDB tool:

http://crazydba.com/sql-server-inventory-and-performance-baselining-using-powershell-and-ssrs/