Home Dashboard Directory Help

PDW QueryActivityUpload Exception - Arithmetic overflow error converting expression to data type int. by Plex


Status: 

Closed
 as Fixed Help for as Fixed


15
0
Sign in
to vote
Type: Bug
ID: 484224
Opened: 8/20/2009 11:35:58 AM
Access Restriction: Public
Duplicates: 491064
0
Workaround(s)
view
5
User(s) can reproduce this bug

Description

SQL Server 2008 SP1 Enterprise

I continue to get the following excepetion in PDW for the collection_set_3_upload job.

Date        8/20/2009 2:30:03 PM
Log        Data Collection (Query Statistics)

Log ID        156603
Source        QueryActivityUpload
Collection Set Name        
Collection Item Name        
Status        
Runtime Execution Mode        
Start Time        8/20/2009 2:30:03 PM
Last Iteration Time        
Finish Time        8/20/2009 2:30:03 PM
Duration (ms)        
Operator        
Package Execution ID        
Event        OnError
Data Code        -1071636471

Message
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E57.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E57 Description: "Arithmetic overflow error converting expression to data type int.".
Details
Sign in to post a comment.
Posted by brijpatil on 2/2/2010 at 12:17 PM
Mike,

I try to run tarasul query in sql server 2008 RTM and it works fine but if try to run the same query in
sql server 2008 + sp1 I get below error message

Msg 321, Level 15, State 1, Line 106
"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

is there any work around for this ??

Thanks,
Brij
Posted by Microsoft on 9/30/2009 at 9:23 AM
This issue will be fixed in SQL Server 2008 Cumulative Update #5 when it is release and then in Service Pack 2 for general release. I appreciate tarasul's community spirit in suggesting a workaround, but I recomend that folks wait for the official fix to be released. Microsoft can only support official fixes and doesn't recommend manual alteration to the product code.

Regards,
Mike Wachal
SQL Server
Posted by A. Tarasul on 9/18/2009 at 1:10 PM
Found the problem. Below is a problem statement which gives error. It's fixed already by replacing all "* 1000" to "* 1000.0".
Otherwise it fails with aryfmetic overflow.

SET NOCOUNT ON
DECLARE @p1 datetime
SET @p1 = GETDATE()

SELECT
    [sql_handle],
    statement_start_offset,
    statement_end_offset,
    -- Use ISNULL here and in other columns to handle in-progress queries that are not yet in sys.dm_exec_query_stats.
    -- These values only come from sys.dm_exec_query_stats. If the plan does not show up in sys.dm_exec_query_stats
    -- (first execution of a still-in-progress query, visible in sys.dm_exec_requests), these values will be NULL.
    MAX (plan_generation_num) AS plan_generation_num,
    plan_handle,
    MIN (creation_time) AS creation_time,
    MAX (last_execution_time) AS last_execution_time,
    SUM (execution_count) AS execution_count,
    SUM (total_worker_time) AS total_worker_time,
    MIN (min_worker_time) AS min_worker_time,         -- NULLable
    MAX (max_worker_time) AS max_worker_time,
    SUM (total_physical_reads) AS total_physical_reads,
    MIN (min_physical_reads) AS min_physical_reads,     -- NULLable
    MAX (max_physical_reads) AS max_physical_reads,
    SUM (total_logical_writes) AS total_logical_writes,
    MIN (min_logical_writes) AS min_logical_writes,     -- NULLable
    MAX (max_logical_writes) AS max_logical_writes,
    SUM (total_logical_reads) AS total_logical_reads,
    MIN (min_logical_reads) AS min_logical_reads,     -- NULLable
    MAX (max_logical_reads) AS max_logical_reads,
    SUM (total_clr_time) AS total_clr_time,
    MIN (min_clr_time) AS min_clr_time,                 -- NULLable
    MAX (max_clr_time) AS max_clr_time,
    SUM (total_elapsed_time) AS total_elapsed_time,
    MIN (min_elapsed_time) AS min_elapsed_time,         -- NULLable
    MAX (max_elapsed_time) AS max_elapsed_time,
    @p1 AS collection_time
