SQL Change Automation - Create Trigger Failure & TVPs

Shaggy

Does SCA distinguish between table & TVP objects with the same name?

The build fails when attempting to create a trigger object on a table when a table-valued-parameters exists with the same name.  This error message is thrown because we have a table and TVP with the same name.

Our workaround is to change the TVP name.  Is this workaround recommended?

2019-02-06T13:11:47.9468715Z ##[warning]The error 'The object 'dbo.Address' does not exist or is invalid for this operation.' occurred when executing the following SQL:

2019-02-06T13:11:47.9468715Z ##[warning]/****** Object:  Trigger dbo.TrgAudit_Address    Script Date: 11/10/2004 4:56:12 PM ******/

2019-02-06T13:11:47.9468715Z ##[warning]CREATE TRIGGER [dbo].[TrgAudit_Address] ON [dbo].[Address]

2019-02-06T13:11:47.9468715Z ##[warning]FOR UPDATE, DELETE, INSERT

2019-02-06T13:11:47.9468715Z ##[warning]AS

2019-02-06T13:11:47.9468715Z ##[warning]BEGIN

Kendra_Little

Hi there,

Interesting question! I did a few tests:

I created a table named dbo.duplicate, and a table type named duplicate. Committed, ran sequence of build and deploy via powershell, they succeeded.

Added a procedure, dbo.useduplicate, taking a parameter of the duplicate type as a TVP, and also using the type inline in the procedure in a writable way. Committed. Build and deploy still succeeding.

Added a trigger onto the dbo.duplicate table, committed. Build and deploy still succeeded.

The SQL I used is here.

So something about our scenarios is different -- if you check out the SQL I'm using, any suggestion on what changes need to be made to reproduce your setup? I may just have misunderstood what you are describing.

Thanks!
Kendra

Shaggy

Hello Kendra,

I executed your SQL in my environment and it failed with the following message:

2019-02-07T15:59:14.4268592Z ##[warning]The error 'The object 'dbo.duplicate' does not exist or is invalid for this operation.' occurred when executing the following SQL:

2019-02-07T15:59:14.4268592Z ##[warning]CREATE TRIGGER [dbo].[tr_duplicate] ON [dbo].[duplicate] AFTER INSERT AS 

2019-02-07T15:59:14.4268592Z ##[warning]BEGIN 

2019-02-07T15:59:14.4268592Z ##[warning]DELETE FROM dbo.duplicate WHERE 1 = 0

2019-02-07T15:59:14.4268592Z ##[warning]END

Shaggy

My filter and compare options are attached for reference.  I am building in Microsoft Visual Studio Team Foundation Server

Version 15.117.27414.0

Running SQL Change Automation:Build v3.1.

Monday

Is this possibly an issue of the order of when things are executed (Trigger is being created before the table type has been created) in the deployment script? I have run into similar issues and had to control it myself in custom scripts. Kendra indicates she does 3 build and deploys for each change which would make it run in the correct order.  On a side note having the duplicate names makes it hard to know what object the error really is on. You may want to change that regardless in my opinion. 

Kendra_Little

I just raised a question with the folks over in the dev team to see if this is something that may have changed between your version (is it 3.0.1?) and the current 3.0.4 version. They are over in Cambridge so I may not hear back right away, but sometimes they appear when I least expect them!

MichaelDowling

I'm having a similar issue here I think. Our build is failing for some table triggers and I can't place why. I'm on V 3.0.4 Building in TFS with a temporary server build.

<div><span>The error 'The object 'dbo.DIE_OVEN' does not exist or is invalid for this operation.' occurred when executing the following SQL:</span></div>

Shaggy

@Kendra_Little Were the developers able to address my question?