Azure AI Search Is Not Allowing Track Changes For A View

Question

I'm trying to create a Data Source that uses a view in Azure Search through the Azure Portal. It allows me to select the view, but when I try to use Track Changes, it fails to create. I know I can't turn Change Tracking for view, but the tables that are used in the view all have Change Tracking activated. Please help.

Answer 1

@Bernard Brown You can still use Change Tracking for a view indirectly by creating a stored procedure that selects data from the view and returns it to Azure AI Search. You can then use the stored procedure as the data source for Azure AI Search and enable Change Tracking for the underlying tables. Here are the steps to create a stored procedure that selects data from the view and returns it to Azure AI Search:

1. Open SQL Server Management Studio and connect to your database.
2. Create a new stored procedure using the following syntax:

   CREATE PROCEDURE [dbo].[MyViewData]
   AS
   BEGIN
       SET NOCOUNT ON;
       SELECT * FROM MyView;
   END
   

Replace MyView with the name of your view.

1. Save the stored procedure and execute it to make sure it returns the expected data.
2. In the Azure Portal, go to your Azure AI Search service and create a new data source.
3. Select "SQL" as the data source type and enter the connection string for your database.
4. Enter the name of the stored procedure you created in step 2 as the table or view name.
5. Enable Change Tracking for the underlying tables by following the instructions in the Azure Advisor document titled "View Azure Advisor recommendations that matter to you".

Once you have completed these steps, you should be able to use Track Changes for your Azure AI Search data source.