modifying table schema and related stored proc in one migration script

jregan

I am missing something and could use a little help.

I have 2 migration scripts, that i need to consolidate into 1 script for ease of deploying through multiple environments.

My think problem boils down to this.

• I am adding a new column to table A
• I am also altering a view and stored procs that reference this new column in table A

I get errors of "Invalid column name 'newcolname'"

Is there any was to get these two updates together into the same script? ( while keeping one big transaction around the whole migration script, in case an error occurs?) I am successful when I keep them in 2 separate scripts, but due to our deployment system, we'd like to push out one migration script as we push them into higher environments

thanks

Mike U

I'm not sure why you're using a migration script for this case - you should just be able to check in your changes as normal, because adding a column and altering related views/stored procs shouldn't result in data loss.

jregan

sorry, I missed a (very important!) point in my description when I tried to boil down my problem.
There are some tables that have columns dropped as well. ( we redesigned some of our lookup tables, so the structure of a several tables and views has changed and some data is being moved between them)

This was the only solution that I got to work, and i was wondering if there was a better solution....
--this is wrapped up in 1 migration script--
step 1 : create a stored proc that scripts performs some of the table/view changes and moves some data between tables.
step 2: start a transaction,
call the stored proc (from step1),
continue to rest of migration script which alters views, procs
step3: commit transaction then delete the stored proc

Mike U

Any changes deployed by the Compare engine (so SQL Compare, DLM Automation, and SQL Source Control get latest) will automatically be wrapped in a single transaction, so there should be no need to create a transaction inside your migration scripts (and in fact because it would be a nested transaction it probably wouldn't do what you expect anyway)

jregan

is there a support phone number i could talk with someone? i dont think i am doing a good job of explaining my issue here.
I used a migration script because when i checked in my changes, redgate determined that it couldn't do all of the changes and created a migration script. The migration script crated needed to be tweaked as it didn't capture all the changes that were needed.

[Deleted User]

Hi @jregan, I'll create a ticket for you and one of the Support team will be in touch soon.