FROM
(
    SELECT
        [sql_handle],
        statement_start_offset,
        statement_end_offset,
        plan_generation_num,
        plan_handle,
        creation_time,
        last_execution_time,
        execution_count,
        total_worker_time,
        min_worker_time,
        max_worker_time,
        total_physical_reads,
        min_physical_reads,
        max_physical_reads,
        total_logical_writes,
        min_logical_writes,
        max_logical_writes,
        total_logical_reads,
        min_logical_reads,
        max_logical_reads,
        total_clr_time,
        min_clr_time,
        max_clr_time,
        total_elapsed_time,
        min_elapsed_time,
        max_elapsed_time
    FROM sys.dm_exec_query_stats AS q
    -- Temporary workaround for VSTS #91422. This should be removed if/when sys.dm_exec_query_stats reflects in-progress queries.
    UNION ALL
    SELECT
        r.[sql_handle],
        r.statement_start_offset,
        r.statement_end_offset,
        ISNULL (qs.plan_generation_num, 0) AS plan_generation_num,
        r.plan_handle,
        ISNULL (qs.creation_time, r.start_time) AS creation_time,
        r.start_time AS last_execution_time,
        1 AS execution_count,
        -- dm_exec_requests shows CPU time as ms, while dm_exec_query_stats
        -- uses microseconds. Convert ms to us.    
        r.cpu_time * 1000.0 AS total_worker_time,
        qs.min_worker_time,     -- min should not be influenced by in-progress queries
        r.cpu_time * 1000.0 AS max_worker_time,
        r.reads AS total_physical_reads,
        qs.min_physical_reads, -- min should not be influenced by in-progress queries
        r.reads AS max_physical_reads,
        r.writes AS total_logical_writes,
        qs.min_logical_writes, -- min should not be influenced by in-progress queries
        r.writes AS max_logical_writes,
        r.logical_reads AS total_logical_reads,
        qs.min_logical_reads, -- min should not be influenced by in-progress queries
        r.logical_reads AS max_logical_reads,
        qs.total_clr_time,     -- CLR time is not available in dm_exec_requests
        qs.min_clr_time,        -- CLR time is not available in dm_exec_requests
        qs.max_clr_time,        -- CLR time is not available in dm_exec_requests
        -- dm_exec_requests shows elapsed time as ms, while dm_exec_query_stats
        -- uses microseconds. Convert ms to us.
        r.total_elapsed_time * 1000.0 AS total_elapsed_time,
        
        qs.min_elapsed_time,    -- min should not be influenced by in-progress queries
        r.total_elapsed_time * 1000.0 AS max_elapsed_time
    FROM sys.dm_exec_requests AS r
    LEFT OUTER JOIN sys.dm_exec_query_stats AS qs ON r.plan_handle = qs.plan_handle
        AND r.statement_start_offset = qs.statement_start_offset
        AND r.statement_end_offset = qs.statement_end_offset
    WHERE r.sql_handle IS NOT NULL
) AS query_stats
OUTER APPLY sys.dm_exec_sql_text (sql_handle) AS sql
GROUP BY [sql_handle], plan_handle, statement_start_offset, statement_end_offset
ORDER BY [sql_handle], plan_handle, statement_start_offset, statement_end_offset

Posted by Microsoft on 9/9/2009 at 11:28 AM
Hi,

Some additional information will help us investigate this issue. Could you configure the following extended events logging and attempt to reproduce the problem? Once the error has occured again just attach the generated log file to this Connect item.

Thanks,
Mike Wachal
SQL Server

-- XEvent Session Defenition Arithmetic error, Log additional info

-- Sample Query: select convert(bigint,21568194 * 100)
if exists(select * from sys.server_event_sessions where name='ArithmeticError')
     DROP EVENT SESSION ArithmeticError ON SERVER
go

CREATE EVENT SESSION ArithmeticError ON SERVER
ADD EVENT sqlos.exception_ring_buffer_recorded (
ACTION (sqlserver.tsql_stack, sqlserver.sql_text)
                 WHERE (error = 8115 ))
ADD TARGET package0.ring_buffer,
ADD TARGET package0.asynchronous_file_target(
SET filename='c:\temp\ArithmeticErrors.xet', metadatafile='c:\temp\ArithmeticErrors.xem')
WITH (MAX_MEMORY = 4096KB,
EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 1 SECONDS,
MAX_EVENT_SIZE = 0KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = OFF)

go

ALTER EVENT SESSION ArithmeticError ON SERVER
state=start
go

-- Cleanup the session
--drop EVENT SESSION ArithmeticError ON SERVER
Posted by Microsoft on 8/28/2009 at 9:03 AM
Thank you for reporting this issue. We are researching this and will let you know what we discover.

Regards,
Mike Wachal
SQL Server
Posted by Jeremy T. Brown on 8/27/2009 at 7:50 AM
Seems like a bug in the SSIS package. Noticing the issue on SQL Server 2008 SP1 CU3
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
pdw.log (restricted) 9/4/2009 -
ArithmeticErrors_0_128970796120050000.xet (restricted) 9/10/2009 -
ArithmeticErrors_0_128970796120560000.xem (restricted) 9/10/2009 -