SQL query error (Arithmetic overflow error converting expression to data type int)

Hi Experts,

I am facing below error while running attached stored procedure. Please advise how to fix below error.

Error:
Msg 8115, Level 16, State 2, Procedure usp_Main_GetCPUHistory, Line 8
Arithmetic overflow error converting expression to data type int.

Stored procedure:
create procedure MS_PerfDashboard.usp_Main_GetCPUHistory
as
begin
	declare @ts_now bigint
	select @ts_now = cpu_ticks / convert(float, ms_ticks) from sys.dm_os_sys_info
	
	select top 15 record_id,
		dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime, 
		SQLProcessUtilization,
		SystemIdle,
		100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
	from (
		select 
			record.value('(./Record/@id)[1]', 'int') as record_id,
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
			timestamp
		from (
			select timestamp, convert(xml, record) as record 
			from sys.dm_os_ring_buffers 
			where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
			and record like '%<SystemHealth>%') as x
		) as y 
	order by record_id desc
end

Open in new window

sqldba2013Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
the issue is very likely the expression:
 (@ts_now - [timestamp])

or this one:
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')

either of the 2 is not a INT as value, please double-check your data
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
It's running OK for me.
How are you calling the SP?
0
sqldba2013Author Commented:
Still I am not able to find root cause of error.

I am executing SP through CMS (Central management servers). In CMS I've registered three SQL servers and I am getting output for two servers and I am not able to fetch data for remaining one server.

I am using below command to fetch data from CMS:
use msdb;
Exec MS_PerfDashboard.usp_Main_GetCPUHistory

Svr 1: it returns 15 rows without error
Svr 2: it returns 15 rows without error
Svr 3: No records and Got above error

Sample data of Svr 1 and Svr 2:
Server Name          record_id      EventTime                               SQLProcessUtilization      SystemIdle      OtherProcessUtilization
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Svr 1                 496               2014-09-27 00:29:20.633              0                                         77                             23
Svr 1                495               2014-09-27 00:28:20.630             0                                        75                             25
Svr 2              1584              2014-09-27 09:15:41.960            6                                        89                            5
Svr 2              1583              2014-09-27 09:14:41.873              6                                         90                                4
0
sqldba2013Author Commented:
I found the solution using below URL.

http://celticcodingsolutions.com/Blog/post/2011/05/11/Microsoft-Performance-Dashboard-for-SQL-Server-2008-Issue-3.aspx

Now I am able to run SP qithout error.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
which is doing exactly what I suggested:
>the issue is very likely the expression:  (@ts_now - [timestamp]) <

dividing by 1000 to get a smaller value...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.