SQL error when upgrading from 2009/2013 to 2016 CU 10

sebsid

Hi! It has come to my attention that when we upgrade a NAV database from either 2009 R2 or 2015 (that was previously updatet from 2009R2), we receive a new error that only seem to occur from version 9.0.46621 (NAV 2016 CU 10). We receive the following error in event Viewer as soon as we start the NAV service. If i run with an older version, say 9.0.45241, i don't get this error. Does anyone else get this error?
The error is as following:

Server instance: nav_test
Tenant ID: 
User: 
Type: Microsoft.Dynamics.Nav.Types.Exceptions.NavSqlException
ErrorNumber: 102
SuppressMessage: False
SuppressExceptionCreatedEvent: False
FatalityScope: None
Message:
  The following SQL error was unexpected:
  Incorrect syntax near '='.
  Statement(s) could not be prepared.
  SQL statement:
  DECLARE @LastActiveDate AS DATETIME = GETUTCDATE();UPDATE [nav_test].dbo.[Server Instance] SET [Last Active] = @LastActiveDate WHERE [Server Instance ID] = @0;SELECT IIF(@@ROWCOUNT = 0, NULL, @LastActiveDate)
  StackTrace:
     at System.Environment.GetStackTrace(Exception e, Boolean needFileInfo)
     at System.Environment.get_StackTrace()
     at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ThrowNavSqlException(SqlExceptionAdapter exceptionAdapter, NavSqlCommand command, Boolean isLastExceptionARollbackCause, Boolean logExceptions)
     at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ExecuteFunction[T](Func`1 function, NavSqlCommand command)
     at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.<>c__DisplayClass12`1.<ExecuteFunctionWithTrace>b__11()
     at Microsoft.Dynamics.Nav.Types.NavThread.RunExternalAction[T](Func`1 action)
     at Microsoft.Dynamics.Nav.Runtime.Heartbeat.<>c__DisplayClass2.<ExecuteWriteHeartbeatToServiceInstanceTable>b__1()
     at Microsoft.Dynamics.Nav.Runtime.NavPerformanceCounterSetter.UpdatePerformanceCountersWithDuration(Stopwatch stopWatch, Action action, NavPerformanceCounterEntry performanceCounterEntry)
     at Microsoft.Dynamics.Nav.Runtime.Heartbeat.ExecuteWriteHeartbeatToServiceInstanceTable(NavDatabase database, NavSqlCommand updateCommand, NavSqlConnectionScope conScope)
     at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ExecuteCommandAndCacheOnSuccess[T](NavSqlCommandCacheKey commandKey, Func`1 createFunc, Func`2 executeFunc)
     at Microsoft.Dynamics.Nav.Runtime.Heartbeat.WriteHeartbeatToServiceInstanceTable()
     at Microsoft.Dynamics.Nav.Runtime.PeriodicScheduler.RunAction()
     at Microsoft.Dynamics.Nav.Runtime.ThreadPeriodicScheduler.DoScheduledAction()
     at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
     at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
     at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
     at System.Threading.ThreadHelper.ThreadStart()
HResult: -2146233088
----------------------------------
Type: System.Data.SqlClient.SqlException
ClientConnectionId: ef4984a3-9d60-43dc-815a-834372ec2934
Class: 15
LineNumber: 1
Number: 102
Server: NAV
State: 1
Source: .Net SqlClient Data Provider
ErrorCode: -2146232060
Message:
  Incorrect syntax near '='.
  Statement(s) could not be prepared.
StackTrace:
     at System.Data.SqlClient.SqlConnection.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.ExecuteScalar()
     at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ExecuteFunction[T](Func`1 function, NavSqlCommand command)
HResult: -2146232060

kine

Ok, found the problem... IIF is supported since SQL 2012. When you will check the system requirements for NAV 2016, you will find out that SQL 2008 is not supported. I expect that you have used the SQL 2008 as I was... ;-)

Solution - use SQL 2012 or 2014 or 2016 with NAV 2016

kennie

This part of the SQL statement looks odd:
WHERE [Server Instance ID] = @ROWCOUNT = 0

Have you contacted support?

manisharma31

Have you created new service using CMD prompt or Admin Shell ?

manisharma31

Check this if it can help http://www.myerrorsandmysolutions.com/how-to-create-nav-instances-with-multiple-builds-in-the-same-machine

kine

I have exactly same problem with one instance of NAV 2016 CU11. I have nearly 40 instances running on the server using 4 different builds (our development server), and only one instance of CU11 has this issue (there are 2 other instances with same build).

All instances were created in same way - from the Admin console, and "upgraded" through powershell script (moving the config files, changing the paths inside, changing the service path in registry).

Have you found the reason?

kine

Ok, found the problem... IIF is supported since SQL 2012. When you will check the system requirements for NAV 2016, you will find out that SQL 2008 is not supported. I expect that you have used the SQL 2008 as I was... ;-)

Solution - use SQL 2012 or 2014 or 2016 with NAV 2016