How can we help you today? How can we help you today?

modifying table schema and related stored proc in one migration script

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

jregan
0

Comments

5 comments

Sort by
  • 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.
    Mike U
    0
  • 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
    jregan
    0
  • 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)
    Mike U
    0
  • 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.
    jregan
    0
  • [Deleted User]
    Hi @jregan, I'll create a ticket for you and one of the Support team will be in touch soon.
    [Deleted User]
    0

Add comment

Please sign in to leave a comment.