The significant majority of the errors were due to cross-database references in stored procedures and functions. Here is how we resolved them:
- 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.
- 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».dbschemaOnce the .dbschema file is created, a database reference was set up to reference the file.
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.