I work on sql server 2019 i run queries to get current long queries run
but i get error
so how to solve error below
Msg 535, Level 16, State 0, Line 52
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
Msg 16917, Level 16, State 2, Line 54
Cursor is not open.
Msg 16917, Level 16, State 1, Line 104
Cursor is not open.
(0 row(s) affected)
What I have tried:
DECLARE @spid INT ,
@stmt_start INT ,
@stmt_end INT ,
@sql_handle BINARY(20)
DECLARE @ProcessID INT ,
@Duration VARCHAR(MAX) ,
@ProgramName VARCHAR(MAX) ,
@HostName VARCHAR(MAX) ,
@LoginName VARCHAR(MAX)
DECLARE @Processes TABLE
(
ProcessID INT ,
Duration VARCHAR(MAX) ,
ProgramName VARCHAR(MAX) ,
HostName VARCHAR(MAX) ,
LoginName VARCHAR(MAX) ,
Query VARCHAR(MAX)
)
DECLARE crsProcesses CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT p.spid ,
RIGHT(CONVERT(VARCHAR, DATEADD(ms,
DATEDIFF(ms, P.last_batch,
GETDATE()), '1900-01-01'), 121),
12) AS 'batch_duration' ,
P.program_name ,
P.hostname ,
P.loginame
FROM master.dbo.sysprocesses P
WHERE P.spid > 50
AND P.STATUS NOT IN ( 'background', 'sleeping' )
AND P.cmd NOT IN ( 'AWAITING COMMAND', 'MIRROR HANDLER',
'LAZY WRITER', 'CHECKPOINT SLEEP', 'RA MANAGER' )
ORDER BY batch_duration DESC
OPEN crsProcesses
FETCH NEXT FROM crsProcesses INTO @ProcessID, @Duration, @ProgramName,
@HostName, @LoginName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @spid = @ProcessID
SELECT TOP 1
@sql_handle = sql_handle ,
@stmt_start = CASE stmt_start
WHEN 0 THEN 0
ELSE stmt_start / 2
END ,
@stmt_end = CASE stmt_end
WHEN -1 THEN -1
ELSE stmt_end / 2
END
FROM master.dbo.sysprocesses
WHERE spid = @spid
ORDER BY ecid
INSERT INTO @Processes
( ProcessID ,
Duration ,
ProgramName ,
HostName ,
LoginName ,
Query
)
SELECT @ProcessID AS ProcessID ,
@Duration ,
@ProgramName AS ProgramName ,
@HostName AS HostName ,
@LoginName AS LoginName ,
SUBSTRING(text, COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end
WHEN -1 THEN DATALENGTH(text)
ELSE ( @stmt_end - @stmt_start )
END) AS Query
FROM ::
fn_get_sql(@sql_handle)
FETCH NEXT FROM crsProcesses INTO @ProcessID, @Duration, @ProgramName,
@HostName, @LoginName
END
CLOSE crsProcesses
DEALLOCATE crsProcesses
SELECT p.ProcessID
, p.Duration
, p.ProgramName
, p.HostName
, LoginName = MAX(p.LoginName)
, ThreadCount = COUNT(*)
, p.Query
FROM @Processes AS p
GROUP BY p.ProcessID
, p.Duration
, p.ProgramName
, p.HostName
, p.Query