Kill a Remote Desk Process

My SSMS is hanging on my remote desktop.
I ran two commands from windows powershell: tasklist to find the SSMS.exe pid and use taskkill command to kill the process.
(example: taskkill /fi “pid eq 4948” )

taskkill /F /PID pid_number

A window will popup and you can close the window and close the hanging SSMS application from there.

Advertisements

Continue to Use SQL Server 2019 CTP After Trial Period Ends

  
  
$currentDate = Get-Date  

$pastTime = $currentDate.AddDays(-179)  

set-date $pastTime  

Start-Service -Name "*sql*" -ErrorAction SilentlyContinue  

$areServicesStopped = $true  

while($areServicesStopped){  

    $serviceStatus = get-service -Name "*sql*" | select status

    if ($serviceStatus -notlike "Stopped")    {

        $areServicesStopped = $false

    }

    Start-Sleep -Seconds 1

}

$currentDate = $pastTime.AddDays(179)  

set-date $currentDate

 

https://sysxnull.blogspot.com/2017/09/solved-restart-sql-service-after-trial.html


Dynamic Alias Name

  
 DECLARE @sql NVARCHAR(MAX)

DECLARE @Month as Int
SET @Month = 2

SELECT @sql = N'Select CAST(@Month as nvarchar(10)) as '+ QUOTENAME('MTD' + CAST(@Month as nvarchar(2)) )   ;

EXEC sp_executesql @SQL,N'@Month int', @Month

 

Recursive Solution to Implement Excel Formula

  


CREATE TABLE #ResultsTable 
(
    Datum DATETIME,
    window CHAR(10),
    countersIN INT,--countersOUT INT,
    countersOUT INT,--countersIN INT,
    RESULT INT,
    RESULT_by_Cursor INT,
    countersIN_corrected INT
);


INSERT INTO #ResultsTable 
VALUES ('20180104 08:30:00.000', '08:30', 0, 0, 0, NULL, NULL),
       ('20180104 09:00:00.000', '09:00', 2, 1, 1, NULL, NULL),
       ('20180104 09:30:00.000', '09:30', 1, 0, 2, NULL, NULL),
       ('20180104 10:00:00.000', '10:00', 25, 9, 18, NULL, NULL),
       ('20180104 10:30:00.000', '10:30', 45, 41, 22, NULL, NULL),
       ('20180104 11:00:00.000', '11:00', 38, 37, 23, NULL, NULL),
       ('20180104 11:30:00.000', '11:30', 50, 51, 22, NULL, NULL),
       ('20180104 12:00:00.000', '12:00', 21, 24, 19, NULL, NULL),
       ('20180104 12:30:00.000', '12:30', 12, 19, 12, NULL, NULL),
       ('20180104 13:00:00.000', '13:00', 25, 18, 19, NULL, NULL),
       ('20180104 13:30:00.000', '13:30', 35, 27, 27, NULL, NULL),
       ('20180104 14:00:00.000', '14:00', 81, 9, 52, NULL, NULL),
       ('20180104 14:30:00.000', '14:30', 113, 18, 70, NULL, NULL),
       ('20180104 15:00:00.000', '15:00', 116, 34, 71, NULL, NULL),
       ('20180104 15:30:00.000', '15:30', 123, 36, 54, NULL, NULL),
       ('20180104 16:00:00.000', '16:00', 127, 35, 50, NULL, NULL),
       ('20180104 16:30:00.000', '16:30', 103, 19, 47, NULL, NULL),
       ('20180104 17:00:00.000', '17:00', 79, 31, 27, NULL, NULL),
       ('20180104 17:30:00.000', '17:30', 50, 16, 26, NULL, NULL),
       ('20180104 18:00:00.000', '18:00', 28, 11, 17, NULL, NULL),
       ('20180104 18:30:00.000', '18:30', 16, 15, 2, NULL, NULL),
       ('20180104 19:00:00.000', '19:00', 0, 2, 0, NULL, NULL),
       ('20180104 19:30:00.000', '19:30', 0, 0, 0, NULL, NULL),
       ('20180104 20:00:00.000', '20:00', 0, 0, 0, NULL, NULL),
       ('20180104 20:30:00.000', '20:30', 0, 0, 0, NULL, NULL),
       ('20180104 21:00:00.000', '21:00', 0, 0, 0, NULL, NULL),
       ('20180104 21:30:00.000', '21:30', 0, 0, 0, NULL, NULL),
       ('20180104 22:00:00.000', '22:00', 0, 0, 0, NULL, NULL)



 ;with mycte as (
select Datum,window,countersIN,countersOUT,  RESULT,
Lead(countersOUT,1)  Over(Order by Datum) countersOUT_next,
Lead(countersOUT,2)  Over(Order by Datum)  countersOUT_nextnext,
row_number() over(order by Datum) rn
from  #ResultsTable 
  )
  ,recursiveCte as (
  select Datum,window,countersIN,countersOUT,  RESULT
  ,countersOUT_next
  ,countersOUT_nextnext
  ,rn  
 , 0 as countersIN_corrected 
 , 0 as RESULT_by_Recursive 
  from mycte
  where Datum='20180104 08:30:00.000'

  Union all

  Select  t.Datum,t.window,t.countersIN,t.countersOUT,  t.RESULT
  ,t.countersOUT_next
  , t.countersOUT_nextnext
  ,t.rn
  ,countersIN_corrected=
  IIF(isnull(r.RESULT_by_Recursive,0) + t.countersIN - t.countersOUT  (t.countersOUT_next + t.countersOUT_nextnext)    ,
  t.countersOUT_next + t.countersOUT_nextnext + t.countersOUT -isnull(r.RESULT_by_Recursive,0) ,
 t.countersIN )  )  
 ,
 RESULT_by_Recursive= 
  r.RESULT_by_Recursive  + 
 (
 IIF( r.RESULT_by_Recursive  + t.countersIN - t.countersOUT  (t.countersOUT_next + t.countersOUT_nextnext)    ,
  t.countersOUT_next + t.countersOUT_nextnext + t.countersOUT - r.RESULT_by_Recursive ,
 t.countersIN )  ) 
 
 )
 - t.countersOUT  

  from  recursiveCte r join mycte t on t.rn=r.rn+1
  )
  
