Exporting Dynamics NAV Database on SQL Server 2016: the How-To

Before you run anything through SQL Server 2016 for your Dynamics NAV database, make sure you update everything.

Before you export a database to the .bacpac format, if you are using the SQL Server 2016 for your Dynamics NAV database, you need to make sure you are running the latest update from August 2016 or later. You should check with your Microsoft SQL Server Data-Tier Application Framework (DacFx) or SQL Server Management Studio (SSMS). If you don’t you run the risk of exposing your system to a bug in the tools around SQL Server that Microsoft has located.

The Error Message

During one of their tests, Microsoft found out that the Dynamics NAV 2017 demo database couldn’t be exported to a .bacpac file.

"c:Program FilesMicrosoft SQL Server130DACbinSqlPackage.exe" /Action:Export /TargetFile:nav-test.bacpac /SourceServerName:10.0.0.10 /SourceDatabaseName:"Demo Database NAV (10-0)" /SourceUser:sa /SourcePassword:blablabla
 Connecting to database 'Demo Database NAV (10-0)' on server '10.0.0.10'.
 Extracting schema
 Extracting schema from database
 Resolving references in schema model
 Validating schema model
 Validating schema model for data package
 Validating schema
 *** Error exporting database:One or more unsupported elements were found in the schema used as part of a data package.
 Error SQL71564: View: [dbo].[CRONUS International Ltd_$Service Shipment Buffer$VSIFT$0] contains a statement that is not supported on Microsoft Azure SQL Database v12. The specific error is: Incorrect syntax near "CRONUS International Ltd_$Service Shipment Buffer$VSIFT$0".
 Error SQL71564: View: [dbo].[CRONUS International Ltd_$Calendar Entry$VSIFT$0] contains a statement that is not supported on Microsoft Azure SQL Database v12. The specific error is: Incorrect syntax near "CRONUS International Ltd_$Calendar Entry$VSIFT$0".
 ...

They found that the view that had the erros in code was used for a SIFT index. They were not sure where exactly the problem was in the code but they had a hypothesis that it might be hidden in the .bacpac report. They then tested it by creating an empty database with a table and a view using the same syntax that was found in the view with the error.

IF OBJECT_ID(N'id_v', N'V') IS NOT NULL
DROP VIEW id_v
GO
DROP TABLE IF EXISTS id
GO 
CREATE TABLE id(n int)
GO 

CREATE VIEW id_v AS
SELECT "abc".n FROM dbo.id "abc"
GO

But this export still triggered the error.

"c:Program FilesMicrosoft SQL Server130DACbinSqlPackage.exe" /Action:Export /TargetFile:nav-test.bacpac /SourceServerName:10.0.0.10 /SourceDatabaseName:dacpac-test /SourceUser:sa /SourcePassword:blablabla
Connecting to database 'dacpac-test' on server '10.0.0.10'.
Extracting schema
Extracting schema from database
Resolving references in schema model
Validating schema model
Validating schema model for data package
Validating schema
*** Error exporting database:One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71564: View: [dbo].[id2_v] contains a statement that is not supported on Microsoft Azure SQL Database v12. The specific error is: Incorrect syntax near "abc".

The error message indicated that the sqlpackage had a problem with quoted identifiers and Microsoft immediately contacted the SQL Server Team to report a bug. The team confirmed that this was, in fact, a bug and that it had been fixed in the July update of DacFx.

If you haven’t already, you need to install the update. You can run this to get the latest update:

Register-PackageSource -Name NuGet -Location https://www.nuget.org/api/v2 -Provider NuGet -Verbose
Install-Package -Name Microsoft.SqlServer.DacFx.x64 -MinimumVersion 130.3485.1 -ProviderName NuGet -Force

At Sensible Systems, LLC., we are always here to help. For more information about this and other issues you may be having, be sure to contact us in Littleton by calling us at (877) 773-6797 or emailing us at {email}.