Kill a Remote Desk Process
Posted: August 28, 2019 Filed under: Uncategorized 1 CommentMy 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.
Continue to Use SQL Server 2019 CTP After Trial Period Ends
Posted: August 21, 2019 Filed under: Uncategorized Leave a comment
$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
Posted: August 19, 2019 Filed under: Uncategorized Leave a comment
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
Posted: August 8, 2019 Filed under: Uncategorized Leave a comment
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
*/
Jingyang Li 