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


By:   |   Updated: 2013-08-19   |   Comments   |   Related: More > DevOps

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.

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

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.

    save this file as C:\Temp\Import.sql to the file system

  5. Then, from within Visual Studio, right-click on the Database Project in the Solution Explorer and click Import Script...

    rom within Visual Studio, right-click on the Database Project in the Solution Explorer

  6. Click Next to move beyond the Welcome page

    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).

    We are going to choose Single File

  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).

    The options on the Specify Options page

  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.

    You also know that the file was named the way Visual Studio wanted
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.

Last Updated: 2013-08-19


get scripts

next tip button



About the author
MSSQLTips author Rick Krueger Rick Krueger is a Lead Development DBA, BI Developer, and speaker with 18 years of experience, based in the Twin Cities.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools