Configure AdoJobStores of Quartz with LocalDb

Question

I've been using Quartz AdoJobStores with SQL Server for a long time without any problem. Recently I changed the SQL Server database to LocalDB database and received the following exception:

Quartz.JobPersistenceException: Couldn't obtain job names: Invalid object name 'QRTZ_JOB_DETAILS'.
System.Data.SqlClient.SqlException: Invalid object name 'QRTZ_JOB_DETAILS'.
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, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
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 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at Quartz.Impl.AdoJobStore.StdAdoDelegate.SelectJobsInGroup(ConnectionAndTransactionHolder conn, GroupMatcher`1 matcher) 
at Quartz.Impl.AdoJobStore.JobStoreSupport.GetJobNames(ConnectionAndTransactionHolder conn, GroupMatcher`1 matcher)

This is the Quartz configuration section on my App.config:

<quartz>
<add key="quartz.scheduler.instanceName" value="RemoteServer" />
<add key="quartz.threadPool.type" value="Quartz.Simpl.SimpleThreadPool, Quartz" />
<add key="quartz.threadPool.threadCount" value="10" />
<add key="quartz.threadPool.threadPriority" value="Normal" />
<add key="quartz.scheduler.exporter.type" value="Quartz.Simpl.RemotingSchedulerExporter, Quartz" />
<add key="quartz.scheduler.exporter.port" value="9999" />
<add key="quartz.scheduler.exporter.bindName" value="QuartzScheduler" />
<add key="quartz.scheduler.exporter.channelType" value="tcp" />
<add key="quartz.scheduler.exporter.channelName" value="httpQuartz" />
<add key="quartz.scheduler.instanceId" value="instance_one" />
<add key="quartz.jobStore.type" value="Quartz.Impl.AdoJobStore.JobStoreTX, Quartz" />
<add key="quartz.jobStore.useProperties" value="true" />
<add key="quartz.jobStore.dataSource" value="default" />
<add key="quartz.jobStore.tablePrefix" value="QRTZ_" />
<add key="quartz.jobStore.lockHandler.type" value="Quartz.Impl.AdoJobStore.UpdateLockRowSemaphore, Quartz" />
<add key="quartz.jobStore.driverDelegateType" value="Quartz.Impl.AdoJobStore.SqlServerDelegate, Quartz" />
<add key="quartz.dataSource.default.connectionString" value="Data Source=(localdb)\v11.0; Initial Catalog=Scheduling;MultipleActiveResultSets=true;Integrated Security=SSPI" />
<add key="quartz.dataSource.default.provider" value="SqlServer-20" />
<add key="quartz.jobStore.misfireThreshold" value="60000" />

The database exists, I can connect to it using Navicat but is empty without any table. I thought that maybe I could be the database provider and reading the page Quartz.NET - Lesson 9: JobStores I found that these are database providers available:

• SqlServer-20 - SQL Server driver for .NET Framework 2.0
• OracleODP-20 - Oracle's Oracle Driver
• OracleODPManaged-1123-40 Oracle's managed driver for Oracle 11
• OracleODPManaged-1211-40 Oracle's managed driver for Oracle 12
• MySql-50 - MySQL Connector/.NET v. 5.0 (.NET 2.0)
• MySql-51 - MySQL Connector/:NET v. 5.1 (.NET 2.0)
• MySql-65 - MySQL Connector/:NET v. 6.5 (.NET 2.0)
• SQLite-10 - SQLite ADO.NET 2.0 Provider v. 1.0.56 (.NET 2.0)
• Firebird-201 - Firebird ADO.NET 2.0 Provider v. 2.0.1 (.NET 2.0)
• Firebird-210 - Firebird ADO.NET 2.0 Provider v. 2.1.0 (.NET 2.0)
• Npgsql-20 - PostgreSQL Npgsql

QUESTIONS

1. I can use LocalDB database with SQLServerDelegate?
2. The problem is given because Quartz do not have a provider for LocalDB?

Answer 1

If you connect to "(localdb)\v11.0" via SSMS (Sql Server Management Studio)...you can create a new db (QuartzDB for example). Then run the scripts to create it.

I found the scripts with this google search

https://www.google.com/search?q=%22CREATE+TABLE+%5Bdbo%5D.%5BQRTZ_CALENDARS%5D%22

Here is one I found:

https://raw.githubusercontent.com/quartznet/quartznet/master/database/tables/tables_sqlServer.sql

or this one:

https://github.com/quartznet/quartznet/blob/main/database/tables/tables_sqlServer.sql

I think then you can use quartz library against that database, using SqlServer-20. Note, a provider does not exist for every single version of sql-server out there, the 'SqlServer-20' works for multiple versions of sql-server in the "least common denominator" type of way.

I think the biggest thing is that... quartz library doesn't create the tables/DDL, it uses already created DDL.