Alter view on lakehouse SQL endpoint not working

[Anne-MarijnV]

At my company old sales orders are deleted from the source system after 2 years because they are no longer needed there.

To keep the data available for reporting, I've created a pipeline that will automatically extract the sales order information and store it in a lakehouse table at the end of the year.

So I currently have a lakehouse with tables: Sales Order History 2018-2023.

For reporting I have 1 generic Sales Order History view that combines data from all these tables.

Because I'm lazy and don't want to update the view manually every year, I have made a stored procedure that will alter the view's select statement 
(something like this)

DECLARE @sql NVARCHAR(MAX);

    SELECT @sql = STRING_AGG('SELECT * FROM ' + QUOTENAME(TABLE_NAME), ' UNION ALL ')
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME LIKE 'Sales Order History%';

    SET @sql = 'CREATE OR ALTER VIEW AllHistory AS ' + @sql;

    EXEC sp_executesql @sql;

Whenever I run the stored procedure, I don't get any errors, but the view is not updated either.

Am I doing something wrong? Is this a fabric bug?

[Anne-MarijnV · Accepted solution]

deleting the lakehouse and recreating it did the trick. must've been some kind of bug

[v-hashadapu]

Hi @Anne-MarijnV , Thank you for reaching out to the Microsoft Community Forum.

Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[suparnababu8]

Hello @Anne-MarijnV 

I'm not sure why its working. SQL Server saves information about views for quick access. However, if the tables used by the view are changed, the view's information might not update automatically. To fix this, you can try the sp_refreshview command to update the view's information so it matches the updated tables. At some cases, dynamic SQL might not execute as expected. 

If you need more info please visit below urls. It might helps you

Solved: Re: SQL Query / View not loading - Microsoft Fabric Community

Fix SQL Analytics Endpoint Sync Issues in Microsoft Fabric – Data Not Showing? Here's the Solution! ...

Lakehouse T-SQL Endpoint syncing issues (force update fix) · Learn Microsoft Fabric

Thank you!