An internal DMS error occurred that caused this operation to fail

Harish Sunderaraj 176 Reputation points
2020-08-06T15:28:12.38+00:00

Hi

Got the below error for unknown reason.

Initially thought it could be due to data issue, so enabled the skip incompatible rows option to debug the issue, but after enabling it didn't skip any rows and the copy activity succeeded by copying all rows.

Can you let me know is it a ADF issue or how it worked after enabling the skip incompatible rows option.

ErrorCode=FailedDbOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error happened when loading data into SQL Data Warehouse.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=110802;An internal DMS error occurred that caused this operation to fail. Details: Please use this Error ID when contacting your Administrator for assistance. EID:(e3aa99bb30d942c085c61cf80537e9ed)
SqlNativeBufferReader.Run, error in OdbcExecuteQuery: SqlState: 42000, NativeError: 8628, 'Error calling: SQLExecDirect(this->GetHstmt(), (SQLWCHAR *)statementText, statementLength), SQL return code: -1 | SQL Error Info: SrvrMsgState: 1, SrvrSeverity: 17, Error <1>: ErrorMsg: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]A time out occurred while waiting to optimize the query. Rerun the query. | Error calling: pReadConn->ExecuteQuery(statementText, statementLength, bufferFormat) | state: FFFF, number: 84137, active connections: 301', Connection String: Driver={pdwodbc17e};app=TypeD00-DmsNativeReader:DB14\mpdwsvc (40152)-ODBC;autotranslate=no;trusted_connection=yes;server=\.\pipe\DB.14-fa30c110b8f0-0\sql\query;database=Distribution_29,Source=.Net SqlClient Data Provider,SqlErrorNumber=8628,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=8628,State=1,Message=110802;An internal DMS error occurred that caused this operation to fail. Details: Please use this Error ID when contacting your Administrator for assistance. EID:(e3aa99bb30d942c085c61cf80537e9ed)
SqlNativeBufferReader.Run, error in OdbcExecuteQuery: SqlState: 42000, NativeError: 8628, 'Error calling: SQLExecDirect(this->GetHstmt(), (SQLWCHAR *)statementText, statementLength), SQL return code: -1 | SQL Error Info: SrvrMsgState: 1, SrvrSeverity: 17, Error <1>: ErrorMsg: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]A time out occurred while waiting to optimize the query. Rerun the query. | Error calling: pReadConn->ExecuteQuery(statementText, statementLength, bufferFormat) | state: FFFF, number: 84137, active connections: 301', Connection String: Driver={pdwodbc17e};app=TypeD00-DmsNativeReader:DB14\mpdwsvc (40152)-ODBC;autotranslate=no;trusted_connection=yes;server=\.\pipe\DB.14-fa30c110b8f0-0\sql\query;database=Distribution_29,},],'

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,660 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee
    2020-08-06T19:31:16.437+00:00

    Hi anonymous userunderaraj-3480,

    Welcome to Microsoft Q&A platform and thanks for your query.

    As per my understanding, after receiving the error you have enabled Skip incompatible rows option in ADF copy activity to see if it is an issue with data, but the pipeline ran successful without issues and no incompatible rows were written (Please correct me if my understanding is incorrect). - This means that there is no issue with data.

    From the error message it seems to be an issue on your sink side (SQL Data warehouse) and not ADF issue. This could be a transient issue when there is insufficient system memory in resource pool. It could be the reason your second run has succeeded without any issues/errors.

    To double confirm on this, could you to please try disabling the "Skip incompatible rows" option in ADF copy activity and do couple of test runs to see if you are noticing the error consistently? If you are not seeing any errors then it is a transient issue with your sink. In case if you are able to reproduce this error consistently, please let us know, we will have a deeper look into it.

    Let us know how it goes.

    Thank you

  2. Harish Sunderaraj 176 Reputation points
    2020-08-17T15:09:16.587+00:00

    Hi @KranthiPakala-MSFT

    As its in the prod i am limited to reproduce this issue

    I have the previous failure details will this be useful for your analysis.

    What is your source & sink

    Source: Azure SQL DB
    Target: Azure Synapse

    Your Data factory region. : UK South

    Failed pipeline runID : 6f91e3ca-8783-4bc5-8b25-26e852b3f966

    Failed Activity runID : b9ee86e0-0c56-4ba8-9253-d884f02be0f7

    Integration runtime used for source and sink? Azure Auto Resolve IR

    Thanks Harish