Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

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

0
sqldba2013
Asked:
sqldba2013
  • 2
  • 2
2 Solutions
 
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
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now