Cannot bulk load file error code 12. Azure synapse link, dynamics. Azure synapse workspace. SQL database

Luuk de Vlugt 51

We created an azure synapse link and a "sql" database in the azure synapse workspace. But sometimes (not all the time) we get an error when selecting with sql from the csv files in the storage account.

The query bulk loads the data from the csv files:

But gives the following error:
Cannot bulk load because the file ****.csv could not be opened. Operating system error code 12(The access code is invalid.).

I think this is because the azure synapse link that loads the data from our dynamics platform to the storage account, updates the files within the storage accounts at the same time when I am running the query. (the entity accounts for instance is updated almost all the time in our system, for small entities with a few records that never change, this error does not occur)

I am wondering what the solution is, because the prebuild views of the azure synapse link are exactly like this.

I don't want to use the "allow inconsistent data reads" because I think it will mess up the synchronisation of the azure synapse link. Because it updates data, not only appends data.

When I run the query myself it is not a big problem because I will just run it again and it works. But when the query is triggerd by a timer and gives this error the whole proces stops.
Anyone some clever ideas?

KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-09-01T23:40:35.887+00:00

Hello @Luuk de Vlugt ,

Thanks for the question and using MS Q&A platform.

This is a known problem and product team have acknowledged and working on a fix. I have reached out to the respective team to get the latest update on it and will keep you posted as soon as I hear back from them.

We appreciate your patience.
Luuk de Vlugt 51 Reputation points

2022-09-02T08:44:29.157+00:00

Hey @KranthiPakala-MSFT ,

Thanks for the reply. Good to know that they are working on a solution.
Please let me know what the latest update is, that would be great!

Do you think they can give an indication of the timeline? Will it be days, weeks or months?
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-09-02T22:32:27.09+00:00
Hello @Luuk de Vlugt ,

Thanks for your response.

We do not have an ETA at this time as it is going to be kind of new feature rather than a fix.

However, on further discussions with product team, below are couple of workarounds you may want to consider in the meantime:

Two approaches to solve the problem:

Use Snapshot data to avoid read-lock error on CSV(in-place update): Why do I see the error message - cannot bulk load because the file is incomplete or could not be read?

When exporting data, use append-only mode with dirty read support: When do I use Append only mode for a historical view of changes?

Hope this info helps.
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-09-05T23:57:19.537+00:00

Hello @Luuk de Vlugt ,

Wanted to check if any the two approaches were helpful as a workaround and if you have any further questions do let me know.

Thank you
Luuk de Vlugt 51 Reputation points

2022-09-06T08:12:18.487+00:00

Hey @KranthiPakala-MSFT ,

Option 1 could work for us. But i am wondering if the problems are not the same but less frequent? Because also the snapshot folders need to be updated every hour? So the likelihood of the error occuring is lower but can still happen right?

I dont think option 2 will work for us because we have a lot of changes, so a lot of useless records will be created.

And I was wondering if the azure synapse link is affected by the fact that I run a query. So for instance if I run a query that takes a while, and at the same time the azure synapse link wants to update the folder that I am using for my query; will the azure synapse link fail? Or will it later on try to update the record again? Will running a query give incorrect/incomplete data in the data lake?

Thanks!
Luuk de Vlugt 51 Reputation points

2022-09-07T14:45:32.407+00:00

Hey @KranthiPakala-MSFT ,

Did you have a chance to look at this question:
"And I was wondering if the azure synapse link is affected by the fact that I run a query. So for instance if I run a query that takes a while, and at the same time the azure synapse link wants to update the folder that I am using for my query; will the azure synapse link fail? Or will it later on try to update the record again? Will running a query give incorrect/incomplete data in the data lake?"

I saw that I could use the option ROWSET_OPTIONS = '{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"] }'. And now I don't get any errors when I run the query but I am not really sure what the consequences are of this option. Will the data not be updated in the datalake? I am fine with the fact that my query is not completely correct but I dont want to affect the source data in de data lake with this option.
Do you know what happens when you use this option?

Thanks!
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-09-30T16:06:07.247+00:00

Hello @Luuk de Vlugt ,

As per my understanding Synapse link shouldn't fail as the query can't take a lock on the files. I'm also waiting for a confirmation from product team. Will update you as soon as I hear back from the team. The product team is having internal discussion (between dataverse and Synapse) to provide more clarification on the expected behavior and how to handle query failures.

