Schema Compare Problem #5
I am comparing a Database to a Project. One table is showing up as being different. Here is what DBPro reports as the difference:
In the Database:
[DefaultPerformanceEmailText] [nvarchar] (
max)COLLATESQL_Latin1_General_CP1_CI_ASNOT NULL,In the Project:
[DefaultPerformanceEmailText] [nvarchar] (
max)COLLATESQL_Latin1_General_CP1_CI_ASNOT NULL CONSTRAINT[DF_HumanResourcesPolicy_DefaultPerformanceEmailText]DEFAULT(''),When I click the 'Write Updates' button to synch my Project, DBPro says everything was successful but when I do a recompare, this same difference reappears. DBPro is not removing the constraint.
Thanks - Amos.
[1261 byte] By [
AmosSoma] at [2007-12-28]
Hello Amos,
you've got such a bunch of problems (Schema Compare Problem #1 - #5) that I wonder whether you're problem is much more general. I will only answer on this thread, although the problems you are having seem to have different causes in some cases.
I would a few possible ideas that could be the cause (either one - the other - or more than one in each case):
1. I'm wondering whether you might be working on the project disconnected from your source control (i.e. at home or so) and you neglected to recursively check out the project before disconnecting from the source control. This would leave all your files "read only" and the project would not be able to check out the files in order to make the changes you want. I would expect an error to occur stating something of this nature but we both know there were some VSS problems prior to RTM. Maybe the error message isn't coming when it should.
When you are working disconnected from Source Control ALL files for the project should be recursively checked out before leaving the "office".
2. Other problems that you are having could be due to dependencies in the objects. If procedure A references table B, then table B can not be deleted unless procedure A is changed (to remove the reference) or deleted at the same time. Of course in a large project these refences can be quite complicated and at worst contain circular references which can be rough to reconcile. This is the blessing and curse of DB Pro at the same time. It will not allow you to delete objects which are still referenced anywhere in the project. You have to get to the root and work upwards. Finding the root object (that you can delete by itself) could be quite hard at first.
I feel this is one area that DB Pro does not cover yet. Being able to show all references inside a project (with a graphical UI showing all the depencies or something similar). But this would be a HUGE wish for an additional tool in DB Pro that is certainly hard to deliver. I'm sure you have seen third party tools that can do this, but even then in large projects it is terribly hard to see the forrest for the trees.
3. If by way of manually deleting files (with out the project being opened) or some such other "non legal method" objects have been deleted or moved without updating the project / .dat / and validation database then of course things get complicated. The project thinks it still has dependencies or objects which are not there anymore.
4. A simple way to create problems could be injudicious use of external programs (SSMA or such) to make changes to the database. You can do things there, that wouldn't be allowed in the project. Synchronising can then become a problem, especially in the case of external deletions.
I don't suppose this will be all that much help to you but maybe you can understand the how and why better and be able to prevent a reoccurrence that way.
Maybe deleting the .dat file while the project is closed and then regenerating it by reopening the project will help. If your project is under source control, be sure to be connected and have the last version. I'm not sure though. Especially since your project seems to be quite large. It will presumably take quite a while to regenerate the .dat file.
HTH
Alle
BTW - As a possible last ressort, you could create a NEW project and import the external schema again and work on from there. The errors on importing the schema might also give you an idea what is wrong. The external source could have problems too!
Alle,
Thank you for the time you spent responding to this issue. I don't know what the problem is but it's not the result of working disconnected. I haven't done that. I suppose the issue could be related to dependencies but how can it be possible to not be able to remove a constraint? What would be dependent on a constraint that it couldn't be removed? I don't think this is possible.
I tend to think the real problem is that Database to Project synchonizations is not "ready for prime time". I have never had these issues comparing a database to a database.
Regarding the fact that DBPro will not delete objects during a schema compare when there are dependencies; if this is the case, I don't think Microsoft made the right decision by implementing schema compare in this way. I think it would be much easier to clean up the dependencies if the object in question was deleted and then all the dependencies show up as errors. This would then clearly let me know what objects I need to edit or delete. This could at least be an option.
Amos.
My post was not really aimed at your constraint thread, but at the multitude of compare problems you are seeming to have at the moment with the DB to project compare. I haven't used this direction extensively yet, but in the tests I did it seemed to be OK. Especially when importing externally created objects.
In the case of the default constraint, the problem could be that the constraint was / is not imported into the project as an independent object, but instead inline in the table definition? I seem to remember having a problem in this area too. Since there is no independent constraint object it doesn't seem to be able to grasp it or delete it. Synchronising the table definition doesn't seem to help in this case either. Try changing the project by reimplementing the constraint (so that it becomes a separate object). If this is the case in RTM, then I would say this is a bug - either in the import because the constraint definition is not separated from the table definition or with the schema compare that imported it. This appears to happen with default constraints more than check or foreign key, etc. constraints.
To be perfectly honest, I'm not 100% sure on that DB Pro schema compare prevents the deletes in the project (dependencies preventing delete). I didn't try it last night as it was late and I was at home. I'm pretty sure though that schema compare is that way.
Whether the other way would be better? I'm not sure whether finding the errors in dozens of objects is easy either. An error message when trying to delete with the dependency shown (which would be known) would be easy to find with a Find over the project.
Sorry I couldn't help you here yet.
Alle
I'm not able to reproduce this problem. When I compare the project with a database, I see the table is different because of the constraint. When I write the updates, the constraint is gone. Instead of writing the updates, can you export the script to the editor and post the script that is shown? That might tell me something about what is happening.
Are you doing a database to project compare? If so, I was told this is W.A.D because DBPro will not delete objects out of a project during a schema compare for "security protection".
Amos.
Yes, I was the one that told you that. 
It's not really for security, more of a "don't let the user shoot themselves in the foot" feature.
But an inline constraint is different. It's not an object that lives on its own - so when the table gets updated in the project, it should get removed. Like I said, it works for me. I'm going to try a few other things to see if I can repro it, but I'd like to see the update script that is generated on your machine.
Chuck,
As I mentioned to Alle in a previous post, I guess I wouldn't mind manually deleting objects as much if it didn't take SO LONG to do. We have 3200 objects in our project and (I kid you not) it takes 5 to 10 minutes each time I delete an object. I have no idea what it's doing, but VS just hangs for this amount of time. With smaller projects this hasn't been a problem. Is this a known issue, or am I doing something wrong?
Amos.
We will look into the performance issue and I'll get back to you within the next few days. Can you give us your machine specs so we can compare it to the hardware that we are using?
Thanks
Hi Amos,
just as another thought and out of curiousity, have you got Visual Studio 2005 SP1 installed on your dev box?
I know that SP1 seems to have improved the general performance of VS quite a bit in several areas. BTW, be very careful if you want to install SP1. It temporarily needs an awful lot of space on the system drive (about 3.5 Gigs) regardless of where VS in installed on the file system! Otherwise the installation can run out of space after an hour or so (yeah, this is not a fast update). There is a blog from the VS folks on how to disable the SAFE mode during installation which helps to speed the installation up.
Alle
Chuck,
My Specs:
Dell Latitude D820
Intel CPU T2500 @ 2Ghz
Windows XP SP2 (2G Ram)
100G Hard Disk (about 20G Free)
Project:
3200 Objects
TFS Source Control
Deleting an object in Schema View takes, on average, 3 to 5 minutes and quite often takes 10 minutes or more.
Amos.
Alle,
I do not have SP1 installed; I haven't been brave enough to install it :). I'll wait and see what Chuck says about the performance issue and go from there.
Amos.
Most important dimension is the amount of memory in the system? Can you tell us how much memory you have? Also did you compare the performance when you project is not under source control?
-GertD
Gert,
I have 2G of RAM. All our projects are under source control. I've haven't removed source control to test performance. Frankly the performance when not under source control doesn't matter to me. We need the source control.
Amos.
I understand your need for source control, but I helps us narrow down the issue.
-GertD
