Semantic Model Refresh Error - "Reported a change in schema version"

[v-jkikuchi]

I'm getting this error during scheduled refreshes:

"The OLE DB provider \"MSOLEDBSQL\" for linked server \"ServerName\" reported a change in schema version between compile time (\"CompileTime\") and run time (\"RunTime\") for table \"\"DatabaseName\".\"dbo\".\"TableName\"\"."}}"

Background:

• TableName is updated and reindexed nightly, but the process completes hours before the refresh.
• Using SQL Server 2019.

Steps I’ve Tried:

• Restarting the on-prem gateway service after reindexing.
• Restarting the entire server.
• Updating Power BI queries from SELECT * to specific column selections.

Any advice on resolving this error?

[v-jtian-msft · Accepted solution]

Hi,@v-jkikuchi .I am glad to help you.
Based on your description, it seems that you are failing Scheduled Refresh due to frequent architectural changes in the semantic model of the report while performing Scheduled Refresh right.
If my understanding is correct, I hope my suggestions below will help you.
1. you mentioned using power BI gateway. there is no concept of gateway for Power BI Report Server, because all automation operations are done locally in SQL Server by relying on Agent, so I guess you are having problems with Scheduled Refresh execution in Power BI Service.
For data source column/table name changes, these changes are schema refreshes, unfortunately Power BI Service itself does not support schema refreshes, so when you encounter schema changes, the better solution is to start from the data source.
URL:
Data refresh in Power BI - Power BI | Microsoft Learn

The refresh error you gave is usually due to a change in the structure of the database tables between compile time and run time.
Here are the suggestions I gave.
1. Try to modify the way the data source is fetched, by modifying the data fetched from the table to the corresponding view, and using the view to abstract the structure of the table. This allows you to manage the architecture at the view level without affecting the underlying table changes.
This is the most efficient way in my opinion to work with data from the data source itself, you need to make sure that after modifying the data source in the power bi desktop, you republish it to the service, ensuring that the data source credentials are correct.
Use views to virtualise tables, even if your actual table name changes, the view name of the power BI connection will not change, you can reduce the architectural changes generated by the table name/column name modifications you mentioned, thus reducing the number of refresh failures.

Writing scripts in SQL Server using SQL Server Agent can also automate this process!

I hope the related links below are helpful.
URL:

3 reasons to use views instead of tables in Power BI! - Data Mozart
CREATE VIEW (Transact-SQL) - SQL Server | Microsoft Learn
SQL Server CREATE VIEW - Creating New Views in SQL Server
ALTER VIEW (Transact-SQL) - SQL Server | Microsoft Learn
 

2. While modifying the way the data source gets the data, I recommend that you Delay the refresh time
Delayed Refresh Scheduled Refresh to ensure that after the database update and reindexing is complete, you give the system some time to stabilise before performing the Power BI refresh operation. This avoids architectural changes during the database update process.
Because the system performs a series of operations also need some time to buffer, Power BI Service on the operation of the time delay generally exists!

3. Try to use lakehouse
Lakehouse can automatically synchronise semantic models, reducing the need for manual intervention. By using Lakehouse, you can ensure the consistency and synchronisation of the data schema, thus reducing the occurrence of similar errors.
Note that lakehouse, as a service feature on Fabric, itself requires time buffering to perform refresh operations.
Solved: Re: Semantic Model not properly refreshing - Microsoft Fabric Community

I found a similar issue, other users have encountered similar issues to yours, you can try to regenerate the view and ensure that the view in sql server is modified and saved before performing a Scheduled Refresh.

The OLE DB provider "SQLNCLI" for linked server "X" reported a change in schema version between comp...
KB2498818 - FIX: Error 7359 when you run a query against a user-defined function or a view that uses...

If you have access to Microsoft Fabric, I would more than recommend using Warehouse/lakehouse to connect to SQL Server data sources. Subsequently use the data in Fabric as the data source for the report. This avoids problems and supports schema changes in lakehouse.

4. If your refresh operation is performed on Power BI Report Server, then I recommend you to try the first two suggestions above: use view as data source  &  delay Scheduled Refresh.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.