External file format and external data source changes in the project generate incorrect script #120
Description
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:
- Project contains:
CREATE EXTERNAL FILE FORMAT [txt] WITH (
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS (FIELD_TERMINATOR = N'|', FIRST_ROW = 1, ENCODING = N'UTF8')
);- 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')
);- 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- 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.