Search

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

Closed
as Fixed Help for as Fixed

5
0
Sign in
 to vote
Type: Bug
ID: 491064
Opened: 9/21/2009 7:24:29 AM
Access Restriction: Public
Primary Feedback Item: 484224
2
Workaround(s)
7
User(s) can reproduce this bug
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
Details (expand)
Product Language
English

Version

SQL Server 2008 - Standard Edition

Category

Tools (SSMS, Agent, Profiler, etc.)

Operating System

Win2003 Standard Server (SP2)
Operating System Language
English
Steps to Reproduce
After setting up Data Collection.

1. Right Click on Query Statistics, and choose Collect and Upload Now.

2. This will start the QueryActivityUpload.dtsx package which will error if there are any very long running spids.


Actual Results
Error Message in SQL job log:

Executed as user: xxx\xxx. The step did not generate any output. Process Exit Code 5. The step failed.

Error Message in Data Collection Log:

Message
component "ODS - Get current snapshot of dm_exec_query_stats" (16412) failed the pre-execute phase and returned error code 0xC0202009.
Expected Results
Success message for the Query Statistics collector upload job.

Platform

32
File Attachments
0 attachments
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.
Sign in to post a workaround.
Posted by TatyanaS on 1/15/2010 at 1:19 PM
Cumulative Update #5 for SQL Server 2008 SP1 resolves the issue.
Posted by Aviel Iluz on 1/13/2011 at 2:57 AM
1. Export the package QueryActivityUpload to a dtsx file
2. fix the query in the ole source command "ODS - Get current snapshot of dm_exec_query_stats" :
convert all columns multiplied by 1000, e.g. r.total_elapsed_time, to BIGINT. for example: CONVERT(BIGINT, r.total_elapsed_time) * 1000

3. Stop the Query Statistics collection set
4. delete the package from the SSIS repository under MSDB\Data Collector
5. re-import the package into the SSIS repository under MSDB\Data Collector
6. restart the collection set.
© 2012 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement