VS Db Pro – Script Deployment - Database Context

When deploying databases using Db Pro the scripts which are executed have a database context. Visual Studio will automatically include a 'USE [YourDbName]' where YourDbName is taken from the project's BUILD property. This is the context for the scripts to execute. This is important because if you change it in any of the scripts it can mess with your deployment.

For example, you create a script called 'AgentJobs.sql' in the Post-Deployment folder and add a reference to the script in Script.PostDeployment.sql. This means your AgentJobs script will be executed when you do a deployment. Now assuming you add a 'USE MSDB' statement in the script, your deployment runs successfully HOWEVER any scripts which execute after AgentJobs eg Data.sql (it executes in alphabetical order) will now have MSDB as the context rather than [YourDbName] as expected.

So make sure that you use fully qualified references to objects in other databases and DON'T use 'USE'!

Visual Studio Db Pro (DataDude) Headaches

So, no question, Db Pro is a great tool. However, it is not without it's headaches.

When working with TFS and multiple team members, we have had no end of synching problems and regular out of memory errors. This only seems to occur when multiple users are working on the same Db Pro project.

As you can imagine, this has added to development time on projects... ok not hugely but certainly a day or maybe two in terms of charge-able hours and in terms of developer frustration and tense working environment!

My advice to you would be keep it to single user projects where possible... if not, maybe wait for SP1 or Orcas before switching to DataDude.

BUG REPORT: VS Db Pro - TFS - Get Latest Version doesn't update error list

When using Visual Studio Database Professional (DataDude) and Team Foundation Server there are synching problems between team members when using "Get Latest Version" - the error list does not update to reflect changes. Allow me to give you an example:

1. DeveloperA deletes TableA and checks in. At this point, StoredProcA is in error because it depends on DatabaseObjectA - this is shown in DeveloperA's error list in Visual Studio.
2. DeveloperB does "Get Latest Version" on the project
3. DeveloperB corrects StoredProcA and checks in.
4. DeveloperA does "Get Latest Version" on the project

After the Get Latest, you would expect that DeveloperA's error list is now clear of the aforemetioned errors. Unfortunately it isn't. To get rid of these errors, Visual Studio must be restarted OR each error double-clicked. And every now and then, only a reboot will resolve it.

Have logged it with Microsoft Connect... you can view/watch it at https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=285730


******************************** UPDATE ********************************

This is the response from Microsoft.

Unfortunately, this is expected behavior. The database project doesn't know when all of the files have finished being updated, so it doesn't know when to refresh itself. When the project is in this situation, there is an information message in the info strip at the top of schema view telling you that you need to refresh. You can refresh the project one of these ways: . do a refresh on the solution explorer (button on the toolbar of solution explorer) . open the file that was modified . build . close/reopen the project

FEATURE REQUEST: VS DbPro - Refactor/Delete function

If you have been using Db Pro (aka DataDude) you may have hit this little headache during your build phase... if you delete a column, table, stored proc, function or other object, references to it are not deleted thus leaving your project in an error state which is simple to fix however time-consuming and unnecessary.

I've suggested this should be a prompted action whenever you do a schema view delete... and if the action is not carried out, then all affected items should be flagged.

If you've hit this little nugget of joy, please vote for the feature request...

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=285727

************** UPDATE FROM MICROSOFT **************

This is a good suggestion, thank you for your feedback. We are considering your suggestion for future release.
Posted by Microsoft on 22/08/2007 at 9:45 AM

************** ANOTHER UPDATE FROM MICROSOFT **************

We are concerned about two potential problems around how we understand your suggestion:1) Cascading deletions. If we were to enable deletion of dependent objects automatically, the cascade could be disasterous2) Statement level references. Do you envision deletion of referencing statements within programmability objects or deletion of the programmability object (function, sproc, etc.) as a whole?We do believe that the deletion of child objects, such as indexes or triggers on a table being deleted, is beneficial.We'd be interested in additional details you could provide on the scenario that you envision
Posted by Microsoft on 8/22/2007 at 9:45 AM

I agree that a cascading delete would be far too heavy-handed. An excellent solution would be something similar to a 'schema compare'.

For example, let's say I select Refactor>Delete on an object called [Qty] on the [Order] table. A window appears looking very like the schema compare window which lists all objects dependent on [Order].[Qty] broken down by schema type. When an object is selected, the object definition is shown below with the references to [Order].[Qty] highlighted and below that the update script (and the cursor in the update script is positioned to the current object).

I have 3 Update Actions available to each object in the list - Skip, Drop and Update.When I select, Drop, the Update script is automatically updated to carry out that action.When I select Update, I then remove or replace references to [Order].[Qty] in the update script for the current object.When I select Skip, the object is not changed in any way.

Once all changes are done, I can then hit 'Write Updates' to apply the changes including the delete of [Order].[Qty].

This implies that a 'Find Orphaned References' should also exist which identifies references to deleted objects ie when the 'Skip' update action was selected. And again, a similar approach could be taken as outlined above.

This approach covers both your questions ie eliminating the impact of cascading deletes and amending programmability objects as well as deleting them.
Posted by khodges on 8/29/2007 at 5:33 PM