NHibernate - Incorrect syntax when migrating from PGSQL to MSSQL

Question

I'm currently trying to migrate an ASP.NET MVC 4 website from postgreSQL to MS SQL (to then use SQL Azure). The website works flawlessly with pgsql. I expected that just changing the connection string and database driver would be enough, but I'm getting an error message saying this:

NHibernate.HibernateException: Incorrect syntax near the keyword 'Public'. ---> System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'Public'. 

Note that we use NHibernate Castle for the database interactions. This is the web.config part showing the NHibernate configuration:

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<session-factory>
  <property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
  <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
  <property name="connection.connection_string">Data Source=.\SQLEXPRESS;Initial Catalog=folke;Integrated Security=SSPI;</property>
  <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
  <property name="proxyfactory.factory_class">NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle</property>
  <mapping assembly="Folke" />
</session-factory>

The error is triggered when I call this method:

public static void Install()
    {
        new SchemaExport(CurrentConfiguration).Execute(true, true, false);
    }

This is the full error message:

NHibernate.HibernateException: Incorrect syntax near the keyword 'Public'. ---> System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'Public'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at NHibernate.Tool.hbm2ddl.SchemaExport.ExecuteSql(IDbCommand cmd, String sql) at NHibernate.Tool.hbm2ddl.SchemaExport.Execute(Action1 scriptAction, Boolean export, Boolean throwOnError, TextWriter exportOutput, IDbCommand statement, String sql) at NHibernate.Tool.hbm2ddl.SchemaExport.Execute(Action1 scriptAction, Boolean export, Boolean justDrop, IDbConnection connection, TextWriter exportOutput) at NHibernate.Tool.hbm2ddl.SchemaExport.Execute(Action1 scriptAction, Boolean export, Boolean justDrop) --- End of inner exception stack trace --- at NHibernate.Tool.hbm2ddl.SchemaExport.Execute(Action1 scriptAction, Boolean export, Boolean justDrop) at NHibernate.Tool.hbm2ddl.SchemaExport.Execute(Boolean script, Boolean export, Boolean justDrop) at FolkeLib.BaseDataAccess.Install() in e:\Documents\GitHub\Folke\FolkeLib\BaseDataAccess.cs:line 36 at Folke.Models.Installer.Install() in e:\Documents\GitHub\Folke\Folke\Models\Installer.cs:line 22 at Folke.Controllers.InstallerController.Index() in e:\Documents\GitHub\Folke\Folke\Controllers\InstallerController.cs:line 20 at lambda_method(Closure , ControllerBase , Object[] ) at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters) at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass15.<InvokeActionMethodWithFilters>b__12() at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func1 continuation) at System.Web.Mvc.ControllerActionInvoker.<>c_DisplayClass15.<>c_DisplayClass17.b__14() at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList1 filters, ActionDescriptor actionDescriptor, IDictionary2 parameters) at System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName);

Answer 1

Actually, I think it's because MS SQL Server defines PUBLIC as a keyword (http://msdn.microsoft.com/en-us/library/ms189822%28v=sql.100%29.aspx).

You probably have some table or property/column named like this. You should rename it in the mappings, or enclose it in backticks (`) to make NHibernate apply the dialect specific quote character.

You can also enable auto quoting in the NHibernate configuration.

Answer 2

It looks like you may have some Auxiliary Database Objects in your mappings. You may have to change their SQL syntax, or use dialect-scope to provide them for multiple dialects.

Custom SQL may also come from named queries, and if you use CreateSqlQuery() on ISession, but since the exception occurs during schema creation, this seems less likely. Note that some other mapping constructs (like formula and filter) may also use custom SQL in the mappings.

If this doesn't help, I think you must try to figure out what the actual attempted SQL command is. If it isn't available from the exception, it should be quite easy to do by providing the NHibernate.pdb file and the corresponding source code. And asking Visual Studio to break where the exception occurs.