Synapse Spark - The access code is invalid

Ryan Abbey 1,181 Reputation points
2023-03-16T22:44:49.1366667+00:00

We have some Spark external tables set up to point to a CDM storage folder that is fed by Dynamics F&O. Most of the time, the queries to these tables work but on occasion one or more will fail with below error

Operation on target lkupLatestUpdate failed: Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Cannot bulk load because the file "<path>/GENERALJOURNALENTRY_00005.csv" could not be opened. Operating system error code 12(The access code is invalid.). Statement ID: {D65F7B26-D4B7-4896-9260-} | Query hash: 0x | Distributed request ID: {79485A8C-CEEF-4915-9348-}. Total size of data scanned is 20 megabytes, total size of data moved is 0 megabytes, total size of data written is 0 megabytes.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Cannot bulk load because the file "<path>/GENERALJOURNALENTRY_00005.csv" could not be opened. Operating system error code 12(The access code is invalid.). Statement ID: {D65F7B26-D4B7-4896-9260-} | Query hash: 0x | Distributed request ID: {79485A8C-CEEF-4915-9348-}. Total size of data scanned is 20 megabytes, total size of data moved is 0 megabytes, total size of data written is 0 megabytes.,Source=.Net SqlClient Data Provider,SqlErrorNumber=4861,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=4861,State=1,Message=Cannot bulk load because the file "<path>/GENERALJOURNALENTRY_00005.csv" could not be opened. Operating system error code 12(The access code is invalid.).,},{Class=0,Number=15885,State=1,Message=Statement ID: {D65F7B26-D4B7-4896-9260-} | Query hash: 0x | Distributed request ID: {79485A8C-CEEF-4915-9348-}. Total size of data scanned is 20 megabytes, total size of data moved is 0 megabytes, total size of data written is 0 megabytes.,},],'

The F&O system writes frequently to the storage account so current thinking is that we're being refused read while this writing is happening so we tried setting transaction isolation beforehand but that hasn't resolved the issue

The fails are happening on a relatively simple query like below

set transaction isolation level READ UNCOMMITTED
select max(DataLakeModified_DateTime) as LastUpdate, count(*) as NumRows from dbo.GeneralJournalEntry

any suggestions on what we can try? or even some more detail on what is causing the error?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,696 questions
{count} votes