cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jt__
Regular Visitor

New Entity From Get Data throws exception System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated.

Since this query pulls data via csv file in the public domain anyone can reproduce this...

 

  1. Login in to Power Apps
  2. Select Entities
  3. Select Get data
  4. Choose blank query
  5. Connect to a data source, like this

 

let

  Source = Csv.Document(Web.Contents("https://ceqanet.opr.ca.gov/Search?County=Alameda&OutputFormat=CSV"), [Delimiter = ",", Columns = 48, QuoteStyle = QuoteStyle.Csv]),

  #"Promoted headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),

  #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"SCH Number", Int64.Type}, {"Lead Agency", type text}, {"Document Title", type text}, {"Document Type", type text}, {"Received", type date}, {"Posted", type date}, {"Document Description", type text}, {"Document Portal URL", type text}, {"Project Title", type text}, {"Contact Full Name", type text}, {"Contact Email Address", type text}, {"Contact Address  1", type text}, {"Contact Address  2", type text}, {"Contact City", type text}, {"Contact State", type text}, {"Contact Zip Code", type text}, {"Contact Phone Number", type text}, {"Location Coordinates", type text}, {"Cities", type text}, {"Counties", type text}, {"Location Cross Streets", type text}, {"Location Zip Code", type text}, {"Location Total Acres", type text}, {"Location Parcel Number", type text}, {"Location State Highways", type text}, {"Location Waterways", type text}, {"Location Airports", type text}, {"NOC Has Non Late Comment", type text}, {"NOC Review Start Date", type date}, {"NOC Review End Date", type date}, {"NOC Development Type", type text}, {"NOC Local Action", type text}, {"NOC Project Issues", type text}, {"NOE Exempt Status", type text}, {"NOE Exempt Citation", type text}, {"NOE Reasons for Exemption", type text}, {"NOD Agency", type text}, {"NOD Approved By Lead Agency", type text}, {"NOD Approved Date", type date}, {"NOD Significant Environmental Impact", type text}, {"NOD Environmental Impact Report Prepared", type text}, {"NOD Negative Declaration Prepared", type text}, {"NOD Other Document Type", type text}, {"NOD Mitigation Measures", type text}, {"NOD Mitigation Reporting Or Monitoring Plan", type text}, {"NOD Statement Of Overriding Considerations Adopted", type text}, {"NOD Findings Made Pursuant", type text}, {"NOD Final EIR Available Location", type text}})

in

  #"Changed column type"

Continued...

  • Anonymous connection
  • Set project title to text
  • Set the key and primary fields
  • Do the load by clicking next
  • Refresh manually
  • Click create
    Error....

