AI Search Indexer started failing with opperand type clash error

Question

Starting 26 Feb 2025 at around 2 PM EET, indexers on our Azure AI Search services started failing on all our environments at around the same time. The indexers have previously worked with no other issue. The error is Operand type clash: datetime2 is incompatible with timestamp.

We currently use a column named HighWatermark as the HighWatermark column with the SQL data type datetime2. In the c# indexer code, we have defined the model type used for the HighWatermark as Datetime (we have also tried to change it now to DateTimeOffset with no success).

C#Copy

[SimpleField(IsKey = true)]
public string DocumentKey { get; set; }

[SimpleField(IsFilterable = true)]
public int Id { get; set; }

[SimpleField(IsFilterable = true, IsFacetable = true)]
public int Type { get; set; }

[SimpleField(IsHidden = true)]
public DateTime HighWatermark { get; set; }

[SimpleField(IsFilterable = true, IsFacetable = true)]
public DateTimeOffset ModifiedDate { get; set; }

[...]

The first time when the indexer runs it seems to work successfully. But for later runs it always crashes with the mentioned error

This is the configuration for the AI Search Instance:

JSONCopy

{
  "@odata.context": "...",
  "@odata.etag": "",
  "name": "...",
  "description": null,
  "type": "azuresql",
  "subtype": null,
  "credentials": {
    "connectionString": "..."
  },
  "container": {
    "name": "db_view",
    "query": null
  },
  "dataChangeDetectionPolicy": {
    "@odata.type": "#Microsoft.Azure.Search.HighWaterMarkChangeDetectionPolicy",
    "highWaterMarkColumnName": "HighWatermark"
  },
  "dataDeletionDetectionPolicy": {
    "@odata.type": "#Microsoft.Azure.Search.SoftDeleteColumnDeletionDetectionPolicy",
    "softDeleteColumnName": "IsDeleted",
    "softDeleteMarkerValue": "true"
  },
  "encryptionKey": null,
  "identity": null
}

The HighWatermark is computed in DB view as

SQLCopy

GREATEST(
			i1.[ModifiedDate],
			i1.[DeletedDate],
			i2.[ModifiedDate],
			i2.[DeletedDate],
			i3.[SettingsModifiedDate])
	   ) 

All of the columns are of type Datetime2(7).

Answer 1

Hi @Anonymous ,
Thanks for the question and using MS Q&A platform.
The Azure AI Search indexer is hitting a snag because of an "Operand type clash: datetime2 is incompatible with timestamp." This happens when the convertHighWaterMarkToRowVersion setting is turned on, making the system read the HighWatermark column as a rowversion (timestamp) instead of datetime2(7). These types don't mix, causing the indexer to fail. To fix this, set convertHighWaterMarkToRowVersion to false in the indexer settings. This way, the system will correctly recognize the datetime2(7) column.

Azure AI Search has the HighWaterMarkChangeDetectionPolicy for efficient change detection during data indexing. This policy uses a high water mark column, usually a rowversion or timestamp column in SQL databases, to track and index only changed data. By watching the highest value in this column, the system optimizes indexing and skips over unchanged data.

To set this up, specify the HighWaterMarkColumnName property in the indexer settings to point to the high water mark column. Make sure this column is a compatible type, like rowversion, to avoid type conflicts. If it's set as datetime2 and the indexer expects a rowversion, you'll get an operand type mismatch error. Keeping the column type and the indexer’s expectations aligned is key for smooth operation.

Properly configuring the HighWaterMarkChangeDetectionPolicy and matching convertHighWaterMarkToRowVersion with the column data type ensures efficient and reliable indexing in Azure AI Search. This keeps the search index up-to-date with the latest data changes while avoiding unnecessary re-indexing of unchanged records.
https://stackoverflow.com/questions/46351605/how-can-i-use-change-detection-when-indexing-a-sql-view-with-an-azure-search-ind
https://learn.microsoft.com/en-us/azure/search/search-howto-index-changed-deleted-blobs?tabs=portal
https://learn.microsoft.com/en-us/dotnet/api/azure.search.documents.indexes.models.highwatermarkchangedetectionpolicy?view=azure-dotnet&utm_source=chatgpt.com
https://learn.microsoft.com/en-us/azure/search/search-how-to-index-sql-database?utm_source=chatgpt.com&tabs=portal-check-indexer
If the answer is helpful, please click Accept Answer and kindly upvote it so that other people who faces similar issue may get benefitted from it.
Let us know if you need any assistances.