Monday, May 15, 2017

Compiling SQL Server Database project

One of the great features of SQL Server Database project is the compilation of the project. If the project setup up correctly, developers can use advantages of compilation. Let's assume you have changed the name of the column in some table. You know that this column is used in many places in your database code. It is hard to find all locations where you have to change the name of the column. Compilation of project helps a lot in such scenarios. Just compile your project and Visual Studio will show all places where you have mistakes. To compile the project right-click on project file → Build or Rebuild. After build finishes open 'Error List' window and check if there any error in warning in it.

From previous example we have some warning which we have to fix. As you can see on the picture below, Visual Studio helps us to identify places which could be the cause of the warning. 

Warning say to us: Severity Code Description Project File Line Suppression State
Warning SQL71502: Procedure: [Application].[CreateRoleIfNonexistent] has an unresolved reference to object [sys].[database_principals].
It means that we have to add a reference to the master database. To add a reference to the master, database you have to navigate to WideWorldImporters project → right-click on References → Add Database Reference → Select System database → Ensure master is selected.

After that, if you recompile your project, all warning will go away. Once again it is critical to use compilation feature of SQL Server Database project. It saves a lot of time for maintenance and issue investigation in future. Source code for this tutorial you can find using link