In previous articles we discussed how database professionals could use the features in Visual Studio 2010. During the video I demonstrated with a test project. Since then I have been working to migrate my DBA database project into Visual Studio 2010. From the beginning I ran into issues. This was a very discouraging start to using Visual Studio 2010 for my projects. There was a very simple fix and now we are moving right along.
One of the scripts that I deploy to all of my servers is Adam Machanic’s (Blog|Twitter) WhoIsActive. I created a stored procedure and saved the script. A few seconds later I saw 84 warnings show up on the Error List. Something was amiss. I have this script deployed into most of my environment and it works like a charm. It was time to do a little research.
The warnings all seemed to be due to some sort of missing reference. The WhoIsActive script, like many DBA scripts, uses the [master] database heavily. Visual Studio is parsing through the script and can’t find any of the items that the script is trying to reference, so it throws a warning. The good news is that there is a very simple fix.
Adding a Reference to master.dbschema
Database projects are designed to be self contained. When creating a database project, scripts typically will reference items within the same project. Some scripts, however, will reference other database items. In this case, the [master] database is referenced. The master.dbschema file is not included in every project for performance reasons. It contains a large number of objects that are unnecessary for many projects.
The default installation of Visual Studio 2010 includes dbschema for both the [master] and [msdb] databases. There are separate schema SQL Server 2005 and 2008. These files are located in the Visual Studio installation directory in the Extensions folder. I am working on a SQL 2008 project so I will use the following folder:
C:\Program Files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Extensions\SqlServer\2008\DBSchemas
To add the database reference right click on the References folder in the Solutions Explorer and select Add Database Reference. This will open the Add Database Reference window. Add the master.dbschema by selecting browse and navigating to the folder that contains the appropriate schema for your project. In my case, it was the folder listed above. I accepted the defaults and clicked OK. Saving the project will cause Visual Studio to parse the code again and resolve the reference errors.
If no other references have been added, it can also be added from the References tab of the Project Properties page. If the project already contains references this tab will show a list of the current references and no additional references can be added.
We are working up more articles and videos about Visual Studio 2010 and its features for Database professionals. Do you have a specific topic you would like us to cover? We would love to hear from you.
