Create a dbschema file for DB Project reference


Scenario:  When using a VSTS 2008 or VSTS2010 database project, stored procedures and views that reference system items or items in other databases throw warnings or invalid reference errors.

Solution:  Add a database reference to your project, the hardest part, getting the .dbschema file.

Snippet:

To get the schema file, from the Command prompt

Navigate to the Deploy folder under your visual studio installation.

cd C:\Program Files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Deploy

Run the following comand replacing servername and  DBName as appropriate.

vsdbcmd /a:import /cs:"Server=servername;Database=DBName;Trusted_Connection=yes; " /dsp:SQL /ModelFile:"DBName.dbschema"

Once you have the file, copy it to a location you want to reference and use the wizard in VSTS to add the database reference.  For large shops or databases that are shared across multiple projects, a dbschema library on a file share everyone could get to might be appropriate.


One response to “Create a dbschema file for DB Project reference

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: