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

sebsidsebsid Member Posts: 27
edited 2016-09-20 in NAV Three Tier
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

Best Answer

Answers

  • kenniekennie Member Posts: 34
    This part of the SQL statement looks odd:
    WHERE [Server Instance ID] = @ROWCOUNT = 0

    Have you contacted support?
  • manisharma31manisharma31 Member Posts: 285
    Have you created new service using CMD prompt or Admin Shell ?
    Regards,
    Manish
  • kinekine Member Posts: 12,562
    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?
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
Sign In or Register to comment.