2

For many weeks my data collections from my database instace have been uploading to my MDW server. Suddenly step 3 of the Server Activity Collector has stopped working giving the following error:

Job Name          DataCollector Server Stats Coll n Upload
Step Name         collection_set_3_noncached_collect_and_upload_upload
Duration          00:00:05
Sql Severity      0
Sql Message ID    0
Operator Emailed            
Operator Net sent           
Operator Paged                
Retries Attempted 0

Message:
Executed as user: domain_svcAccount. SSIS error. Component name: DFT - Find and save sql text, Code: -1071636471, Subcomponent: LKUP - Look up query text on target server [2], Description: 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 11.0"
Hresult: 0x80040E14 Description: "The handle that was passed to dm_exec_sql_text was invalid.".

SSIS error. Component name: DFT - Find and save sql text, Code: -1071611309, Subcomponent: LKUP - Look up query text on target server [2], Description: OLE DB error occurred while fetching parameterized rowset. Check SQLCommand and SqlCommandParam properties. SSIS error. Component name: DFT - Find and save sql text, Code: -1073450974, Subcomponent: SSIS.Pipeline, Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "LKUP - Look up query text on target server" (2) failed with error code 0xC0208253 while processing input "Lookup Input" (16). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

The master package exited with error, previous error messages should explain the cause.
Process Exit Code 5. The step failed.

Has anyone encountered this error before?

0

This error:

0x80040E14 Description: "The handle that was passed to dm_exec_sql_text was invalid."

indicates a "handle" was passed into the sys.dm_exec_sql_text function that was not an actual, valid, SQL handle.

You'll see this error if you run this code:

SELECT *
FROM sys.dm_exec_sql_text(0)

sys.dm_exec_sql_text is used to obtain the query text for queries that reside in the plan cache, and can be seen in action via this sample code:

SELECT TOP(10)
    decp.plan_handle
    , dest.text
FROM sys.dm_exec_cached_plans decp
CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) dest;

It looks like the data collector has a bug; what version of SQL Server do you have installed?

  • I am using SQL 2012 SP4. – user170875 Jan 25 at 9:57
  • I have enabled logging and getting the following errors.. The external columns for ODS - Upload snapshots into active_sessions_and_requests table are out of synchronization with the data source columns. The external column "host_name" needs to be updated. The external column "program_name" needs to be updated. – user170875 Jan 25 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.