Crappy SSMS Solution explorer

 
In SQL Server Management Studio (2005, 2008, 2008 R2) it’s possible to create projects and at a first glance it seems great, but after working with the solution explorer in some projects it started to annoy me. Why? Well, I’ll describe this in my post below about this crappy solution explorer. I’ve created in SSMS the following template which i want to use for my projects.



In one of my projects, we want to organize all the SQL server objects in scripts and version them. So I decided to use SSMS solutions. That’s the way because Microsoft has added this functionality to the SSMS? Right?
 
#1 Crappy user interface
The first crappy thing is the creation of a new project. When you create this via SSMS “new project” you are first asked to choose which kind of project. In this case SQL Server Scripts and that seems to be the case for me. There are some options:

• Name. This is the first confusion thing. This is the name of the project and not of the solution.
  
• Solution name. So when you create a new project you are also asked to give the name to a solution. From my viewpoint (and that’s how SSIS works) SSMS should a solution and then you add the files (and perhaps folders). But this is the other way around. You create a project and get a solution for free.
  
• The next confusing thing is the option : “Create directory for solution”. At first you would say “Yes, I want it in a folder” but whether you set this option or not, a directory is created for a solution. Anyway, it seems that it has to do, with creating a project folder with the same name you specified at ‘Name’ e.g. Database in the solution folder. Are you still there? I think it has something to with how you want to organize your project (uhm solution). Do you want to create a solution with only scripts or do you want to organize your solution in projects.

#2 Crappy change connection
The next thing I would to do with the solution explorer is executing a query against different connections. If you click in the query file, there is a property ‘connection’. That options seems that it’s possible to execute queries on different connections. First I create a query:




Right click in the query and in the properties window you see the property connection:



It seems that it’s possible to change connection here. So let’s try to add a connection to the project.




So now there are two connections. Let’s choose one of these connections in the query:




Great! The connection does not appear in this pull down box. Why ? Well let’s try another thing. Change connection in the query window. Choose connection and change connection in the context menu:




 And hey(?) the connection appears now in the solution explorer:



And the property of the query window has changed to this new connection. So what’s point of the storing connections in the solution explorer?
 
#3 Crappy delete/remove of files/projects
The next thing is deleting files and projects in the solution explorer. So I have this situation in the solution explorer:



First let’s remove the file in the project. So right click in the solution explorer and press remove the file :




The next thing is the following window where you have the option to remove or delete. Intuitively this is a bit strange. What’s the difference between remove or delete? After some experimenting you’ll find out. When you press remove it will not physically remove the file.



To me it’s not clear why this behaviour happen when you choose remove. Why do you want this? The solution folder and projectfolders will be wasted with virtually removed files and even worse when you use version software it will fill your repository with old files.
Deleting the project is another thing. The option ‘delete’ (you know the physical delete) is not available here (?). It’s not clear to me why you can’t physical delete a project.



And the following option appears :



But the files/folders are still there?



So again, why the option to virtually remove a project is available, is not clear to me. Choosing this will not clean up the project folder and metafiles. So the solution folder is filling up with wasted projects. This is not a structured way, how to work with solutions/projects. When you choose to remove a project, you have to physical delete the folder too.
 
Conclusion
Currently we are investigating how to sset up a DTAP (Dev, Test, Acc and prd) environment and we wanted to use solutions in SSMS but now we are not sure. I’ve heard that perhaps visual studio for database developers is a better tool for doing this.