Error: referencing a Programmable Object in a Migration script

ducman

This causes an error when verifying/refreshing with the shadow database since there's a sequence issue.

Example:

1. Deploy migration script which calls a stored procedure
2. Deploy programmable object (the stored procedure that the above is referencing)

How does one get around this?

way0utwest

Sorry, this process sometimes is a bit of a chicken and egg, going around in circles when trying to explain the process. In case someone else is following, here's what I recommend.

Let's say I have a procedure that I wanted created called dbo.SetDateforClass. This looks like this in my programmable object:

CREATE OR ALTER dbo.SetDateforClass<br>&nbsp; @ClassID int<br>, @dt date<br>as&nbsp;<br>begin<br>update Class<br>&nbsp;set StartDate = @DTs <br>where ClassId = @ClassID<br>end

I add this to my database and then add a migration script that looks like this.

<!-- migrationid = xxxx --><br>exec dbo.SetDateforClass 1, getdate()<br>exec dbo.SetDateforClass 2, getdate()

Now, when I refresh the project, this compares to the shadow database. The migration scripts execute first, then the programmable objects. In this case, I'll get an error as the proc doesn't exist in Shadow yet, so the script fails. This worked in the main development database since the chronology was correct in how I built the objects.

To fix this, I recommend this: change the migration script to:

<!-- migrationid = xxxx --><br>if not exists( select name from sys.objects where name = 'SetDateforClass')<br>&nbsp; begin<br>CREATE OR ALTER dbo.SetDateforClass<br>&nbsp; @ClassID int<br>, @dt date<br>as&nbsp;<br>begin<br>update Class<br>&nbsp;set StartDate = @DTs <br>where ClassId = @ClassID<br>end<br>goexec dbo.SetDateforClass 1, getdate()<br>exec dbo.SetDateforClass 2, getdate()

This will deploy the procedure as part of the migration script and then execute it. Later, the programmable object will overwrite this procedure with the same definition and the compare with the Shadow db for validity, as well as a build or downstream deploy, will work.

If the procedure gets updated with a new parameter or different logic, that will be deployed, but this migration won't run again. However, if a new developer sets up from scratch, when they load the project and pick databases, this migration script will work (likely no data, so no effect) and then the updated procedure definition will be deployed as a part of the programmable object deployment.

way0utwest

I'm assuming your migration script uses the procedure to do some work? I'd that right?

What is say here is you can really only do one thing and thats include the proc crrate in the migration script . It will also be in the programmable object and that should just ovewrite with the version stored there. I'd do this in an idempotent way so that in future deployments if the proc exists, we don't try to recreate it .Not create it alter but an if exists(check sysobjects). 

Toss isn't great but it does solve the issue here. In the future, this also ensures builds work from scratch . Even if the proc changes, the valid version of the proc that works with this migration script works. 

I think if you deoy the proc first, to both shadow sha downstream environments, then add the script this may work, but if have to test.  However, any deployments to a new system, like a new developer setup, would fail so I'd rather just inline the proc in the migration script, knowing that all future changes are handled as a part of the programmable object

ducman

It was a bit difficult trying to understand but I believe I got what you're trying to say. I thought through it and other ways as well and it's best to include the stored procedure within the migration script (doing an exists check is nice as well).