0

I'm trying to publish a change to a user defined table value type but the SSDT publish fails with the following error:

This deployment may encounter errors during execution because changes to [dbo].[my_tvp] are blocked by [dbo].[my_stored_proc].[@myTvp]'s dependency in the target database.

The TVP went from 

CREATE TYPE [dbo].[my_tvp] as TABLE
(
    [Id]          VARCHAR (100) not null PRIMARY KEY,
    [ColorCount]  int           not null
)

to

CREATE TYPE [dbo].[my_tvp] as TABLE
(
    [Id]          VARCHAR (100) not null,
    [Id2]         VARCHAE (7)   NULL,
    [ColorCount]  int           not null,
    unique(Id, Id2)
)

I'm able to publish to a fresh local DB but not upgrade an existing one. Any idea why?

Josh Russo
  • 3,080
  • 2
  • 41
  • 62
  • This completely breaks the SSDT automated deployment. SQL Server does not allow for Type objects to be altered. If you want them to fix it vote up this bug: http://connect.microsoft.com/SQLServer/feedback/details/319134/msft-mso-support-alter-type – Josh Russo Jun 19 '13 at 15:29

1 Answers1

1

I see now that TVPs cannot be altered, they have to be dropped and recreated, which is not supported by the SSDT publish mechanism

Josh Russo
  • 3,080
  • 2
  • 41
  • 62
  • You can do that in pre/post-deploy scripts. It's not ideal but could work in your situation. – Peter Schott Jun 18 '13 at 16:55
  • Ya, that's what I was planning on – Josh Russo Jun 18 '13 at 17:13
  • Actually that won't even work because the error I included originally stops the execution before the deploy scripts get a chance to run. This completely brakes the SSDT publish process if you don't realize that you can modify Type objects – Josh Russo Jun 18 '13 at 18:17
  • There might be an option to allow it to continue in your publish settings. I'd look through there to see if something can be tweaked to allow you to continue. We don't use TVPs in our solution so not something I've dealt with yet. – Peter Schott Jun 19 '13 at 17:44