Hi,
I'm struggling to understand a method to query Synapse data without receiving the 'Operating system error code 12(The access code is invalid.).' error. This is now becoming a serious obstacle and I would like your help to understand how to bypass it for my needs.
I have Power BI (PBI) reports that call SQL Views that reside in a Serverless SQL pool in Synapse. These SQL Views query the External Tables held within Synapse's 'Lake database'. When performing a PBI data refresh, or developing a PBI report which triggers Power Query data evaluations, the refresh will fail due to the refresh taking place at the same time a Dynamics update is being pushed to Synapse. It seems any processes against Synapse will be stopped so Synapse can receive the Dynamics update.
Question 1 - Is the stopping of processes against Synapse, when Synapse receives a Dynamics update, expected behaviour? Data Export Services, which is what Synapse is replacing, didn't have this problem so I would be surprised if its replacement (Synapse) was to designed to have this issue.
Question 2 - I need to understand how I can query/retrieve Synapse data without it failing due to this Dynamics/Synapse locking issue. Is there a suitable method to the following needs?
I have seen the suggestion of using the _partitioned tables. I understand these _partitioned tables are updated hourly and therefore the occurrence of Synapse locking will be less. This is still not an option for me as I need to refresh/develop throughout the day and not keep facing this problem on the hour (or throughout each hour if the updates are staggered per Entity table).
I've also read the option of querying the Azure Data Lake Storage Gen2 CSV files. On this option I have two further questions:
a) When I view each CSV file, for each Entity, I find they have no header row. Without this header row I cannot query the columns via my SQL Views. How do I produce the header row in each CSV file?
b) The below link suggests that even when querying the CSV files, I will still face the locking issue. Is this true, would using the OPENROWSET command against the CSV files still incur the Dynamics/Synapse locking issue?
https://learn.microsoft.com/en-us/answers/questions/988935/cannot-bulk-load-file-error-code-12-azure-synapse.html
I've read the method of importing data from Dynamics to Synapse using 'Append only' mode, but this will bring in the historical changes which will greatly increase our data volume, which will incur greater Serverless SQL pool querying costs. This isn't viable as it will be costly.
Please can someone help me understand how to query Synapse data throughout working hours, when our Dynamics implementation is continuously updated, so I can refresh/build my PBI reports? I just do not understand how the standard approach to querying Synapse's table faces this locking issue.
Thanks in advance.