·
3 min read

How to export a Dynamics NAV database on SQL Server 2016

If you use SQL Server 2016 for your Dynamics NAV database, and you need to export a database to the .bacpac format, then please make sure that you are running the latest update (August 2016 update or later) of the Microsoft SQL Server Data-Tier Application Framework (DacFx) or SQL Server Management Studio (SSMS).

If you are interested in why we recommend this, here is the story of how we found a bug in Dynamics NAV, analyzed the impact on customers and partners, and then finally found out that it wasn’t a bug in Dynamics NAV, but in the tools around SQL Server. And the story even has a happy ending. And some Windows PowerShell bonus info 🙂

During a manual test outside our test lab, we recently discovered that the Dynamics NAV 2017 demo database couldn’t be exported to a .bacpac file:

"c:\Program Files\Microsoft SQL Server\130\DAC\bin\SqlPackage.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".
 ...

By zooming in on one of the error rows, such as:

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".

we saw that the view which contained the errors was used for a SIFT index.
Querying the view from SSMS gave no errors. This indicated that from the point of view (pun intended!) of SQL Server and therefore also of Dynamics NAV, the view was healthy and SIFT indexes were working in Dynamics NAV.

And maybe the problem might be hidden in the .bacpac export. To test this hypothesis, we created an empty database with one table and one view by using the same syntax as the view that was giving us trouble:

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

Exporting this database by using sqlpackage still triggered the error:

"c:\Program Files\Microsoft SQL Server\130\DAC\bin\SqlPackage.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".

It seemed like sqlpackage had a problem with quoted identifiers. We contacted the SQL Server team responsible for DacFx to file a bug, and they immediately confirmed that:

  1. this was a bug, and
  2. it had been fixed in the July update of DacFx: https://blogs.msdn.microsoft.com/ssdt/2016/06/30/sql-server-data-tools-july-update-2/ (actually, it didn’t make it into the July version, but in the August update.)

So, as stated in the introduction, you should use the DacFx msi installer from the August update (or later).

Bonus info:
To make sure that your environment is always updated with the latest version of DacFx, try using the PowerShell package manager PowerShellGet at:
https://msdn.microsoft.com/en-us/powershell/reference/5.1/powershellget/powershellget

Then, from today and going forward, you can run this to get and install the latest version of DacFx:

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
Kennie Nybo Pontoppidan

Related posts