StrataFrame Forum » StrataFrame Database Deployment Toolkit » Issues » DDT, SProcs and Views . . . Oh, My!

DDT, SProcs and Views . . . Oh, My!

Bill Cunnien — Mon, 18 Aug 2008 16:41:12 GMT

For a new project I imported 2 tables into the DDT. No stored procedures were imported. No views were imported.

I made sure that object name history was clear.

I edited these two tables. I also added two new tables to the DDT.

The object name history was looking good and ready for a deployment.

I deployed my changes to the database. At this point, I have four tables that should be showing up in the table list with my changes. They did!! Suh-weeet! This is great stuff...and so easy. :w00t:

Maybe too easy!?!

At the time I did not know it, but ALL stored procedures were dropped from the database. ALL views were dropped from the database. Only table schema should have been altered in the DB, right? Did I blow by some checkbox somewhere that says, "Drop all SPs/Views"? What configuration of the DDT would cause this behavior?

Please help me so that I am not dropping things every time I deploy changes to my database. :hehe:

Thanks,
Bill

RE: DDT, SProcs and Views . . . Oh, My!

Bill Cunnien — Mon, 18 Aug 2008 16:41:12 GMT

Simple enough. Thanks!!

RE: DDT, SProcs and Views . . . Oh, My!

Trent L. Taylor — Mon, 18 Aug 2008 14:21:10 GMT

It will require a full version of SSMS. But once you have it, just right-click a sproc that the DDT depoyed, go the extended property and delete it. If you want to do it in code...well, that is more complicated :D

RE: DDT, SProcs and Views . . . Oh, My!

Bill Cunnien — Mon, 18 Aug 2008 13:49:46 GMT

[quote]depending on how you have deployed to this machine in the past...So these stored procedures, views, or UDFs had [an] extended property applied to it.[/quote]

I had previously imported everything. After a couple of bumps in the table and indexing roads, I started over and chose to simply import the specific tables that I needed. What I failed to comprehend was that the database itself was altered..even though I did not change certain parts of it (sp, views, etc.). Since each sproc and view had an extended property updated...and my last deployment did not have the sprocs and views in the schema...then the deployment covertly dropped all of them.

Ok...I can accept that.

How do I remove that extended property on all db objects so I can start this process over again?

RE: DDT, SProcs and Views . . . Oh, My!

Trent L. Taylor — Mon, 18 Aug 2008 13:25:47 GMT

[quote]At the time I did not know it, but ALL stored procedures were dropped from the database. ALL views were dropped from the database. Only table schema should have been altered in the DB, right? Did I blow by some checkbox somewhere that says, "Drop all SPs/Views"? What configuration of the DDT would cause this behavior? [/quote]

This could be right depending on how you have deployed to this machine in the past. Any SPROC, view, UDF, etc. that was created outside of the DDT will be left alone...assuming that that sproc, view, or UDF has never been deployed by the DDT. When the DDT deploys a SPROC, view , or UDF, it applies an attribute (Extended Property) to the entity in question so that it knows that it was deployed by the DDT and can safely be removed. So these stored procedures, views, or UDFs had to have that extended property applied to it.

Now if you are running an older version of the DDT, then it will do exactly what you mentioned regardless of the attribute. But if you are running on the latest version of the DDT (1.6.6 or better) then this will not happen....and I am very confident that this works because I recently talked to someone who uses the DDT in an environment where there is a DB admin and the developers deploying to the same production server...and the scenario that you explained use to cause...well...let's just say, "some issues." :D But I recently asked them (with the last week) how this has been working for them and all is well. This is something that actually applied to our medical software as well...so I am confident that this is working as it should. But this should at least clue you in as to how the DDT treats these items.