D365FO: DBSync error 'The SQL view definition for your_view_name is deployed as a code package but has not been synchronized with the database'

 I got the following error today while doing the full DBSync:

The SQL view definition for your_view_name is deployed as a code package but has not been synchronized with the database. As it is currently used by one or more financial dimensions, the database must be synchronized to the code deployment.

The root cause of the issue was described in this Yammer thread by Denis Fedotenko a few years ago. Quote from the thread:

It looks like the problem is caused by the following code in the method DimensionEnabledType::getDimensionEnabledTypeCollection()
select firstonly RecId from sqlDictionary where sqlDictionary.name == any2Str(viewName);

The standard logic is trying to find whatever the view, to which the dimension definition refers, actually exists in the system. The problem is with the expression on the right side of the comparison in the select statement. Since it is not a variable with a defined length, the system cannot properly compile the statement; it creates a bunch of similar SQL Statements with different declared lengths of the only parameter. After execution of the code, my SQL Statement cache was thrashed with similar SQL Statements, which differs only in the string length in the parameter declaration, like:

(@P1 nvarchar(34))SELECT TOP 1  T1.RECID, T1.RECID FROM SQLDICTIONARY T1 WHERE( T1.NAME  =  @P1)

(@P1 nvarchar(25))SELECT TOP 1  T1.RECID, T1.RECID FROM SQLDICTIONARY T1 WHERE( T1.NAME  =  @P1)

(@P1 nvarchar(40))SELECT TOP 1  T1.RECID, T1.RECID FROM SQLDICTIONARY T1 WHERE( T1.NAME  =  @P1)

The problem is, that sometimes the length of the parameter declaration (like nvarchar(40)) does not match the length of the real string, so the passed parameter value is cut like:

<ColumnReference Column="@P1" ParameterDataType="nvarchar(40)" ParameterCompiledValue="N'BOSDimAttributesmmBusRelSalesDistrictGro'" />

The real name of the view is indeed BOSDimAttributesmmBusRelSalesDistrictGroup

I am not 100% sure now how to reproduce the error in  clean-room conditions, but I suspect that if the view name length is larger than 40 characters (or maybe if  it is larger than 40 chars and is equal to some magic numbers, on which the compiler chokes), the sync fails.

The solution depends on the length of your query name:

• if query name is too long - rename it and make it shorter
• if query name is not so long - just retry full DBSync, eventually it will succeed