Tuesday 7 February 2012

SQL Server Management Studio customized startup options

SQL Server Management Studio customized startup options

 

ProblemSQL Server Management Studio (SSMS) is now the primary tool that we all use to manage SQL Server.  Whenever I open up SSMS I always go through the same steps to connect to a server and open certain query files.  Are there any shortcuts or alternative ways of starting SSMS?
Solution
SQLWB (sqlwb.exe) is the executable file that launches SQL Server Management Studio (SSMS). Most-likely the name corresponded to the original working name for Management Studio during the development phase of Yukon (the project title for what would eventually become SQL Server 2005): SQL Server Workbench. What many SQL Server professionals fail to realize is that the startup behavior of SSMS is customizable.  By simply passing parameter values along with the command to launch sqlwb, you can open default queries, projects, or connections.  You can also control whether to launch SSMS with the application's splash screen.  Let's take a look at the list of parameters available, courtesy of SQL Server Books Online:
sqlwb [scriptfile] [projectfile] [solutionfile]
[-S servername] [-d databasename] [-U username] [-P password]
[-E use Windows security]
[-nosplash]
[-?]
Arguments
The [scriptfile], [projectfile], and [solutionfile] parameters specify a file (or in the case of [scriptfile], the possibility of multiple files to open upon launch of SSMS.  If  you do not specify parameter values for servername, databasename, username, or password when you launch sqlwb and specify a script(s), project, or solution you will be prompted for the applicable security context for the file(s) you are opening.
Let's look at some examples of the behavior associated with the various options for launching SQL Server Management Studio from a Run command or Command Prompt:
Open a single script (.sql) file upon SSMS startup
sqlwb "C:\Temp\Config1.sql"
This command launches SQL Server Management Studio and prompts you for the connection information.  Note the query name in the background of this screen shot.
Open multiple sql query (.sql) files upon SSMS startup
The process for opening multiple SQL query files is only slightly different, simply list each of the full file paths for each query file you wish to open, separated by a space, after the call to sqlwb as shown in this screen image:
sqlwb "C:\Temp\Config1.sql" "C:\Temp\Config2.sql"
Without specifying the connection information in the run command for sqlwb you will be prompted for the SQL instance and security information upon launch of SSMS.  Note that once authenticated each of the queries connect using that same criteria.  This bears repeating: each .sql file will connect to the instance you specify, as the login you specify, when launching SSMS in this manner.
Open a SQL Server Management Studio Project (.ssmssqlproj) file upon SSMS startup
Microsoft provided an additional layer of project management with the release of SQL Server 2005.  The concept of a solution and project was nothing new to the developers out there.  This concept has been a component of the Visual Studio architecture for many previous releases.  However, in the continuing streamlining between SQL Server and Visual Studio interfaces, the concept finally was incorporated into the SQL Server management tools.  Simply put, a SQL Server Project file is a collection of various connections,  queries, and other objects that are organized to be utilized for a common purpose.  I personally use SSMS Projects for such purposes as Standard Installations, Daily Maintenance Checks, and Security Audits.  Just as SSMS Projects are a collection of multiple components, SSMS Solutions are a collection of multiple SSMS Projects.  The syntax for launching an SSMS Project or Solution is no different than launching a single .sql file from the command line or Run menu. 
Let's look at the following example; we will specify an existing .ssmssqlproj file, connecting with Integrated (Windows) security to the Sauron.Northwind database.
sqlwb "C:\Config\Config.ssmssproj" -S sauron -d Northwind -E
Additionally, you can open a SSMS Solution by providing a solution file path (...\*.ssmssln) as a parameter.  The following command opens the solution file Config.ssmssln, passing the connection information for the Foo login against the sauron instance of SQL Server:
sqlwb "C:\Temp\SSMS Projects\Configuration\Configuration.ssmssln"-S sauron -d Northwind -U Foo -P pwFoo  
Additional Parameters
Here are some additional options.
  • Launches SQL Server Management Studio without the splash screen
sqlwb -nosplash
  • At any time you are unsure about the parameters available for sqlwb, you can append the -? parameter to display the following help information.
sqlwb -?
 Don't feel that you're forced into launching SQL Server Management Studio with the default splash screen and an initial instance connection.  The parameters associated with sqlwb.exe allow you to specifically control your startup parameters for files and connections.

 

No comments :