SSMS can also execute queries in SQLCMD mode. SSMS executes these queries by using the Sqlcmd utility, letting you include Sqlcmd extended commands not available in regular T-SQL. You can toggle this mode on and off on the toolbar or from the menu by selecting Query, SQLCMD Mode. This setting is unique for each query window, and the Query menu only shows when the focus in SSMS is on a query window. The query window highlights rows containing SQLCMD commands, making it easier to remember that you’re working in this mode.
Working with Projects
One of the biggest advantages of SSMS’s use of the Visual Studio (VS) paradigm for solutions is that it enables you to organize and use your script files into solutions and projects. Although SQL Server users who have used VS should easily understand the concept of projects and solutions, those coming from mainly a DBA background can find it a little confusing or even not very useful at first sight.
So what is an SSMS project? It’s a collection of connections, script files (with the .sql extension), and other files. A project’s main purpose is to organize and group related files to make working on a particular task or project easier. A solution is a collection of related projects, grouped together for ease of management. If needed, you can include the same project in multiple solutions. You can define one or more database connections in a project. Each script file is associated with the connection it was created with (although you can change the connection on the fly if you need to execute the script against a different database).
As a consultant working on multiple projects for multiple customers, I’ve come to greatly appreciate the ability to group and organize project files as well as my collection of saved scripts that I use for almost any project I’m working on. It’s also much easier to check files into and out of SourceSafe when they’re grouped in an SSMS project. Once you understand projects and solutions, you’ll see the logic of organizing your existing scripts into related projects to make it easier to find them and work with them to improve your own productivity.
When you work with projects, one of the frustrating and least intuitive tasks is changing the default connection for a project script file to point to another database. When you look at the file’s properties, the Associated Connection property is grayed out; SSMS doesn’t let you change it. Interestingly, if the file has no default connection, SSMS lets you type in the connection name—and only then “locks” the setting. If you change the connection in the query window, the file resumes the old connection when you reopen the file. So at present, it appears that the only way to permanently change the connection associated with a script file is to remove the existing connection from the project.
SSMS has many improvements over its predecessors, but in my everyday work two features stand out for me. The first is object filtering: You can now rightclick almost any list of objects in Object Explorer and filter by name, schema, and creation date. I find this simple feature to be a huge time-saver when I work with databases that have hundreds of tables or stored procedures. The other feature I find very valuable is the improved dialog boxes. Not only do they open in a modal window, letting you switch back to SSMS if you need to (or open several dialog boxes at once), but they let you capture the generated script instead of executing it. This capability can be useful when you want to save the script for repeated execution as a scheduled job or a stored procedure—as Figure 2 shows for the BACKUP DATABASE command—or when you want to examine what SSMS executes behind the scenes.
Unfortunately SSMS still doesn’t provide Intelli- Sense to save you from typing the object names in full. The buzz is that SSMS in SQL Server 2008 will provide IntelliSense for SELECT statements. Until then, you can check out SQL Prompt, a third-party code-completion tool from Red Gate Software (www.red-gate.com). SQL Prompt works as an add-on to SSMS, providing IntelliSense and a few other timesaving features, such as intelligent SQL code insertion. I’ve been using SQL Prompt for a while and have found that it saves me a lot of time typing in SSMS.
Working with Reports
SSMS includes a set of built-in reports to give you a better view of what’s going on in the database or on the server. These reports are implemented via SSRS technology. You can open a report by right-clicking an object in Object Explorer and selecting Standard Reports. A new feature of SSMS in SQL Server 2005 SP2 is the ability to include your own custom reports and have them easily accessible, like any built-in report. Rolling out your own reports for SSMS is fairly easy if you’re somewhat familiar with building reports using SSRS. Just create a report using SSRS, copy the Report Definition Language (RDL) file to any folder, and open the report by right-clicking the SQL Server instance node in Object Explorer and selecting Reports, Custom Reports from the Object Explorer pop-up menu.
After you’ve opened a report, SSMS adds it to the list of recently opened reports. SSMS passes in a few parameters—such as ObjectName, Object- TypeName, Filtered, ServerName, FontName, and DatabaseName—from the selected node in Object Explorer. Your report can use these parameters, so that you can develop context-sensitive reports that are well integrated into SSMS. I’ve included a custom report called Service Broker Queues with this article, which you can download here. The UI for Service Broker in SSMS is limited, so this report will make it easier for you to see the queues in the database, their status, and the number of messages in each queue. Note that the report runs in the database context and needs to receive the Database- Name parameter from SSMS. So, to execute the report, you need to select a database node (or any subnode below the database node) in Object Explorer.
Tap into SSMS’s Power
SSMS in SQL Server 2005 is packed with functionality to help you more effectively manage queries, organize resources relevant to a management task, and work with reports. Explore SSMS for yourself, and let me know if you find any more “hidden” features.