Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using Solutions and Projects to manage your SQL Server code


By:   |   Last Updated: 2007-11-01   |   Comments (6)   |   Related Tips: More > SQL Server Management Studio

Problem
When developing code for a project there is often more than one component that you need to work with for the entire project. The project may include new tables, table changes, new stored procedures, changes to stored procedures, etc...  Keeping all of these components straight as well as logically grouped together is sometimes a challenge in itself. Are there are any built-in tools that allow you better manage project components vs. one big file?

Solution
In SQL Server Management Studio a  new component has been added that allows you to logically group your project code together in one place. 

To access this you need to create a new project which you can do one of two ways:

  • File -> New -> Project  or (Ctrl + Shift + N)

When you create a new project you have several choices such as:

  • SQL Server Scripts
  • Analysis Services Scripts
  • SQL Server Compact Edition Scripts

At this point you need to select the type of project and a new solution will be created for that project.

After you create the new project the following screen will appear in Management Studio.  At this point you can begin creating connections, queries and any other miscellaneous files that you need for this project.  In addition, you can have multiple projects within one solution.

The following gives you an idea of what a solution could look like with multiple projects and multiple components within each project.

This is a pretty simplistic concept of keeping your code together, but for complex projects where there are a lot of components this could become a big help for keeping your project code straight.

Next Steps

  • Begin to build solutions and projects for your projects you are currently working on
  • Build projects for your maintenance scripts
  • Build new projects for troubleshooting scripts
  • The list is endless.  I think once you start to think how this could help you, this will be a great way of managing your scripts.

 



Last Updated: 2007-11-01


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




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.



    



Monday, August 06, 2012 - 7:57:04 AM - indrish Back To Top

actually, i want o know how this can be used  with visual studio..

please explain it, i really want to know about this.

 

--indrish


Wednesday, November 19, 2008 - 5:28:00 AM - Robert Carnegie Back To Top

Oh, and:

According to help: "Change the default location by opening or saving a file or project from another location. Microsoft SQL Server Management Studio stores the location of the previously opened project and uses it for the next opening or saving of a project."

 You can fake nesting by naming projects such as "Nesting.Nested1", "Nesting.Nested2", "Nesting.Nested2.2b" etc.  (But "Nesting\TrueNesting" apparently isn't allowed.)


Friday, November 14, 2008 - 9:04:50 AM - JimR Back To Top

Thanks Robert. for the comments.  I'll study them.


Friday, November 14, 2008 - 6:11:14 AM - Robert Carnegie Back To Top

New upgrader here, SQL 2005 - regarding your issues, it appears from here that if you open a script from file starting from "Registered Servers" and then do,

 File -> Move into

 then the project contains a reference to the file located elsewhere, instead of making a copy in the project's home.  I think the "Save as" issue also goes away.

 If so, just make a shortcut from the project's home to where you usually keep files, and the rest is easy.

 As a bonus, the script window and/or tab during the current session is named "MeaningfulFileName.sql" instead of "Server1000.ServerFarm.local.database - E:\...ame.sql".  I am considering using a scratch project just for that purpose.  It is more of an issue since Management Studio moves the list of query windows around whereas in Query Analyzer up to nine open windows per server stay in the order that you opened them.

Or, you can have the project's copy -and- the "real" copy of the script, both in the project.

 Downside - it appears(?) that a unique script can be only in one project per solution.  And also only in one query window at a time.

Well - you can also write a script that simply contains this SQLCMD operation -

 :r C:\SQL\OriginalScript.sql

Running that, runs the script.  And you can have lots of copies of that command.

(If like me you like to write scripts with a dozen statements but select and execute them one by one, then there is more work to do.)

(If you also manage multiple servers with the same scripts, and logging in to each is tedious, then running multiple Management Studio sessions is the way to go.  I also have prototype scripts to seize a desktop window named "Management Studio" and change its title to just the server name - every five seconds because it will be changed back.)


Wednesday, October 22, 2008 - 7:02:50 AM - grobido Back To Top

Thanks for the input. To be honest I have not used them at all.  I basically do the same thing that you are doing. 

It would be interesting to see if other people are using this and if they have any success with the issues you wrote about above.

Greg


Wednesday, October 22, 2008 - 4:41:47 AM - JimR Back To Top

I gave up on Solutions and Projects a while back for several reasons:

1. SSMS insists on putting my solutions somewhere deep in my My Documents folder (and I have found no way to change the default)

2. The solutions do not support any nested sub directory levels (other than the 1-level artificial "Project".)

3. Save as does not work as every other save as in the world (create a second copy with a new name); instead (unless it has been recently changed/fixed) it simply renames the original object. So try to save some new variant fork of your development and preserve the original and it simply discards your original version and name and overwrites it with your new changes in a renamed file.

I am probably missing something, but I find life much easier keeping my sql code in a standard directory, and managing and opening them by hand.  The last time I suffered with one level directories was back with the original 128K Macintosh floppy disks.


Learn more about SQL Server tools