Monday, July 16, 2012

References to Other Databases in Visual Studio Database Projects

We recently decided to move our maintenance of our database schemas from a pile-of-scripts to Visual Studio 2010's database projects, so I created a database project and imported its objects from our existing Development database.  The resulting project had thousands of errors and warnings.

The significant majority of the errors were due to cross-database references in stored procedures and functions. Here is how we resolved them:
  1. The first type of error was a reference to a project that I intended to bring into our solution anyway. I created a new project in the same solution and then created a database reference from the first database to the second. Since they were on the same server, and since we, at this time, have no intention of changing the database names, I just set the database name to a literal in the reference options. This obviated my having to replace references to the database with a variable.

  2. The second type of error was for a database that we have no control over and that we had no intention of including in our solution. To solve this problem, we had to create a .dbschema file. To do this, we used the vsdbcmd command line tool.

    The vsdbcmd command line tool resides in C:\Program Files\Microsoft Visual Studio 10.0\VSTSDB\Deploy\ on a 32-bit Windows XP system, and the syntax to generate the .dbschema file is as follows:
    "C:\Program Files\Microsoft Visual Studio 10.0\VSTSDB\Deploy\vsdbcmd" /Action:Import /ConnectionString:"Data Source=«server name»;Initial Catalog=«database name»;Integrated Security=True" /DatabaseSchemaProvider:SQL /ModelFile:«output filename».dbschema
    Once the .dbschema file is created, a database reference was set up to reference the file.
These steps eliminated about half the errors, but there were still about 1,500 remaining. Some of these were the result of obsolete stored procedures and functions referencing obsolete object. These could safely be removed from the project, since they clearly were not being used.

Another class of errors was when an object referenced another object in the same database with the full three-part object name (ie., database name, schema, object name).  Visual Studio complains about this because it allows you to rename the database at deployment time. However, if your scripts have hard-code the database name, this will cause problems. The solution to this is simply to remove the database name from the object reference.

Finally, I discovered that I had references in database 1 to objects in database 2, and vice versa. Visual Studio does not permit database 1 to reference database 2 and database 2 reference database 1.  It considers this a circular reference, even though it is different objects in each database referencing and being referenced by the other database.  The solution we used for this was to suppress certain warnings at the file level.  To do this, right-click on the file in the Solution Explorer and choose “Properties” (or press Alt-Enter) and set the value of the “Suppress Warnings” property to the warning number (or numbers, comma-separated) you wish to ignore.

3 comments:

  1. It may be helpful to add a bit of information on the user interface for references in the project property pages. For instance, why use variables as table names in the database project and what the difference is between Literal and not Literal.

    ReplyDelete
  2. I don't think I would use variables as table names. Can you explain more about why you might do that?

    I might use variables for the name of another database, but as I said above, “[S]ince we, at this time, have no intention of changing the database names, I just set the database name to a literal in the reference options. This obviated my having to replace references to the database with a variable.”

    Since the work is the same whether I do it now or wait until later when I need it, we should wait until we decide to rename a database before converting database names to variables.

    ReplyDelete
  3. Right you are. I wrote "table names" and meant "database names". When I was working on our project, I spent a bit of time trying to figure out the user interface for database references. Other than Microsoft's documentation, I didn't find much help elsewhere.

    ReplyDelete