Dashboard - SqlException: Invalid column name 'id' for COLLATION Turkish_CI_AS

[edelibas]

Hi,

I'm trying to get dashboard but unfortunately get this exception.
I think IMonitoringApi.SqlServer.cs line 184 should be updated with Id
Thanks for great project.
Best regards.

My packages details :

<PackageReference Include="DotNetCore.CAP" Version="2.6.0" />
 <PackageReference Include="DotNetCore.CAP.RabbitMQ" Version="2.6.0" />
 <PackageReference Include="DotNetCore.CAP.SqlServer" Version="2.6.0" />
 <PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.0.0" />
 <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.0.0" />

System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'id'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in C:\projects\dapper\Dapper\SqlMapper.cs:line 1053
   at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext() in C:\projects\dapper\Dapper\SqlMapper.cs:line 1081
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in C:\projects\dapper\Dapper\SqlMapper.cs:line 723
   at DotNetCore.CAP.SqlServer.SqlServerMonitoringApi.GetTimelineStats(IDbConnection connection, String tableName, String statusName, IDictionary`2 keyMaps)
   at DotNetCore.CAP.SqlServer.SqlServerMonitoringApi.GetHourlyTimelineStats(IDbConnection connection, String tableName, String statusName)
   at DotNetCore.CAP.SqlServer.SqlServerMonitoringApi.<>c__DisplayClass5_0.<HourlySucceededJobs>b__0(IDbConnection connection)
   at DotNetCore.CAP.SqlServer.SqlServerStorage.UseConnection[T](Func`2 func)
   at DotNetCore.CAP.SqlServer.SqlServerMonitoringApi.UseConnection[T](Func`2 action)
   at DotNetCore.CAP.SqlServer.SqlServerMonitoringApi.HourlySucceededJobs(MessageType type)
   at DotNetCore.CAP.Dashboard.Pages.HomePage.Execute()
   at DotNetCore.CAP.Dashboard.RazorPage.TransformText(String body)
   at DotNetCore.CAP.Dashboard.RazorPage.ToString()
   at DotNetCore.CAP.Dashboard.RazorPageDispatcher.Dispatch(DashboardContext context)
   at DotNetCore.CAP.DashboardMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Server.IIS.Core.IISHttpContextOfT`1.ProcessRequestAsync()
ClientConnectionId:55e66724-6fe5-4874-a698-297639510fc5
Error Number:207,State:1,Class:16

[yang-xiaodong]

Hello, As far as I know SQL Server queries are case-insensitive.

[edelibas]

Thank you for quick response, yes you are right but also this problem is in COLLATION Turkish_CI_AS.

[yang-xiaodong]

Fixed in v3.0, nuget package is released!

[ayzdru]

SqlException: Invalid column name 'id' for COLLATION Turkish_CI_AS

CAP/src/DotNetCore.CAP.SqlServer/IMonitoringApi.SqlServer.cs

Line 178 in 73949a4

count(id) [Count]

CAP/src/DotNetCore.CAP.SqlServer/IMonitoringApi.SqlServer.cs

Line 189 in 73949a4

count(id) [Count]

Problem is "count(id)" not "count(Id)".