How to use Views inside Migration scripts and avoid errors when calling Refresh later?

clopez

Hello,
I have a migration script that reads data from a view to populate a table.
The project deploys fine.
When later I hit Refresh to check if there are new changes to import, I get an error: the view doesn't exist.
The order in which the scripts execute on the Shadow DB is the same as on the main DB, so the migrations run before the programmable objects. That is why the Refresh fails: when the migration script that uses the view runs the view is not created yet.
I imagine this is a very common problem. What is the best approach here?
Thank you!

dnlnln

My apologies for the delay in responding.

The dependency issue that you describe is a known limitation in the way that the deployment order is handled between migrations and programmable objects: dependencies are only supported in one direction, from the programmable objects to the objects contained in the migrations.

To work around this limitation, there are two possible solutions:

• Update your baseline script (e.g. 001_Create_Objects.sql) to include the CREATE statements for your programmable objects, or
• Each time a programmable object is required as part of a migration, "uplift" the object (i.e. copy+paste the programmable object script contents) into the migration

The first option is easier as it is a once-off task that can be handled by the new project wizard, however if the view is regularly changing, then it may be necessary to follow the second approach to ensure that the definition is kept in-sync.

I hope this helps! Any questions please let me know.