Publish to SQL Azure fails with 'Cannot drop the external data source' message

Question

I've got an SQL Project (.sqlproj) in my solution with target platform 'Microsoft Azure SQL Database V12'.

Recently I've added an external data source and several external tables targeting this data source.

ExternalCSVLists.sql file:

CREATE EXTERNAL DATA SOURCE [ExternalCSVLists] WITH
(  
    TYPE = RDBMS,
    LOCATION = 'location.windows.net',
    DATABASE_NAME = '$(CSVLists)',
    CREDENTIAL = RemoteConnectionCredential
)

Example of external table (IntegerListContent.sql file)

CREATE EXTERNAL TABLE [WebApp].[IntegerListContent] 
(  
     [ListId] INT,
     [Value] int 
)
WITH  
(  
    DATA_SOURCE = [ExternalCSVLists]
)

First time publish went OK.

Now, when I publish again ( not having any changes done to either of external tables or data-sources), I receive the following error:

Dropping [ExternalCSVLists]... (415,1): SQL72014: .Net SqlClient Data Provider: Msg 33165, Level 16, State 1, Line 1 Cannot drop the external data source 'ExternalCSVLists' because it is used by an external table.

I've inspected the publish script and noticed that it attempt to drop-and-create the external data source. The external tables are skipped ( which is probably OK since I didn't change them ).

So,

1) why does it yield a drop external data source statement when all such data sources are identical to those already published

2) why does it ignore the dependent external tables then?

My publish settings are pretty much default (none of the options in 'Drop' tab are checked ). Thanks!

score 3 · Answer 1 · answered Feb 03 '21 at 21:16

I adapted the approaches above and have this in place in my DevOps release pipeline:

Run SQL script to drop all external objects in the target database, using an Azure SQL Database deployment task. This prevents the "unable to drop object" errors that can happen in a plain deployment.

declare @sql as nvarchar(max) 
; with cmds as (
select 
    CONCAT('drop external table ' ,QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) , '.' , QUOTENAME(OBJECT_NAME(t.object_id))) as Cmd
from sys.external_tables t
union all 
select 
    'drop external data source ' + QUOTENAME(s.name)
from sys.external_data_sources s
) 
select @sql= STRING_AGG(cmd,';')
from cmds 
print @sql

if(len(@sql) > 0)
begin
exec sp_executesql @sql
end

Use an Azure SQL Database Deployment task to do a database deployment using a publish.xml profile that ignores all object types except external objects. This will recreate all the objects that were dropped in the previous step, but with the correct definitions.

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName></TargetDatabaseName>
    <DeployScriptFileName></DeployScriptFileName>
    <BlockOnPossibleDataLoss>False</BlockOnPossibleDataLoss>
    <GenerateSmartDefaults>True</GenerateSmartDefaults>
    <DropObjectsNotInSource>True</DropObjectsNotInSource>
    <DoNotDropPermissions>True</DoNotDropPermissions>
    <DoNotDropRoleMembership>True</DoNotDropRoleMembership>
    <DoNotDropUsers>True</DoNotDropUsers>
    <IgnoreColumnOrder>True</IgnoreColumnOrder>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <ExcludeAggregates>True</ExcludeAggregates>
    <ExcludeApplicationRoles>True</ExcludeApplicationRoles>
    <ExcludeAssemblies>True</ExcludeAssemblies>
    <ExcludeAsymmetricKeys>True</ExcludeAsymmetricKeys>
    <ExcludeAudits>True</ExcludeAudits>
    <ExcludeBrokerPriorities>True</ExcludeBrokerPriorities>
    <ExcludeCertificates>True</ExcludeCertificates>
    <ExcludeClrUserDefinedTypes>True</ExcludeClrUserDefinedTypes>
    <ExcludeColumnEncryptionKeys>True</ExcludeColumnEncryptionKeys>
    <ExcludeColumnMasterKeys>True</ExcludeColumnMasterKeys>
    <ExcludeContracts>True</ExcludeContracts>
    <ExcludeCryptographicProviders>True</ExcludeCryptographicProviders>
    <ExcludeDatabaseAuditSpecifications>True</ExcludeDatabaseAuditSpecifications>
    <ExcludeDatabaseRoles>True</ExcludeDatabaseRoles>
    <ExcludeDatabaseTriggers>True</ExcludeDatabaseTriggers>
    <ExcludeDefaults>True</ExcludeDefaults>
    <ExcludeEndpoints>True</ExcludeEndpoints>
    <ExcludeErrorMessages>True</ExcludeErrorMessages>
    <ExcludeEventNotifications>True</ExcludeEventNotifications>
    <ExcludeEventSessions>True</ExcludeEventSessions>
    <IgnoreExtendedProperties>True</IgnoreExtendedProperties>
    <ExcludeExternalDataSources>False</ExcludeExternalDataSources>
    <ExcludeFileTables>True</ExcludeFileTables>
    <ExcludeFilegroups>True</ExcludeFilegroups>
    <ExcludeFullTextCatalogs>True</ExcludeFullTextCatalogs>
    <ExcludeFullTextStoplists>True</ExcludeFullTextStoplists>
    <ExcludeLinkedServerLogins>True</ExcludeLinkedServerLogins>
    <ExcludeLinkedServers>True</ExcludeLinkedServers>
    <ExcludeLogins>True</ExcludeLogins>
    <ExcludePartitionFunctions>True</ExcludePartitionFunctions>
    <ExcludeMessageTypes>True</ExcludeMessageTypes>
    <ExcludePartitionSchemes>True</ExcludePartitionSchemes>
    <IgnorePermissions>True</IgnorePermissions>
    <ExcludeQueues>True</ExcludeQueues>
    <ExcludeRemoteServiceBindings>True</ExcludeRemoteServiceBindings>
    <IgnoreRoleMembership>True</IgnoreRoleMembership>
    <ExcludeRoutes>True</ExcludeRoutes>
    <ExcludeRules>True</ExcludeRules>
    <ExcludeScalarValuedFunctions>True</ExcludeScalarValuedFunctions>
    <ExcludeSearchPropertyLists>True</ExcludeSearchPropertyLists>
    <ExcludeSecurityPolicies>True</ExcludeSecurityPolicies>
    <ExcludeSequences>True</ExcludeSequences>
    <ExcludeServerAuditSpecifications>True</ExcludeServerAuditSpecifications>
    <ExcludeServerRoleMembership>True</ExcludeServerRoleMembership>
    <ExcludeServerRoles>True</ExcludeServerRoles>
    <ExcludeServerTriggers>True</ExcludeServerTriggers>
    <ExcludeServices>True</ExcludeServices>
    <ExcludeSignatures>True</ExcludeSignatures>
    <ExcludeStoredProcedures>True</ExcludeStoredProcedures>
    <ExcludeSymmetricKeys>True</ExcludeSymmetricKeys>
    <ExcludeSynonyms>True</ExcludeSynonyms>
    <ExcludeTableValuedFunctions>True</ExcludeTableValuedFunctions>
    <ExcludeTables>True</ExcludeTables>
    <ExcludeUserDefinedDataTypes>True</ExcludeUserDefinedDataTypes>
    <ExcludeUserDefinedTableTypes>True</ExcludeUserDefinedTableTypes>
    <ExcludeUsers>True</ExcludeUsers>
    <ExcludeViews>True</ExcludeViews>
    <ExcludeXmlSchemaCollections>True</ExcludeXmlSchemaCollections>
    <ExcludeCredentials>True</ExcludeCredentials>
  </PropertyGroup>
