ML.Net model builder - Why do I get an SQL data error " Incorrect syntax near the keyword 'Open' "?

Question

I am using ML.Net Model Builder version: 16.3.0.2056001 on Visual Studio 2019, using .Net 5.0.

Using Model Builder Wizard with a fresh template project, I select Add > Machine Learning > Connect to SQL Server, which is Tested successfully and the Table data loaded correctly into Preview.

When I then continue to the next screen and click Start Training, I get the following error:

Incorrect syntax near the keyword 'Open'

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at Microsoft.ML.Data.DatabaseLoader.Cursor.get_DataReader()
at Microsoft.ML.Data.DatabaseLoader.Cursor.MoveNextCore()
at Microsoft.ML.Data.RootCursorBase.MoveNext()
at Microsoft.ML.AutoML.DatasetDimensionsUtil.CountRows(IDataView data, UInt64 maxRows)
at Microsoft.ML.AutoML.ExperimentBase2.Execute(IDataView trainData, ColumnInformation columnInformation, IEstimator1 preFeaturizer, IProgress1 progressHandler) at Microsoft.ML.ModelBuilder.AutoMLService.Experiments.AutoMLExperiment3.<>c__DisplayClass21_0.b__5() in //src/Microsoft.ML.ModelBuilder.AutoMLService/Experiments/AutoMLExperiment.cs:line 80
at System.Threading.Tasks.Task1.InnerInvoke() at System.Threading.Tasks.Task.Execute() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.ML.ModelBuilder.AutoMLService.Experiments.AutoMLExperiment3.d__21.MoveNext() in //src/Microsoft.ML.ModelBuilder.AutoMLService/Experiments/AutoMLExperiment.cs:line 107
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.ML.ModelBuilder.AutoMLEngine.d__26.MoveNext() in /_/src/Microsoft.ML.ModelBuilder.AutoMLService/AutoMLEngineService/AutoMLEngine.cs:line 150

This is standard scaffold project and I've tried adding the following nuget packages to try and resolve this:

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>net5.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.ML" Version="1.5.4" />
    <PackageReference Include="Microsoft.ML.Experimental" Version="0.17.4" />
  </ItemGroup>

</Project>

It may be something with the data. Could there be some nulls where you may not expect them to be? Something to try, though, is to ignore columns and see if it goes through once one is ignored. — Jon, Mar 01 '21 at 00:47
@Jon it turns out it was column names in the database 'Open' and 'Close'. Renaming them has fixed the issue. Thanks for the pointer. — Holland Risley, Mar 01 '21 at 08:09

score 2 · Answer 1 · answered Mar 01 '21 at 08:08

OK, I have solved the problem. I am using trading market data which includes a column for price called 'Open' and another for 'Close'. ML.Net did not like these column names. As soon as I have renamed them to OpenPrice and ClosePrice, the training is running fine :)

ML.Net model builder - Why do I get an SQL data error " Incorrect syntax near the keyword 'Open' "?

1 Answers1