Calling a new SP/function from migration script.

catalogdb

Hi,

I have a need to call newly added stored procedure from the migration scripts.
Issue#1
The issue is SCA is failing during deployment when the migration script is run because the new stored procedure isn't available yet. (I understand SCA deployes migration scripts first before recreating the programmable objects).

I have overcome this issue by adding the SP code in the migration scripts. This however created another issue.
Issue#2
My SP had explicit transaction blocks. SCA don't like them so It failed again. I overcome this by commenting out BEGIN TRANS and COMMIT TRANS lines.

Luckily I had to edit only two simple sps which didn't have big impact.
Issue#3
I am concerned what if i have to call many SPs and they have explicit transactions. Its need additional effort and there is lot of chances of adding errors in the migration scripts which I need to edit just for the sake of passing through deployment.

We have found an alternative way to deal this situtation but kills lot of time. Which is, by doing multiple deployments. First, we release newly created objects and next we release migration scripts with calls to the new objects.

I hope there is some easy way to handle this situation where I can simply call the newly created programmable objects from the migration script.

Sergio R

The alternative would be to disable the default option of using Programmable Objects. This would cause the Programmable Objects to be scripted alongside the other objects in the migration scripts (rather than in the separate Programmable Objects scripts which are deployed after the migration scripts).

https://documentation.red-gate.com/sca/developing-databases/concepts/migrations/programmable-objects

Sergio R

Hi,

Scripting the Programmable Object in the migration script is the correct solution.

Regarding the transaction blocks issue you can disable automatic transaction handling for a script by following the instructions in the following article:
https://documentation.red-gate.com/sca/developing-databases/concepts/advanced-concepts/transaction-handling

catalogdb

Thanks for the reply, Sergio. I am wondering if there is alternate so I don't have to copy the script in migration script. Its definitely an additional task that I like to avoid if its possible.