Incorrect syntax near the keyword 'ORDER'.

[dmm-l-mediehus]

Hey, I just came back to try your package after a while (from Azure AI). I switched like this:

//memoryBuilder.WithAzureAISearchMemoryDb(configuration["Azure:AISearch:Endpoint"]!, configuration["Azure:AISearch:APIKey"]!);
memoryBuilder.WithSqlServerMemoryDb(new SqlServerConfig
{
    ConnectionString = configuration.GetConnectionString("AIDataContext")!,
    EmbeddingsTableName = "KMEmbeddings",
    MemoryCollectionTableName = "KMMemoryCollections",
    MemoryTableName = "KMMemories",
    TagsTableName = "KMTags"
});

And ran the program. It runs fine, but when I start using it, it comes with:

fail: Microsoft.KernelMemory.Pipeline.InProcessPipelineOrchestrator[0]
      Pipeline start failed
      Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'ORDER'.
         at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
         at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
         at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
         at Microsoft.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
         at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
         at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
         at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
         at Microsoft.Data.SqlClient.SqlCommand.<>c.<InternalExecuteNonQueryAsync>b__210_1(IAsyncResult result)
         at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
      --- End of stack trace from previous location ---
         at KernelMemory.MemoryStorage.SqlServer.SqlServerMemory.CreateIndexAsync(String index, Int32 vectorSize, CancellationToken cancellationToken)
         at Microsoft.KernelMemory.Handlers.SaveRecordsHandler.SaveEmbeddingsAsync(DataPipeline pipeline, CancellationToken cancellationToken)
         at Microsoft.KernelMemory.Handlers.SaveRecordsHandler.InvokeAsync(DataPipeline pipeline, CancellationToken cancellationToken)
         at Microsoft.KernelMemory.Pipeline.InProcessPipelineOrchestrator.RunPipelineAsync(DataPipeline pipeline, CancellationToken cancellationToken)
         at Microsoft.KernelMemory.Pipeline.BaseOrchestrator.ImportDocumentAsync(String index, DocumentUploadRequest uploadRequest, CancellationToken cancellationToken)
      ClientConnectionId:0593855a-dbf3-4b5e-83b1-a2ff7c29731b
      Error Number:156,State:1,Class:15

Stacktrace displays your method: at KernelMemory.MemoryStorage.SqlServer.SqlServerMemory.CreateIndexAsync(String index, Int32 vectorSize, CancellationToken cancellationToken)

My code:

chatInputModel.DocumentId = Guid.NewGuid().ToString();

Document document = new Document(chatInputModel.DocumentId).AddFiles(filePaths);
await KernelMemory.ImportDocumentAsync(document, "document-uploads", cancellationToken: CancellationToken);

while (!await KernelMemory.IsDocumentReadyAsync(chatInputModel.DocumentId, "document-uploads", CancellationToken))
{
    await Task.Delay(100, CancellationToken);
}

Edit: I tried downgrading nuget from 1.3.1 to 1.3.0, still the same issue. Then I tried downgrading nuget from 1.3.0 to 1.1.4 and the issue disappeared.

[marcominerva]

I see that in your PR you have removed the ORDER clause. This is fundamental for COLUMNSTORE INDEX definition, so this isn't the correct solution.

[mamift]

I see that in your PR you have removed the ORDER clause. This is fundamental for COLUMNSTORE INDEX definition, so this isn't the correct solution.

@marcominerva The ORDER clause for CREATE COLUMNSTORE INDEX is only supported on SQL Server 2022 and newer.

I'm running SQL Server 2017 and removing the ORDER clause works for me. Perhaps you could do a SELECT @@version check before hand?

I note on the README you haven't mentioned which SQL Server version is a minimum requirement. If you don't support 2017 you should at least mention what minimum version is required.

[kbeaugrand]

@mamift @marcominerva , even if order clause is very important regarding performance. I updated the PR from @dmm-l-mediehus to check SQL server version before adding this clause to the tables creation.

In that way, this should work for all.