Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Power Platform Community / Forums / Microsoft Dataverse / SQL Exception- Input Q...
Microsoft Dataverse
Answered

SQL Exception- Input Query Length Exceeding Maximum Limit in Dataverse Integration

(1) ShareShare
ReportReport
Posted on by EBMRay 1,697
Hello,
 
We are currently integrating using AppService to push data to Dataverse in a production environment, following this logic:
  • We retrieve data from an API to extract the customer's email and compare it with CRM.
  • If the account does not exist, we create the account.
  • We serialize the create/update lists separately.
  • Using a parallel "for each" loop, we send data in batches of 1,000 records per batch back to CRM APIs after generating the token.
  • We tried reducing the batch size to 10 records and even one record per batch and running the process without the parallel "for each" loop. However, in all cases, the calls are still failing.
When we trigger the process, the code fails with the attached error, suggesting that the query length is being exceeded. We have not found a way to catch which field is failing in order to increase its length.
 
Is there any investigation tool that could help identify the issue? We created a pre-validation plugin to check all field lengths, but it seems the plugin is not being triggered, indicating the process isn't even reaching the insert step in Dataverse.
 
Unfortunately, the customer cannot provide us with access to the production environment due to the sensitivity of their data.
 
Any assistance would be greatly appreciated.
Best regards,
Julien
 
Categories:
Microsoft Dataverse
SQL Client Except...
  • Verified answer
    ankit_singhal Profile Picture
    ankit_singhal 181 on at
    SQL Exception- Input Query Length Exceeding Maximum Limit in Dataverse Integration
    I am not getting anything from error. Maybe you can raise issue with MS, they will not support custom code but they can check logs from CRM side for you and if there is any issue captured then they can help. Also, as it is not working then you can think of another way to import your data. Like SSIS using kingswaysoft.
    One more thing, one time we faced this type issue, our  data migration using code working fine on non prod but it failed don prod. after investigation, we found there was some issue in data conversation. As our utility is hosted on the server and non prod and prod server are different so we faced this issue. you can cross check this also.  
     
    Note: Please mark verified, if it is useful for you 
  • EBMRay Profile Picture
    EBMRay 1,697 on at
    SQL Exception- Input Query Length Exceeding Maximum Limit in Dataverse Integration
    Hi @ankit_signhal,
     
    Thank you for your valuable reply.
     
     We tried to investigate further and now we are facing a different issue as attached.

    We are not able to know which Task is Canceled. I think there is something wrong from the App Service itself. We tried on a different environment rather than the production and it is working perfectly.


  • Suggested answer
    ankit_singhal Profile Picture
    ankit_singhal 181 on at
    SQL Exception- Input Query Length Exceeding Maximum Limit in Dataverse Integration
    Hi,

    This is a generic error. Best way to replicate this issue on your non prod, if there is some sensitive prod data then you can munging/masking those data and use in non prod. we are following the same approach. 
    Or if this is not feasible for you then update your code. when you are fetching a record from your source use sorting and print some id or unique column in log one by one so you can check till which row your record is picked and inserted. when you got error then check the next record that should be bad data. 

    Note: Please mark verified, if it is useful for you 
  • EBMRay Profile Picture
    EBMRay 1,697 on at
    SQL Exception- Input Query Length Exceeding Maximum Limit in Dataverse Integration
    Hello @ankit_singhal,
     
    Appreciate if you have any update based on the provided information below.
     
    Best regards,
    EBMRay
  • EBMRay Profile Picture
    EBMRay 1,697 on at
    SQL Exception- Input Query Length Exceeding Maximum Limit in Dataverse Integration
    Hello Ankit,

    Thank you for your prompt reply.

    As mentioned, the customer is not permitting us to analyze the data in the production environment. Based on our debugging and error handling, we are encountering the following exception:

    Microsoft.Data.SqlClient.SqlException (0x80131904): Input query length exceeding the supported maximum length.
       at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
       at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
       at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
       at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
       at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
       at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
       at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
       at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in /_/Dapper/SqlMapper.cs:line 1156
       at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext() in /_/Dapper/SqlMapper.cs:line 1184
       at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       at .Service.Services.AccountAPIService.AddToCrm(List`1 model, String unitId, String chunkId)
    ClientConnectionId:267bde26-2b02-4157-927a-32eb6ee1996b
    Error Number:40000,State:1,Class:16

  • Suggested answer
    ankit_singhal Profile Picture
    ankit_singhal 181 on at
    SQL Exception- Input Query Length Exceeding Maximum Limit in Dataverse Integration
    You can write the logs and then you can check where it failed and check that record.. Also, if it is custom code then run it in debug mode and analysis the data. 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

September 2024 Newsletter…

September 2024 Community Newsletter…

Community Update Sept 16…

Power Platform Community Update…

Welcome to the new Power Platform Community!…

We are excited to announce our new Copilot Cookbook Gallery in the Community…

more Community News

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 141,122

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,414

Leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans