Click here to Skip to main content
15,880,364 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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
Posted
Updated 18-May-22 23:27pm
Comments
Richard Deeming 19-May-22 6:10am    
REPOST
The answer has not changed since you posted exactly the same problem back in March:
Error the datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large.[^]

1 solution

Read the error message: it's pretty explicit.
You are using DATEDIFF with "ms" - or millisecond - difference. So for each second that two timestamps are different, the value will add 1000. Since there are 60 seconds in a minute, 60 minutes in an hour, and 24 hours in a day, for each day that passes the value changes by 1000*60*60*24 == 86,400,000.
So for each year difference, that's 31,536,000,000.
Since a 32 bit integer can only hold 2,147,483,647 you will get the error when the difference is just over three weeks ...

As it says: "use datediff with a less precise datepart"
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900