web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Database sync error: R...
Microsoft Dynamics AX (Archived)

Database sync error: Reference not supported in this version of SQL Server

(1) ShareShare
ReportReport
Posted on by Chen Wancheng

When i synchronized the database by using following cmd, i got a sync error.

What is this table [TEMPDB.DBO.T_TRVREQUISITIONLINE]? and why this error occurred.

How can i fix it.

Cmd:

Microsoft.Dynamics.AX.Deployment.Setup.exe -bindir "F:\AosService\PackagesLocalDirectory" -metadatadir F:\AosService\PackagesLocalDirectory -sqluser axdbadmin -sqlserver <azure sql database server name>.database.windows.net -sqldatabase <database name> -setupmode sync -syncmode fullall -isazuresql true -sqlpwd <sql password> >log.txt 2>&1

Error:

10/02/2017 10:17:00: AOS database sync failed. Microsoft.Dynamics.AX.Framework.Database.TableSyncException: Full sync did not complete successfully. Error: Exception:TempDB Sync Failed: System.InvalidOperationException: Database execution failed: Reference to database and/or server name in 'TEMPDB.DBO.T_TRVREQUISITIONLINE_89D1D5091B9D4913861F8296525E9983' is not supported in this version of SQL Server.

CREATE TABLE TEMPDB.DBO.T_TRVREQUISITIONLINE_89D1D5091B9D4913861F8296525E9983 (ACCOUNTINGCURRENCYAMOUNT NUMERIC(32,6) NOT NULL DEFAULT 0,AIRLINESERVICECLASS INT NOT NULL DEFAULT 0,CARRENTALVEHICLECLASS INT NOT NULL DEFAULT 0,CATEGORY NVARCHAR(30) NOT NULL DEFAULT '',DEFAULTDIMENSION BIGINT NOT NULL DEFAULT 0,ESTIMATEDDATE DATETIME NOT NULL DEFAULT '1900-01-01 00:00:00.000',EXCHANGERATE NUMERIC(32,16) NOT NULL DEFAULT 0,EXPTYPE INT NOT NULL DEFAULT 0,ISBUDGETCHECKPENDING INT NOT NULL DEFAULT 0,LEGALENTITY BIGINT NOT NULL DEFAULT 0,MERCHANT NVARCHAR(30) NOT NULL DEFAULT '',MILEAGE NUMERIC(32,6) NOT NULL DEFAULT 0,MILEAGERATE NUMERIC(32,6) NOT NULL DEFAULT 0,PERDIEMDATEFROM DATETIME NOT NULL DEFAULT '1900-01-01 00:00:00.000', PERDIEMDATEFROMTZID INT NOT NULL DEFAULT 37001,PERDIEMDATETO DATETIME NOT NULL DEFAULT '1900-01-01 00:00:00.000', PERDIEMDATETOTZID INT NOT NULL DEFAULT 37001,PERDIEMLOCATION NVARCHAR(128) NOT NULL DEFAULT '',PROJACTIVITYNUMBER NVARCHAR(50) NOT NULL DEFAULT '',PROJLINEPROPERTYID NVARCHAR(10) NOT NULL DEFAULT '',PROJTABLE BIGINT NOT NULL DEFAULT 0,RECONCILIATIONSTATUS INT NOT NULL DEFAULT 0,REFERENCEDATAAREAID NVARCHAR(4) NOT NULL DEFAULT '',SOURCEDOCUMENTLINE BIGINT NOT NULL DEFAULT 0,TRANSACTIONCURRENCYAMOUNT NUMERIC(32,6) NOT NULL DEFAULT 0,TRANSACTIONCURRENCYCODE NVARCHAR(3) NOT NULL DEFAULT '',TRVREQUISITIONTABLE BIGINT NOT NULL DEFAULT 0,PARTITION BIGINT NOT NULL DEFAULT 5637144576,RECID BIGINT IDENTITY NOT NULL CHECK (RECID <> 0),RECVERSION INT NOT NULL DEFAULT 1) WITH (DATA_COMPRESSION = PAGE);