</Project>

Use another Azure SQL Database Deployment task to do a database deployment using a publish.xml profile that ignores all external object types.

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName></TargetDatabaseName>
    <DeployScriptFileName></DeployScriptFileName>
    <BlockOnPossibleDataLoss>False</BlockOnPossibleDataLoss>
    <GenerateSmartDefaults>True</GenerateSmartDefaults>
    <DropObjectsNotInSource>True</DropObjectsNotInSource>
    <DoNotDropPermissions>True</DoNotDropPermissions>
    <DoNotDropRoleMembership>True</DoNotDropRoleMembership>
    <DoNotDropUsers>True</DoNotDropUsers>
    <IgnoreColumnOrder>True</IgnoreColumnOrder>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <ExcludeCredentials>True</ExcludeCredentials>
    <ExcludeDatabaseScopedCredentials>True</ExcludeDatabaseScopedCredentials>
    <ExcludeExternalTables>True</ExcludeExternalTables>
    <ExcludeExternalFileFormats>True</ExcludeExternalFileFormats>
    <ExcludeExternalDataSources>True</ExcludeExternalDataSources>
  </PropertyGroup>
</Project>

Thanks for the workaround. I was trying to find how to declare dependencies withing a project but that doesn't seem to be a feature available, which is quite weird since there really are things that need to be created in order. I guess we would need yet another step creating database scoped credentials for greenfield deployments right? — Molotch, Mar 11 '22 at 13:21

score 2 · Answer 2 · answered Jun 19 '17 at 18:31

I hit the same issue; and unfortunately I could not figure out why this problem was created in the first place.

But the work around was to introduce a pre-deployment step that dropped ALL

Tables AND
Views

That referenced the External Data Source... I would have expected the DACPAC to perform this on its own; but I suppose we live in a non-perfect world.

score 1 · Answer 3 · edited Jul 11 '18 at 08:21

1

I have also this problem. As a workaround you can also use: /p:DoNotDropObjectTypes=ExternalDataSources

But it works only if you have no changes in your external data source.

I opened a feedback case on microsoft. If you like you can support the case.

edited Jul 11 '18 at 08:21

Mohammad Kanan

4,452
10
23
47

answered Jul 11 '18 at 07:59

Peter Horst

21
2

Publish to SQL Azure fails with 'Cannot drop the external data source' message

3 Answers3

Linked