There was an error while creating this entity. Details: Failed to create entity with logical name cr9c8_query and object type code 10021. Exception: System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Crm.CrmDbConnection.LockConnectionAndExecuteMethod[TResult](IDbConnection connection, Func`1 executeMethod)
at Microsoft.Crm.CrmDbConnection.InternalExecuteWithRetry[TResult](Func`1 ExecuteMethod, IDbCommand command, ICrmTransaction crmTransaction)
at Microsoft.Crm.CrmDbConnection.<>c__DisplayClass139_0.<InternalExecuteNonQuery>b__0()
at Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute[TResult](ILogger logger, EventId eventId, ActivityType activityType, Func`1 func, IEnumerable`1 additionalCustomProperties)
at Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute[TResult](ILogger logger, XrmTelemetryActivityType activityType, Func`1 func)
at Microsoft.Crm.Platform.MetadataBusinessEntities.DBCommandExecutor.ExecuteNonQuery(IDbCommand command, ISqlExecutionContext context)
at Microsoft.Crm.Platform.SolutionAwareComponents.MPOBulkCreateComponentInstanceAction.ExecuteCommand(IDbCommand command, ISqlExecutionContext sqlExecutionContext)
at Microsoft.Crm.Platform.SolutionAwareComponents.MPOBulkCreateComponentInstanceAction.Execute(ExecutionContext context)
at Microsoft.Crm.Platform.SolutionAwareComponents.ComponentStateTransition.Execute(Int32 componentType, IReadOnlyList`1 operationParameters, BusinessComponentState currentState, ExecutionContext context)
at Microsoft.Crm.Platform.MetadataBusinessEntities.MetadataProcessObject.DoCreate(List`1 metadataEntities, ExecutionContext context)
at Microsoft.Crm.Platform.MetadataBusinessEntities.MetadataProcessObject.<>c__DisplayClass4_0.<Create>b__0()
at Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute(ILogger logger, EventId eventId, ActivityType activityType, Action action, IEnumerable`1 additionalCustomProperties)
at Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute(ILogger logger, XrmTelemetryActivityType activityType, Action action)
at Microsoft.Crm.Platform.MetadataBusinessEntities.MetadataProcessObject.Create(IReadOnlyList`1 metadataDescriptions, ExecutionContext context)
at Microsoft.Crm.Metadata.EntityDefaultAttributesCreateAction.MetabaseOperation()
at Microsoft.Crm.Metadata.EntityCreateAction.CreateDefaultAttributesMetadataInBulk()
at Microsoft.Crm.Metadata.BulkMetadataQueueProcessor.<>c__DisplayClass6_1.<ExecuteMetabaseOperation>b__0()
at Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute(ILogger logger, EventId eventId, ActivityType activityType, Action action, IEnumerable`1 additionalCustomProperties)
at Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute(ILogger logger, XrmTelemetryActivityType activityType, Action action)
at Microsoft.Crm.Metadata.BulkMetadataQueueProcessor.ExecuteMetabaseOperation(IReadOnlyList`1 metabaseActions, IOrganizationContext organizationContext)
at Microsoft.Crm.Metadata.BulkMetadataQueueProcessor.<>c__DisplayClass3_0.<ProcessQueue>b__0()
at Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute(ILogger logger, EventId eventId, ActivityType activityType, Action action, IEnumerable`1 additionalCustomProperties)
at Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute(ILogger logger, XrmTelemetryActivityType activityType, Action action)
at Microsoft.Crm.Metadata.MetadataHelper.ProcessQueue(IMetadataQueueProcessor metadataQueueProcessor)
at Microsoft.Crm.Metadata.SqlTelemetryHelper.LogSqlTimes(Action action, String operationName)
at Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute(ILogger logger, EventId eventId, ActivityType activityType, Action action, IEnumerable`1 additionalCustomProperties)
at Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute(ILogger logger, XrmTelemetryActivityType activityType, Action action)
at Microsoft.Crm.Metadata.EntityService.CreateInternal(EntityCreateInfo entityInfo, MetadataHelper metadataHelper, ExecutionContext context)
at Microsoft.Crm.Metadata.EntityService.<>c__DisplayClass26_1.<CreateInternalHelper>b__3()
at Microsoft.Crm.Metadata.SqlTelemetryHelper.LogSqlTimes(Action action, String operationName)
at Microsoft.Crm.Metadata.EntityService.CreateInternalHelper(EntityCreateInfo entityInfo, MetadataHelper metadataHelper, ExecutionContext context)
ClientConnectionId:b16764e9-c338-446e-98aa-08ba4eae2904
Error Number:8152,State:30,Class:16
ClientConnectionId before routing:1eeb0a76-3274-42ef-ad06-531f1bdf33cd
Routing Destination:b378054d0a52.tr917.westus1-a.worker.database.windows.net,11037.

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
jt__
Regular Visitor

After opening a ticket with MS, they were able to get to the back end to tell me the exact problem. Apparently there is a column name length limit of 50 characters, if you simply rename the column the import is successful. This does not appear to be documented anywhere that I can find and there is no indication from Microsoft that they intend to enhance their code to give a more meaningful error message.

 

 

View solution in original post

7 REPLIES 7
SeanHenderson
Impactful Individual
Impactful Individual

Hi @jt__ 

What are you trying to do? Just import the csv into CDS?

Yep, I documented the issue with a blank query so that anyone could reproduce this issue  with ease

SeanHenderson
Impactful Individual
Impactful Individual

If you set up the entities in CDS in advance then you can use the data import wizard to import a CSV. This is a much more reliable method of import.

You can find the data import wizard either in the admin center, select your environment then Settings > Data Management > Imports

or in advanced settings under Settings > Data Management > Imports

It's easy to use, just select your CSV, the entity you're importing to and then map your fields.

v-xida-msft
Community Support
Community Support

Hi @jt__ ,

Could you please share a bit more about your scenario?

Do you want to import data from your CSV file into your CDS Entity through "Power Query" functionality?

 

Based on the issue that you mentioned, I have made a test on my side based on the "https://ceqanet.opr.ca.gov/Search?County=Alameda&OutputFormat=CSV" URL, and same error message occurs on my side.

 

Based on the error message that you mentioned, it seems that there is something wrong with the column values in the CSV file you want to load. 

The "String or binary data would be truncated" error would occur when you implement an ETL between a source and a destination that does not have matching data types and/or length.

Please check and see if the following article would help in your scenario:

https://techcommunity.microsoft.com/t5/SQL-Server/String-or-Binary-data-would-be-truncated-replacing...

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for the reply, the end result I am after is for the Power Apps / Entities / Get Data feature to work. Having it display a .net exception from the back end is obviously not helpful to me because power apps is closed source. I can't load the code up in a debugger and see exactly where why it's failing. As such I am dependent on the web front end to deliver a human readable error message and then giving me the opportunity to correct the error. 

This feature currently does neither of those two things. It does not present an error telling me which column has an issue and it also does not give the ability for me to go back and correct the issue. Once this exception is thrown you have to start the import all over again from scratch. 

v-xida-msft
Community Support
Community Support

Hi @jt__ ,

Have you solved your problem?

 

Thanks for your feedback. The feature that you mentioned has not been supported in "Power Query" functionality of CDS. If you would like this feature to be added in PowerApps, please submit an idea to PowerApps Ideas Forum:

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/idb-p/PowerAppsIdeas

 

 

If you have solved your problem in importing data into your CDS Entity, please consider go ahead to click "Accept as Solution" to identify this thread has been solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jt__
Regular Visitor

After opening a ticket with MS, they were able to get to the back end to tell me the exact problem. Apparently there is a column name length limit of 50 characters, if you simply rename the column the import is successful. This does not appear to be documented anywhere that I can find and there is no indication from Microsoft that they intend to enhance their code to give a more meaningful error message.

 

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,602)