Build failing with referenced object during cleaning of database

BlueBSH

This is my first attempt to move our existing DB into using SQL Change automation with Azure DevOps.

I set up my pipeline and set up a build SQL Change automation task for my project. When I run it though when it goes to clean my integration database I always error out with this error.

##[warning]The error 'Cannot ALTER 'Info.frnNormalizeText' because it is being referenced by object 'AssignedIDNumbers'.' occurred when executing the following SQL:

when ends with

##[error]Failed to clean the database 'OurSystem_Integration' on server '.\'.

the table has a computed column that uses that normalize function to store a computed version of a text string in another column.

How do I handle something like this in change automation? I was thinking pre-deploy scripts but I haven't had any luck with that either.

Kendra_Little

Hi @blueBSH,

I am guessing this is a build for a SQL Source Control project in SQL Change Automation.

I suspect that the way to solve it this is to filter the function and handle it in a pre-deployment script, but it's hard for me to say without seeing the exact schema of the objects involved. 

If you'd prefer to share the schema in a private way / not in a public forum, I'd encourage you to start up a support ticket so we can help with more detail.

Kendra

Tianjiao_Li

Thanks @Kendra_Little

We've got a support ticket for @BlueBSH and I'll reach out by email shortly.