Select Datum, window, countersIN, countersOUT,RESULT,RESULT_by_Recursive,countersIN_corrected	
 
from recursiveCte
 
 
  

DROP TABLE #ResultsTable;

/*
Datum	window	countersIN	countersOUT	RESULT	RESULT_by_Recursive	countersIN_corrected
2018-01-04 08:30:00.000	08:30     	0	0	0	0	0
2018-01-04 09:00:00.000	09:00     	2	1	1	1	2
2018-01-04 09:30:00.000	09:30     	1	0	2	2	1
2018-01-04 10:00:00.000	10:00     	25	9	18	18	25
2018-01-04 10:30:00.000	10:30     	45	41	22	22	45
2018-01-04 11:00:00.000	11:00     	38	37	23	23	38
2018-01-04 11:30:00.000	11:30     	50	51	22	22	50
2018-01-04 12:00:00.000	12:00     	21	24	19	19	21
2018-01-04 12:30:00.000	12:30     	12	19	12	12	12
2018-01-04 13:00:00.000	13:00     	25	18	19	19	25
2018-01-04 13:30:00.000	13:30     	35	27	27	27	35
2018-01-04 14:00:00.000	14:00     	81	9	52	52	34
2018-01-04 14:30:00.000	14:30     	113	18	70	70	36
2018-01-04 15:00:00.000	15:00     	116	34	71	71	35
2018-01-04 15:30:00.000	15:30     	123	36	54	54	19
2018-01-04 16:00:00.000	16:00     	127	35	50	50	31
2018-01-04 16:30:00.000	16:30     	103	19	47	47	16
2018-01-04 17:00:00.000	17:00     	79	31	27	27	11
2018-01-04 17:30:00.000	17:30     	50	16	26	26	15
2018-01-04 18:00:00.000	18:00     	28	11	17	17	2
2018-01-04 18:30:00.000	18:30     	16	15	2	2	0
2018-01-04 19:00:00.000	19:00     	0	2	0	0	0
2018-01-04 19:30:00.000	19:30     	0	0	0	0	0
2018-01-04 20:00:00.000	20:00     	0	0	0	0	0
2018-01-04 20:30:00.000	20:30     	0	0	0	0	0
2018-01-04 21:00:00.000	21:00     	0	0	0	0	0
2018-01-04 21:30:00.000	21:30     	0	0	0	0	0
2018-01-04 22:00:00.000	22:00     	0	0	0	0	0
*/



  

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e4145994-c9d0-4b16-9fa6-10a2655f3ad5/translate-excel-formula-logic-to-tsql?forum=transactsql