search

microsoft / DACExtensions

DACExtensions contains samples that extend Data-Tier Applications using DacFx. These samples include deployment contributors and static code analysis rules that can be used with Visual Studio as well as examples of how to use the DacFx public mode
MIT License
125 stars 41 forks source link

sqlpackage publish fails to create views in correct order (view on view) #45

Closed richard-browne closed 3 years ago

richard-browne commented 4 years ago

My database has 'view1', then another 'view2' that selects from view1. sqlpackage publish fails with an error like:

Error SQL72914: Core Microsoft SqlClient Data Provider: Msg 208, Level 16, State 1, Procedure View2, Line 24 Invalid object name 'MyDatabase.dbo.View1'.

It is attempting to create View2 before View1 exists.

Is this a known issue? In model.xml it looks like the 'sqlpackage export' has written the correct dependencies.

Is it expected that sqlpackage publish should be smart enough to figure out dependencies?

ErikEJ commented 4 years ago

Yes, it is expected - please share a .dacpac or a sample schema, then I can have a look.

richard-browne commented 4 years ago

Hi I'm just getting back to this after 3 months. If I provide a .dacpac is someone still around to take a look?

richard-browne commented 4 years ago

If it helps anybody else in the future I figure out my problem. The view in my database was defined like this:

CREATE VIEW dbo.MyView2 AS SELECT FROM dbo.MyTable GO CREATE VIEW dbo.MyView1 AS SELECT FROM MyDatabase.dbo.MyView2 GO

Notice MyView1 is selecting on an explicit database causing sqlpackage to skip dependency checking I assume. Even if I published back to a database with the same name 'MyDatabase' it will still try to restore MyView1 before MyView2.

Understandable behaviour and an easy fix. Still it would be great if sqlpackage could be smart enough to realise that 'MyDatabase' is the one being restored and handle the dependency.