I’ve been trying to get a multi-database solution working correctly with MS Visual Studio Team System for Database Professionals (aka DBPro or DataDude). Anyone who has tried to tie multiple co-dependent databases together within DB Pro has experienced some of the pain points in working with these projects. Hopefully these notes can help you to some extent.
Pain Point #1: Circular References
This is the largest challenge that we face in our environment. DB Pro doesn’t really understand that DB A can reference DB B which can then reference DB A. You can easily add a Database Reference to another project in your solution by right-clicking the “references” sub-folder and adding a new Database Reference. However, once you’ve done that, you can’t add one from that project back to the original. I understand you don’t want to get caught in an endless loop, but it would be nice to have some sort of Max Level of Recursion set that would stop the program from getting caught in that.
That being said, there is a way around this. You can generate a .DBSchema file for your existing database using the instructions from this site. Note that you may need to tweak the options a little bit. I think the “ModelType” parameter has been deprecated in GDR2. Save that DB Schema file to some central folder and repeat for each database you want to reference. Once done, go back into your projects and add a reference to each of those files. Because these are DBSchema files and not the actual database projects, DB Pro will recognize these and you’ll be able to add a reference as needed to each database.
Remember that these schema files will not be kept up to date automatically. As you build your DB Projects, you’ll need to update those schema files in some way.
Pain Point #2: System Objects
This isn’t a hard problem to work around and there are a lot of hints out there about this. The easiest way to handle references to the system objects is by adding a reference to the included DBSchema files created when you installed DB Pro. These can generally be found in %ProgramFiles%\Microsoft Visual Studio 9.0\VSTSDB\Extensions\SqlServer. There should be sub-folders for 2000, 2005, and 2008. Simply include these references in your projects and you should be set.
This may not always help you if you reference another database referencing the system objects, though. I ran into this problem and had to find yet another workaround, which leads me to…
Pain Point #3: “Unresolved References”
When all else fails and you can’t actually get around the “Unresolved Reference” warnings due to the use of temp tables, referencing another database that references the system objects, or some other reason you have a final recourse. That is to tweak the file properties to ignore certain error messages or warnings that occur for that file. It’s not a great solution because you could miss some valid warnings, but it can help you get around those warnings that keep popping up when you try to build or deploy. To add an exception just find the file, select it, and edit its properties. The last option is to “Suppress Warnings” and that will let you bypass the warnings that normally occur when DB Pro can’t figure out what you are trying to do. This should really only be turned on for code that is definitely working in your environment and the code should be reviewed carefully when enabling this option.
Finally, I don’t necessarily claim that any of these are best practices. We’re learning here and trying to figure out the best way to work around the limitations and quirks in DB Pro to get it working in our environment. I’m more than happy to learn new and better ways to do things so feel free to share your tips or to correct me where I’m wrong.
