Are You Getting More & More Frustrated With The Lack Of Response From Your NYC IT Services Company?
Book Your Free Onsite Consultation
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 {company}, we are always here to help. For more information about this and other issues you may be having, be sure to contact us in {city} by calling us at {phone} or emailing us at {email}.