External file format and external data source changes in the project generate incorrect script

[zijchen]

Originally submitted as TFS Defect 13100784 by nadebow

The issue was reported by SQL DW customer, but the same issue was repro'ed in SQL DB (SQL DB does not support external file formats, but it supports external data sources and the same issue exists with external data sources) and SQL Server on-prem (external file format and external data source have this issue for on-prem).

More details can be found in VSO - Bug 404161 (External File Format changes generates incorrect script)

SQL DW repro scenario:

1. Project contains:

   CREATE EXTERNAL FILE FORMAT [txt] WITH (
   FORMAT_TYPE = DELIMITEDTEXT,
   FORMAT_OPTIONS (FIELD_TERMINATOR = N'|', FIRST_ROW = 1, ENCODING = N'UTF8')
   );

2. Update script to FIRST_ROW = 2 and save it.

   CREATE EXTERNAL FILE FORMAT [txt] WITH (
   FORMAT_TYPE = DELIMITEDTEXT,
   FORMAT_OPTIONS (FIELD_TERMINATOR = N'|', FIRST_ROW = 2, ENCODING = N'UTF8')
   );

3. Execute Schema Compare of the Project and Target database that was used to generate the project. Use Generate Script option to generate the update script:

   
   /*

Deployment script for nadebowdw01

This code was generated by a tool.

Changes to this file may cause incorrect behavior and will be lost if

the code is regenerated.

*/

SET

ANSI_NULLS ON; SET

ANSI_PADDING ON; SET

ANSI_WARNINGS ON; SET

ARITHABORT ON; SET

CONCAT_NULL_YIELDS_NULL ON; SET

QUOTED_IDENTIFIER ON; SET

NUMERIC_ROUNDABORT OFF; 　

GO

:setvar DatabaseName "nadebowdw01"

:setvar DefaultFilePrefix "nadebowdw01"

:setvar DefaultDataPath ""

:setvar DefaultLogPath ""

GO

:on error exit

GO

:setvar __IsSqlCmdEnabled "True"

GO

IF

N'$(__IsSqlCmdEnabled)' NOT LIKE N'True' BEGIN PRINT N'SQLCMD mode must be enabled to successfully execute this script.'; END 　

GO

PRINT

N'Dropping [txt]...'; 　

GO

DROP

EXTERNAL FILE FORMAT [txt]; 　

GO

PRINT

N'Creating [txt]...'; 　

GO

CREATE

EXTERNAL FILE FORMAT [txt] WITH ( FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS (FIELD_TERMINATOR = N'|', FIRST_ROW = 2, ENCODING = N'UTF8') ); 　

GO

PRINT

N'Update complete.'; 　

GO

4. Execute generated script results in error.

Msg 33165, Level 16, State 1, Line 46

Cannot drop the external file format 'txt' because it is used by an external table.

** An error was encountered during execution of batch. Exiting.

[dzsquared]

@SeenaAugusty - I moved this out as we close out the 161.8089.0 release

[toby-freemarket]

Does that mean this is released?

[dzsquared]

@toby-freemarket this was not completed in the February release, but it is currently targeted for the next release