The serverless SQL pools can't read files that are being modified while the query is running. The query can't take a lock on the files. If you know that the modification operation is append, then you can try to set the following option: {"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}.

The ALLOW_INCONSISTENT_READS read option will disable file modification time check during the query lifecycle and read whatever is available in the files that are referenced by the external table. In appendable files, the existing content is not updated, and only new rows are added. Therefore, the probability of wrong results is minimized compared to the updateable files. This option might enable you to read the frequently appended files without handling the errors.
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-09-30T16:35:13.713+00:00
Update:

(Assuming In place update mode - Live tables) As per my understanding Synapse link shouldn't fail as the query can't take a lock on the files. So, there is a chance you run the risk of getting Error Code 12 error for the query you are running. So, to overcome this, for querying any External table created with In-Place Update mode should use Partitioned tables (Snapshot data).

Append-Only Mode: With this mode Dataverse users can use the Live tables as it allows Inconsistent Reads on these External Tables. Since the data is getting appended in the underlying files, this will allow you to perform Dirty reads on Append-only tables.

Hope this clarifies.
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-10-04T00:17:10.68+00:00

Hello @Luuk de Vlugt ,

Just checking to see if the above information was helpful. If you have any further questions, please let us know. Thank you
Becky Shao 5 Reputation points

2023-01-30T21:36:04.4+00:00

Hi @KranthiPakala-MSFT
Becky Shao 5 Reputation points

2023-01-30T21:50:35.7566667+00:00

Hi @KranthiPakala-MSFT

We recently had same failure with same error code 12, we are querying the snapshot tables (_partitioned) and the only exception is the "OptionsetMetadata" as it doesn't have snapshot.

Could you please advise if your product team has fixed the known problem last year? or it is still due to the same problem as Luuk de Vlugt had please?

thanks

Becky
Marc De Vreese 16 Reputation points

2023-02-15T17:45:18.39+00:00

Hi @KranthiPakala-MSFT

We are experiencing the same error as @Becky Shao on the "OptionsetMetadata" table. Any news on this ?

Thanks,

Marc
Sawan Musthyala 0 Reputation points

2023-02-21T02:24:25.9133333+00:00

Hi @KranthiPakala-MSFT
Any resolution on this issue as we are experiencing the same error with "OptionsetMetadata"? error code 12.

Thanks,
Sawan
Luuk de Vlugt 51 Reputation points

2023-05-31T08:16:15.0233333+00:00
Hey,

We still experience this problem. We build in a retry, so if the query fails it just retries again with a max of 5 time. It is running for almost half a year now and it never exceeded the 5 tries.

However I am still searching for a better solution than this. Really strange that microsoft creates a product where you are not able to query the data.

The snapshot folders are not a solution for us because they are more then one month behind.

The idea of the allow inconsistent reads is that you create a view or external table in your own sql database that exists on the same server as the replicated database. In the master within the ondemand server pool you can create an extra "database". In this database you are allowed to create views and extranal tables with this option "allow inconsistent reads".

I have one view with this option and it seems to work, however I am a little afraid to use it because I cannot find what the "inconsistent" part means.

Will the data you query be inconsistent?

And will the data be wrong, old or will the data be incomplete?

Is it possible that the data in the datalake will become inconsistent with the data in dynamics because of the dirty read?

When will the azure synapse link sync with the data lake when you query something with "allow inconsistent reads"

The few documentation that I found does not answer these questions in my opinion.

@KranthiPakala-MSFT , you said that micosoft acknowledged the problem and are working on a solution. What is the status?

1 answer

PEYRAS Denis 6 Reputation points

2023-02-24T10:29:37.0766667+00:00

Hi,

Same issue. Any solution?

I try to add the option
TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}'
but get this error:
Operation CREATE EXTERNAL TABLE is not allowed for a replicated database.

We can not use Dedicated SQL Pool (too expensive) and Azure Synapse Link create automaticly all external table.
Does the working solution really consist to add the the code "TABLE_OPTIONS = " ?

Regards,
Denis
Please sign in to rate this answer.

1 person found this answer helpful.
Juan Gustavo Ganduglia 5 Reputation points

2023-05-24T10:06:24.1366667+00:00

Hi, same issue as above, any solution?

it is an automatic setup for CRM replication. The use of it is becoming a blocker

Luuk de Vlugt 51 Reputation points

2023-05-31T08:15:33.5366667+00:00

Hey,

We still experience this problem. We build in a retry, so if the query fails it just retries again with a max of 5 time. It is running for almost half a year now and it never exceeded the 5 tries.

However I am still searching for a better solution than this. Really strange that microsoft creates a product where you are not able to query the data.

The snapshot folders are not a solution for us because they are more then one month behind.

The idea of the allow inconsistent reads is that you create a view or external table in your own sql database that exists on the same server as the replicated database. In the master within the ondemand server pool you can create an extra "database". In this database you are allowed to create views and extranal tables with this option "allow inconsistent reads".

I have one view with this option and it seems to work, however I am a little afraid to use it because I cannot find what the "inconsistent" part means.

Will the data you query be inconsistent?

And will the data be wrong, old or will the data be incomplete?

Is it possible that the data in the datalake will become inconsistent with the data in dynamics because of the dirty read?

The few documentation that I found does not answer these questions in my opinion.

@KranthiPakala-MSFT , you said that micosoft acknowledged the problem and are working on a solution. What is the status?

Rajnish Rajput 0 Reputation points

2023-07-04T12:50:49.3933333+00:00

Hello @KranthiPakala-MSFT

I am facing same issue since last 3 days, deleted and recreated the storage but still facing the same issue. My work is hampered because of it.

Can you please assist in fixing this issue or provide any further update from Microsoft.
Sign in to comment