Demystify the Database Project Folder Hierarchy with the Import SQL Script Wizard

Problem

The folder structures for Database Projects can get very complicated. Even the most experienced of us can have trouble figuring out not only where Visual Studio wants us to put certain database objects, but how to name them properly (e.g. SQLUser.user.sql). If scripts aren’t named appropriately, they may not be deployed with the rest of the database objects.

An example of a database object that can be tricky to get properly set up and located within a folder structure (like the one below from AdventureWorks), in a Database Project is a User.

NOTE: These screenshots are from Visual Studio 2010, but this tip also applies to Visual Studio 2012, 2008 and 2005.

Solution

1. First, we need an existing Database Project to illustrate how to use the Import Sql Script Wizard. If you don’t have an existing Database Project, you should start by Creating a New Database Project.
    
   
2. Create a new file, using your favorite text editor (or SQL Server Management Studio). If we were creating a User and didn’t have a database project, here is how we would write the script to create a User (with a Login).
    

   CREATE LOGIN DataOgre WITH PASSWORD = ‘the$wamP’;
   
   USE AdventureWorks2008;
   
   GO
   
   CREATE USER DataOgreUser FOR LOGIN DataOgre;
   
   GO

3. Copy and Paste the above code into the new file.
    
4. Next, save this file as C:\Temp\Import.sql to the file system, so we can import it into our Database Project.
   
   
   
   
   
5. Then, from within Visual Studio, right-click on the Database Project in the Solution Explorer and click Import Script…
   
   
   
   
   
6. Click Next to move beyond the Welcome page
   
   
   
   
   
7. You have two options on the Select File page of the wizard. We are going to choose Single File and select the file that you created in steps 2-4 (C:\Temp\Import.sql).
   
   
   
   
   
8. The options on the Specify Options page of the wizard are self explanatory. In addition, the wizard will ignore options that don’t apply to the script being imported (as in the case of a simple script that creates a User).
   
   
   
   
   
9. Click Finish.

   Author’s Note: Notice in the Summary Section that it wasn’t completely happy (“In the script that you provided to the import operation, one or more statements were not fully understood.”) with the file that we imported. This was intentional, to illustrate what might happen when you import a real script that you have written. If you open the ScriptsIgnoredOnImport.sql file referenced in the message, it will contain the statements that were not imported into your project. In this case, it is because the script file I had you create contained statements that don’t belong in a Database Project. A LOGIN is at the server level, and would go into a Server Project (not a Database Project). And, a USE statement is not required, since you are already within that scope when you are in a Database Project.

10. Now, look in your project under \Schema Objects\Database Level Objects\Security\Users. You might have guessed the location correctly after digging in the project folder structure, but wasn’t it easier to let the wizard do the work for you? You also know that the file was named the way Visual Studio wanted (.user.sql), since it imported the file.
    
    
    

Next Steps

The more often you use Database Projects, the less often you will need to use this trick. However, it still comes in handy from time to time when creating a database object that you haven’t created within a Database Project before.

• Learn more details about Importing Database Objects from a Script.
• Learn about Creating a New Database Project.