What are the challenges you face when working across database platforms? Take the survey

Calling a new SP/function from migration script.


I have a need to call newly added stored procedure from the migration scripts.
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.
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.
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.

Best Answer


Sign In or Register to comment.