Need Computer Services? We Can Help! Call: (317) 585-0500

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

Time To Save Big Money With Your Business IT Solutions?

Seperator

Need Someone Reliable To Fix Your Computer Troubles?

Service Award
Top Imgs

You Got The Right Company To Help.

Reach Out To Our Team Today.

Spam

PCHelp protects your privacy. We will ensure your confidentiality.

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.

SQL DATABASE

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 PC Help Services, we are always here to help. For more information about this and other issues you may be having, be sure to contact us in Indianapolis by calling us at (317) 585-0500 or emailing us at info@pchservices.com.

Alexssa

Absolutely amazing. Matthew came to the office and worked with the laptop I had tried to fix myself. He listened carefully to what I reported, then worked with it for over an hour. He asked if he could take it back to his office to work with it. I agreed on the basis of a two-hour billable cap. He took it back to his shop, I’m sure spent way more than two hours fixing it, and returned to me a laptop I scarcely recognized–faster, working wireless, updated Cisco VPN software–perfection. In the course of his magic, he was always available to discuss the configuration, asked all the right questions and gave the right answers. He met the deadline I imposed. The service was so impressive that I asked about future availability to deal with a small business network if help was needed. There’s a team of folks ready to help.”

Michael Griffith

Connect With Your Computer Support Team

  • 2810 E 116th St Ste 160 Carmel, IN 46033
  • Weekdays 8AM-5PM
    Saturday 10AM-2PM
    Sunday Closed
  • 317.585_.0500