You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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:
CREATEEXTERNALFILEFORMAT [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.
CREATEEXTERNALFILEFORMAT [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 nadebowdw01This code was generated by a tool.Changes to this file may cause incorrect behavior and will be lost ifthe code is regenerated.*/SETANSI_NULLSON;
SETANSI_PADDINGON;
SETANSI_WARNINGSON;
SETARITHABORTON;
SETCONCAT_NULL_YIELDS_NULLON;
SETQUOTED_IDENTIFIERON;
SETNUMERIC_ROUNDABORTOFF;
GO
:setvar DatabaseName "nadebowdw01"
:setvar DefaultFilePrefix "nadebowdw01"
:setvar DefaultDataPath ""
:setvar DefaultLogPath ""GO
:on error exit
GO
:setvar __IsSqlCmdEnabled "True"GOIFN'$(__IsSqlCmdEnabled)'NOTLIKEN'True'BEGINPRINTN'SQLCMD mode must be enabled to successfully execute this script.';
ENDGOPRINTN'Dropping [txt]...';
GODROPEXTERNALFILEFORMAT [txt];
GOPRINTN'Creating [txt]...';
GOCREATEEXTERNALFILEFORMAT [txt]
WITH (
FORMAT_TYPE=DELIMITEDTEXT,
FORMAT_OPTIONS (FIELD_TERMINATOR=N'|', FIRST_ROW=2, ENCODING=N'UTF8')
);
GOPRINTN'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.
The text was updated successfully, but these errors were encountered:
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:
The text was updated successfully, but these errors were encountered: