Data Collection, Query Statistics, collection_set_3_upload job failing due to type issue - by Jon Gurgul

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


5
0
Sign in
to vote
ID 491064 Comments
Status Closed Workarounds
Type Bug Repros 7
Opened 9/21/2009 7:24:29 AM
Access Restriction Public
Primary Feedback Item 484224

Description

The Data Collection jobs which uploads the Query Statistics information errors continually due to a bigint calculation which leads to "Arithmetic overflow error converting expression to data type int."

This is caused by the "QueryActivityUpload.dtsx" package. In the data flow "DFT - Create Interesting Queries Upload Batch" and specifically the 
ole source command "ODS - Get current snapshot of dm_exec_query_stats" 


       --> ***r.total_elapsed_time * 1000 AS total_elapsed_time,
          qs.min_elapsed_time,    -- min should not be influenced by in-progress queries
       --> ***r.total_elapsed_time * 1000 AS max_elapsed_time

I have a very long running service broker spid which has a high r.total_elapsed_time value.

So this causes it to error:

SELECT MAX(r.total_elapsed_time) * 1000 FROM sys.dm_exec_requests AS r 

Section from DTS:

        ***r.total_elapsed_time * 1000 AS total_elapsed_time,
        qs.min_elapsed_time,    -- min should not be influenced by in-progress queries
        **r.total_elapsed_time * 1000 AS max_elapsed_time

Sign in to post a comment.
Posted by GerGav38 on 2/2/2012 at 7:05 AM
Hi,

I am getting this error ,
The MDW database is sql server 2008 r2 sp2 10.0.4000
and the db where I am trying to collect and upload the query stats from is sql server 2008 r2

error

SSIS error. Component name: DFT - Create Interesting Queries Upload Batch, Code: -1073450982, Subcomponent: SSIS.Pipeline, Description: component "ODS - Get current snapshot of dm_exec_query_stats" (16412) failed the pre-execute phase and returned error code 0xC0202009.
Posted by brijpatil on 2/1/2010 at 9:12 PM
We just migrated to sql server 2008 and with sp1 and job "collection_set_3_upload" job fails with the following error message

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: ""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.".

just wondering if there is a work around for this error.

I have installed sql server 2008 RTM on dev machine and it seems to work fine there. Any help is appropriated.

Thanks.
Posted by Microsoft on 12/15/2009 at 12:07 PM
Thanks for the feedback, as you have found this issue has been reported previously and a fix has been released. I'm closing this issue as a duplicate.

Regards,
Mike Wachal
SQL Server
Posted by Jon Gurgul on 11/18/2009 at 8:31 AM
Just found this where tarasul has highlighted the same issue.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=484224

Seems the fix is available now Cumulative Update #5 for SQL Server 2008 Service Pack 1:

http://blogs.msdn.com/sqlreleaseservices/default.aspx
Posted by Jon Gurgul on 11/18/2009 at 8:17 AM
Anything with an elasped time > 2147483 will cause it to fail:

SELECT r.*
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
AND r.total_elapsed_time > 2147483 --2 147 483 microseconds = 2.147483 seconds

You can work round this by adding the .0 or making sure there are no elasped times that cause the error.

DECLARE @i INT
SET @i = 2147484
SELECT @i * 1000
SELECT @i * 1000.0

SELECT MAX(r.total_elapsed_time) * 1000.0 FROM sys.dm_exec_requests AS r
Posted by Andy Krafft on 11/11/2009 at 2:15 AM
Tried a few crude hacks to workaround this but no joy. Please can we have a fix for this ASAP as it is degrading a good tool which is frustrating. Thx.
Posted by TatyanaS on 11/10/2009 at 1:32 PM
We have this error on x64 platform. Need a workaround ASAP.