Using ODBC Driver 18 for SQL Server

[ThomasBudy]

When I changed to ODBC driver my application code which uses temp tables stopped working. My unit testing still works with ODBC driver however, so I also include my new ODBC unit test configuration.

=== Code ===

	session = NHUtils.GetCurrentSession();
	var dialect = ((ISessionFactoryImplementor)session.SessionFactory).Dialect;
	if(!dialect.SupportsTemporaryTables) {
		throw new NotSupportedException("Temporary table support missing from dialect " + dialect);
	}

	var name = dialect.GenerateTemporaryTableName("TestTable");
	var i = session.CreateSQLQuery("create table " + name + " (id INT not null, primary key (id)) select count(*) from " + name).UniqueResult<int>(); //Still works with ODBC
	var i2 = session.CreateSQLQuery("select count(*) from " + name).UniqueResult<int>(); //Throws OdbcException below when new ODBC configuration is used

=== Exception ===

OdbcException: ERROR [42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Invalid object name '#TestTable'.
ERROR [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Statement(s) could not be prepared.

=== New ODBC configuration, does not work ===

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
 <session-factory>
 <property name="connection.connection_string">Driver={ODBC Driver 18 for SQL Server};Server=my_server;Database=my_database;Trusted_Connection=yes;TrustServerCertificate=yes</property>
 <property name="connection.driver_class">NHibernate.Driver.OdbcDriver</property>
    <property name="dialect">NHibernate.Dialect.MsSql2012Dialect</property>
  </session-factory>
</hibernate-configuration>

=== Old working configuration ===

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
 <session-factory>
   <property name="connection.connection_string">Server=my_server;database=my_database;Integrated Security=SSPI;</property>
   <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
   <property name="dialect">NHibernate.Dialect.MsSql2012Dialect</property>
  </session-factory>
</hibernate-configuration>

=== New ODBC unit test configuration, working ===

NHUtils.SessionFactoryProperties["dialect"] = "NHibernate.Dialect.MsSql2012Dialect";
NHUtils.SessionFactoryProperties["connection.driver_class"] = "NHibernate.Driver.OdbcDriver";
NHUtils.SessionFactoryProperties["connection.connection_string"] = "Driver={ODBC Driver 18 for SQL Server};Server=my_server;database=my_test_database;Trusted_Connection=yes;TrustServerCertificate=yes;";
NHUtils.SessionFactoryProperties["current_session_context_class"] = "NHibernate.Context.ThreadStaticSessionContext, NHibernate";
NHUtils.SessionFactoryProperties["show_sql"] = "true";

NHUtils.SessionFactoryProperties["format_sql"] = "true";
NHUtils.UsePreloader = false; // If I set to true, the tests start to throw the same error but if I set UsePreloader to false in my application I still get the same error.

=== PreLoader ===

/// <summary>
/// Preload all entities that go into the 2nd level cache in read-only mode.
/// The work is done in a separate thread.
/// </summary>
private static void Preload(ISessionFactory sf)
{
    if(!UsePreloader) {
        return;
    }

    var preloader = new Thread(StartPreload) { Priority = ThreadPriority.Lowest, Name = "Preloader" };
    preloader.Start(sf);
}

private static void StartPreload(object o)
{
    var sf = (ISessionFactory)o;

    // Sleep a little so that other more important threads can finish in peace.
    Thread.Sleep(500);
    foreach(var entry in sf.GetAllClassMetadata()) {
        try {
            using(var session = sf.OpenSession())
            using(session.BeginTransaction()) {
                // We have to use the actual type to read the cache property - maybe there's a better way...
                var aep = entry.Value as AbstractEntityPersister;
                if(aep == null || !(aep.Cache is ReadOnlyCache)) {
                    continue;
                }

                session.CreateCriteria(entry.Key).List();
                var transaction = session.GetCurrentTransaction();
                if(transaction == null) {
                    throw new Exception("Could not get current transaction.");
                }

                transaction.Commit();
            }
        } catch(Exception e) {
            log.Warn("Unable to preload " + entry.Key, e);
        }
    }
}

=== Versions ===
NHibernate version=5.3.15
sql server version 15.0.2000.5