---> System.Data.SqlClient.SqlException: Reference to database and/or server name in 'TEMPDB.DBO.T_TRVREQUISITIONLINE_89D1D5091B9D4913861F8296525E9983' is not supported in this version of SQL Server.
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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.SqlCommandExtensions.<>c__DisplayClass1.<ExecuteNonQueryWithRetry>b__0()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.<ExecuteNonQuery>b__27_0(SqlCommand c)
at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.ExecuteSql[T](SqlCommand cmd, Func`2 sqlFunc)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.HandleException(ExceptionDispatchInfo edi, SqlExecutionInfo execInfo)
at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.ExecuteSql[T](SqlCommand cmd, Func`2 sqlFunc)
at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteDDLWithNewTransaction(IEnumerable`1 commandTextInBatch)
--- End of inner exception stack trace ---
at Microsoft.Dynamics.AX.Data.Management.DBExecute.ThrowOnSqlExecuteException(SqlException e, String commandText)
at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteDDLWithNewTransaction(IEnumerable`1 commandTextInBatch)
at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteSqlBatch(TableSyncSqlBatch sqlBatch)
at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.CreateTempDBTable(AxTable axTable, String runTimeTableName)
at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.CreateTempDBTable(AxTable axTable, String runTimeTableName)
at Microsoft.Dynamics.Ax.Xpp.DataAccess.Impl.TempDbTableDataAccess.CreateTempTableInDatabase(String tableName, String tempTableName, IMetadataProvider metadataProvider)
at Microsoft.Dynamics.Ax.Xpp.DataAccess.Impl.TempDbPoolManager.Lease(Int32 tableId, String axTableName, IMetadataProvider metadataProvider)
at Microsoft.Dynamics.Ax.Xpp.ManagedCommonImplementation.LeaseTempTable()
at Microsoft.Dynamics.Ax.Xpp.DataAccess.Impl.SqlTempDbDataSource.CreateTempDbTable()
at Microsoft.Dynamics.Ax.Xpp.DataAccess.Core.DataSourceProvider.NewDataSource(Common common)
at Microsoft.Dynamics.Ax.Xpp.ManagedCommonImplementation.get_DataSource()
at Dynamics.AX.Application.ManagedQueryRunImplementation.nextInternal()
at Dynamics.AX.Application.ManagedQueryRunImplementation.next()
at Dynamics.AX.Application.ManagedQueryRunImplementation.GenerateOnlyRunToNext(ManagedQueryImplementation query)
at Dynamics.AX.Application.ManagedQueryRunImplementation.GetSQLSelectQuery(ManagedQueryImplementation query)
at Microsoft.Dynamics.AX.Data.Management.ManagedSyncDDLBuilder`1.GenerateStatement(T syncObject, Func`2 selectQueryGenerator)
at Microsoft.Dynamics.AX.Data.Management.DataEntity.ManagedSyncDataEntityWorker.DoSyncAll[T](IEnumerable`1 sortedViewList, Dictionary`2 oldViewDefinitions)
at Microsoft.Dynamics.AX.Data.Management.View.ManagedSyncViewWorker.SyncAll()
at Microsoft.Dynamics.AX.Data.Management.ManagedSync.<SyncAll>b__18_0()
at Microsoft.Dynamics.AX.Data.Management.ManagedSync.EnsureMetadataProviderCacheCleared(Action action)
at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.RunFullManagedSync(SyncOptions options, String sqlConnectionString, IMetadataProvider metadataProvider)
at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.<>c__DisplayClass15_0.<RunFullTableSync>b__0()
at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.ExecuteWithinAOS(SyncOptions syncOptions, String sqlConnectionString, IMetadataProvider metadataProvider, Func`1 func, Action`1 errorHandler).
at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.<>c.<RunFullTableSync>b__15_1(Tuple`2 result)
at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.ExecuteWithinAOS(SyncOptions syncOptions, String sqlConnectionString, IMetadataProvider metadataProvider, Func`1 func, Action`1 errorHandler)
at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.RunFullTableSync(SyncOptions syncOptions, String sqlConnectionString, IMetadataProvider metadataProvider)
at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.FullTableViewSync()
at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.FullSync()
at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.RunSync(SyncOptions options)
at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.Run(String metadataDirectory, String sqlConnectionString, SyncOptions options)
10/02/2017 10:17:00: The operation failed.
10/02/2017 10:14:25: Running in syncmode.
Syncmode: FullAll
Bindir: F:\AosService\PackagesLocalDirectory
MetadataDir: F:\AosService\PackagesLocalDirectory

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    Restart your IIS server and rety synchronization

    type in CMD:

    1-  iisreset /stop

    2-  iisreset /start

  • Community Member Profile Picture
    Community Member on at

    Hi Chen,

    Did you find a solution? I have the exactly same problem :-(
    A IISreset did not help

  • Community Member Profile Picture
    Community Member on at

    Hi Chen,

    Even I have the same prob.. IISRESET or machine restart did not help..

    Did get a solution for this! Could you please let us know if u get

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Hello everybody got the solution for this issue.........

    Run the below commands on Azure DB. It worked for me

    update sysglobalconfiguration

    set value = 'SQLAZURE'

    where name = 'BACKENDDB'

    update sysglobalconfiguration

    set value = 1

    where name = 'TEMPTABLEINAXDB'

  • Community Member Profile Picture
    Community Member on at

    Setting user axdeployuser on sql as owner of tempdb solve my problem,  but maybe the solution of Bhasker is the correct one.

  • Enc@ Profile Picture
    Enc@ 10 on at

    The issue is AX is trying to sync a tempdb table into the ax db instead of the temp_db, as it is in azure on all uat boxes, whereas in the dev boxes, it's in tempdb.

     CREATE TABLE TEMPDB.DBO.T_TRVREQUISITIONLINE_89D1D5091B9D4913861F8296525E9983

    But the create table statement fails as sqladmin (running dbsync), because it’s still referring to the old tempdb, see the 3rd word in the sync statement.

    So the SQL error text is actually quite helpful:

    Reference to database and/or server name in 'TEMPDB.DBO.T_TRVREQUISITIONLINE_89D1D5091B9D4913861F8296525E9983' is not supported in this version of SQL Server.

    First of all: This version of SQL Server: means you're on Azure SQL this time.

    Second: Reference to database and/or server name: means you're adressing tempdb .dbo.T_yyyyyyyyyy instead of axdb_xxxxxxxxxx.dbo.T_yyyyyyyyyyyyy

    So the advice to check for the following statement is correct.

    update sysglobalconfiguration set value = 'SQLAZURE' where name = 'BACKENDDB'
    update sysglobalconfiguration set value = 1 where name = 'TEMPTABLEINAXDB'

    In our case, it was correct too.

    In our case, after that, restart IIS, app pool was stopped and needed restart,

    resync, no errors, 

    Re-Restart IIS, Bingo

  • Brian Kinser Profile Picture
    Brian Kinser 815 on at

    This worked for me as well, interesting to note that this is SANDBOX and data refresh / restore was from PROD > SANDBOX, therefore done by Microsoft. Thanks

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans