You’re offline. This is a read only version of the page.
Since this query pulls data via csv file in the public domain anyone can reproduce 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...
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.
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.
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,
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.
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:
Best regards,
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.
Hi @jt__
What are you trying to do? Just import the csv into CDS?
#1
WarrenBelz
637
Most Valuable Professional
#2
stampcoin
570
Super User 2025 Season 2
#3
Power Apps 1919
473
Share
Report
All responses (
Answers (