Azure AI Search Is Not Allowing Track Changes For A View

Bernard Brown 40 Reputation points
2024-01-26T14:09:38.4566667+00:00

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.

Azure AI Search
Azure AI Search
An Azure search service with built-in artificial intelligence capabilities that enrich information to help identify and explore relevant content at scale.
623 questions
0 comments No comments
{count} votes

Accepted answer
  1. brtrach-MSFT 14,231 Reputation points Microsoft Employee
    2024-01-28T05:10:19.15+00:00

    @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.


0 additional answers

Sort by: Most helpful