Invalid object name when publishing: SQLPackage (DACFramework 15) Possible Bug

Question

I've been struggling with this issue for most of the morning and I'm ready to suggest this is a bug in SqlPackage.exe

I extract a dacpac using the following command:

C:\"Program Files"\"Microsoft SQL Server"\150\DAC\bin\SqlPackage.exe /a:Extract /ssn:$server /sdn:$dbName /st:300 /p:CommandTimeout=300 /tf:obj\$dbName\extracted.dacpac

And publish a script using:

C:\"Program Files"\"Microsoft SQL Server"\150\DAC\bin\SqlPackage.exe /a:Script /tcs:"Server=$server`;Database=$newDbName`;Trusted_Connection=True`;Connection Timeout=300`;" /p:CommandTimeout=300 /p:ExcludeObjectType=Logins /p:ExcludeObjectType=Users /p:ExcludeObjectType=RoleMembership /p:IgnoreNotForReplication=true /p:UnmodifiableObjectWarnings=false /sf:obj\$dbName\extracted.dacpac /op:obj\$dbName\publish_script.sql

The script generates but it fails when I try to execute it with the error:

Procedure MY_FUNCTION, Line 39 Invalid object name 'MY_OTHER_FUNCTION'

If I examine the script I can see the following:

LINE 300: PRINT N'Creating [dbo].[MY_FUNCTION]...'
... More code ...
LINE 400: PRINT N'Creating [dbo].[MY_OTHER_FUNCTION]...';

I've gone as far as digging into the extracted dacpac to confirm that the model.xml is picking up the dependency MY_FUNCTION has on MY_OTHER_FUNCTION. I have also verified that this isn't a case of a circular dependency. MY_OTHER_FUNCTION is dependent on one table that was created back on LINE 100.

Why is the generated script creating them out of order?

score 0 · Answer 1 · answered Feb 08 '19 at 21:15

Alright I'm certain this is a bug at this point. Steps to reproduce:

Create a new database.
Run the following creation scripts:

CREATE TABLE [dbo].[someTable]([Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL) ON [PRIMARY] GO

CREATE FUNCTION [dbo].[someOtherFunction](@Id INT = 1) RETURNS @someResults TABLE (Id INT) AS BEGIN INSERT INTO @someResults(Id) SELECT * FROM [sqlpackagebug].[dbo].[someTable] st WHERE @Id = st.Id RETURN; END GO

CREATE FUNCTION [dbo].[someFunction](@Id INT = 1) RETURNS TABLE AS RETURN ( SELECT * FROM [sqlpackagebug].[dbo].someOtherFunction ) GO

Extract the dacpac using:

C:\"Program Files"\"Microsoft SQL Server"\150\DAC\bin\SqlPackage.exe /a:Extract /ssn:$server /sdn:$dbName /st:300 /p:CommandTimeout=300 /tf:extracted.dacpac

Create a script using:

C:\"Program Files"\"Microsoft SQL Server"\150\DAC\bin\SqlPackage.exe /a:Script /tsn:$server /tdn:$newDbName /tt:300 /p:CommandTimeout=300 /sf:extracted.dacpac /op:script.sql

Look over the script and you can see that the functions are not created in the proper order. The script will fail.

Invalid object name when publishing: SQLPackage (DACFramework 15) Possible Bug

1 Answers1