Using Solutions and Projects to manage your SQL Server code

By:   |   Updated: 2007-11-01   |   Comments (6)   |   Related: > 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.

Using1

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.

Using2

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

Using3

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.

 



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2007-11-01

Comments For This Article




Monday, August 6, 2012 - 7:57:04 AM - indrish Back To Top (18929)

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 (2227)

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 (2209)

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

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

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 (2040)

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 (2038)

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.














get free sql tips
agree to terms