I am running a view against a Synapse linked server in SSMS. These are blob files. These links are contained in An Azure database. I get this error:
Msg 46836, Level 16, State 1, Line 2
External table schema does not match actual schema from remote table: Mismatch between actual and expected number of columns in the remote query result. Expected: 41, actual: 0
If a run the query again it runs successfully. It is like the tables go to sleep.
This is causing issues because, about 18 months ago a data pipeline was set up in Azure that consists of batches of stored procedures. It loops through the batches and the stored procedures until it fails or completes. When it runs through these stored procedures, I often get a error message similar to above.
I have built a stored procedure to run a series of TRY / CATCH statements, with COUNT(*) FROM table statements to try to wake up the tables but this does not seem to work.
There is very similar development version of the data warehouse with the same pipelines and tables and this one works nearly every time.
I am not a DBA, so am struggling to fix this.
