In SQL Server Management Studio 2008 R2, How to save sets of related, frequently used queries.

How could I best save sets of queries for various projects I work with, then later load up the whole set when I'm working with that particular project?  

Then have a different set of queries that I can load up when I'm working with a different project?

I see there is a "Project" item I the main menu, and under "File" there is a "New Project" option, but from there it opens a dialog with Templates.  "SWL Server Scripts" looks like a likely appropriate template.  Then there is the "Solution" / "Create new Solution"  and "Create directory for solution" options.  

I just don't know how to use them correctly, I guess.

Thank you for some expert guidance!
LVL 1
megninAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Explain at a Barney-level of detail what you mean by 'frequently used queries'.

Offhand, you can save queries as Stored Procedures, which will compile and save the execution plan on the server, and then call them anytime you wish.

>I see there is a "Project" item I the main menu, and under "File" there is a "New Project" option
What software are you describing here?
0
arnoldCommented:
Save them i.e. when you close the query window, you are prompted on whether to save them.  When you do they are saved within your %userprofile%\Documents\sql server management studio\projects\ where you can give the query a descriptive name.

Then when you are connected to the database server on which the database in the query is file open file, and select the file and that is all it takes.

You could also add these queries as StoredProcedures to which you can pass parameters.
0
megninAuthor Commented:
Okay, I'm sorry.  My question wasn't clear enough.

I'll give real examples.  

I work with a "Youth Employment Program." I have a general query I use a lot to list information about the employers, maybe with a join to another table to show youth employed at that employer.  I've got another query for listing (for my own information) the worksites each employer has.  Another for listing the youth and their eligibility criteria, etc.  Say, four such queries. I use all when I'm gathering information for reports or just checking numbers or what-not.  For the "Youth Employment Program."   There's no reason to put them in a stored procedure, they aren't used programmatically.  I run them and look at the out put and sometimes put the output into an email or report.  But I load and modify parameters of the same basic queries all the time.  e.g. SELECT * FROM Employers WHERE [City] = 'Fort Lauderdale'.  Tomorrow I may change it to SELECT * FROM Employers WHERE [City] = 'Divie'.  So, I have several such queries I use for the "Youth Employment Program."  Same query files all the time.  I save them with names in the Projects folder and I find and load them again individually when I need them.

I have another project for "Quality Assurance Monitoring" totally unrelated to the youth employment program.  I have a number of queries I've saved to files in the projects folder for that project.  I load them individually modify and use them as I need to for the day then save them again for use (by me for my information) later.

I have several other projects, with the same need for a set of queries I use for that project on a daily basis.

I'm looking for a convenient way to save a set or group of query files with, say, a project name or a solution name and then open all of the saved query files by opening that one "project" or "solution."
Example:  I want to save all five queries I used today in a "project" or "solution" called "SYEP queries."  Tomorrow I want to open "SYEP queries" and all five of my utility queries will load into SQL Server Management Studio, so I don't have to look for each file individually.  They are sometimes in my "Recent Files" list under the "File" menu, but if I haven't used a set of queries in a while they will roll off of that list and I have to go searching for them individually.

As the subject of my question stated, the software I'm using is:  "SQL Server Management Studio 2008 R2"
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

arnoldCommented:
When you use the query window, you can save the query in said window as an sql file which will end up on the system you are using SSMS on and will be stored in the user profile you logged in with %userprofile%\Documents\SQL Server Management Studio\Projects\filename.sql

The next time you want to use one of the saved queries, you connect to the database server, hit file open file which should default to the projects folder referenced above where you can select the file with the saved query.
Once this window opens (you might want to include the USe database directive at the top to make sure you are querying to correct database or use the explicit format of database.[dbo].tablename in the query/s you save.

If you have all queries within one query window that is what you will end up, I would suggest that you save each query individually in its own file.

You could use/define each query as a function within each file. where you can .........

I am uncertain what your difficulty is. You can save the set as a project.
use SSMS to create a new project, ......


You can make your queries dynamic.

declare @variable1 int
declare @variable2 ..

set @variable1=3
set @variable2='dsafdsf'

-- your query below
select column1,column2,column3 from table1 where column1=@variable1 and column4=@variable2

in this example you need only change the values at the top.

If you use a project, you can get .........
0
arnoldCommented:
I still think rereading/reviewing your post, you actually want to have your queries stored as stored procedures.  SPs are there when there are repeated/frequent requests for information

You can build into the SP the parameters it needs and parameters that are optional.

you can have a single SP that will return all of your query data, or have individual SPs ........
0
megninAuthor Commented:
...You can save the set as a project.
 use SSMS to create a new project, ......

Yes, that's exactly what I want to do.  I said in my original question that the new project and new solution dialogs were just a little confusing.

I do simply want to save a set of query scripts that I have already saved with names such as "SELECT Employers in some city sorted by number of employees.sql."
0
megninAuthor Commented:
No.  Stored procedures would just add one more "thing" I'd have to manage.  I change the queries too much, too often to worry with modifying stored procedures.  I just want one "Project" to open up my "Employers" query, my "Worksites" query, "Positions" table query, "Applicants" table query and maybe a query where I've joined them all into a query to give me all the whatever information I need... Point being it's a query I don't want to have to write from scratch every day and don't want to have to load the same five queries individually every day.

I know that saving them in a Project, in a Solution is the way to do it.  I'd probably do it correctly on the first try.  The dialogs are just not quite Barney-level simple to follow in creating that first Solution with one or more Projects in it.

I'm afraid this became more complicated than I had intended.   It probably functions exactly like projects and solutions do in Visual Studio 2012, which is what I use for development.  You have a Solution with one or more logically related Projects.  Each Project has the files you need for that project in a tree structure so you can load up any one or all of them when you need to edit or test them.
0
arnoldCommented:
It seems you are looking for a way to save a set of "template" queries and you want to have these queries open when you open the project.
The project construct in SSMS is at least in the 2005 version does not work that way.

a project in VS is of interdependent files.
A project in SSMS is not equivalent, i.e query1 is a separate dataset and does not depend on query2 and might not actually be using the same database server.  Each query is self contained to open a connection to the DBserver that it will be getting the data from.

making your queries modular when possible with variables, and conditions could ... help

NOt sure you can achieve what you are looking for within SSMS.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
megninAuthor Commented:
I created a Solution and added my queries to it.

Now I load the Solution and my queries are listed under SolutionName > Queries > 

The queries are not interdependent.

They are just a hand full of queries I want to open all at once.

Each query has it's own version of:  "FROM CSP.dbo.Employers e "
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.