“The SQL Guy” Post # 6: Save Time Connecting to SQL Server Using Management Studio (SSMS)


SQLServerOK, so I’ve been on a bit of a break over the last couple of months. Truth is, with TechDays Canada being one of the major things I am responsible for here at Microsoft Canada, time has been a previous resource. Now that the TechDays 2011 tour is complete, things are looking up to keep these blog posts going.

This post provides you with some info on how to take advantage of SQL Server Management Studio (SSMS) features you may not be aware of. They work on all versions of SQL Server that I have tried (2008, 2008R2 and 2012 RC0) and provide a great way to create shortcuts to connect to specific server instances. To take them for a spin, you can download an full-featured evaluation copy of SQL Server 2008 R2 from the TechNet Evaluation Center at http://technet.microsoft.com/en-ca/evalcenter/default.aspx (look under Server Products and Technologies | Release). If you are feeling adventurous and want to explore SQL Server 2012 RC0, you can download it from http://technet.microsoft.com/en-ca/evalcenter/hh225126.aspx. Give it a try!

SAVE TIME WITH ONE CLICK CONNECTION TO SQL SERVER

You are a developer or DBA responsible for working with a specific database. Every time you open up SQL Server Management Studio, you have to provide the name of the SQL Server Instance,  Authentication Type, User Name and Password and if you are planning on working with a specific database, you will need to click on Options and then select the default database. Once you are logged in, you will then need to click on New Query to open the query editor.

 

Performing the above operations on a frequent basis to connect to SQL Server using management studio could time consuming. Wouldn’t it be nice if you could simply click on SQL Server Management Studio and it logs you in and also connects to the database you usually work with and open up query editor by default for you?

 

You can now change the behavior of SQL Server Management Studio to make it work the way you want and here’s how you can do that. Please note that there are two changes that needs to be done:

 

1.       Update the SSMS.EXE to include the connection parameters in the Shortcut link:

 

CONNECT TO SQL SERVER THRU MANAGEMENT STUDIO WITH DEFAULT VALUES

 

Syntax: SSMS.EXE –S <ServerName> -d <Database_Name> -E

 

Example: SSMS.EXE –S TK2SAMSQL01 –d MSSOLVE –E

 

You can update the shortcut link of SQL Server Management Studio from Start->Programs->SQL Server 2008-> SQL Server Management Studio link. (Simply right click on the link and select properties to update the link)

 

2.       Configure SQL Server Management Studio to open Object Explorer and Query Editor by default:

 

OPEN QUERY EDITOR BY DEFAULT WHEN MANAGEMENT STUDIO IS LAUNCHED

STEPS

ACTION

1

SELECT TOOLS FROM SQL SERVER MANAGEMENT STUDIO MENU

2

SELECT OPTIONS FROM THE TOOLS MENU

3

SELECT GENERAL FROM THE ENVIRONMENT FOLDER

4

CLICK ON THE DROP DOWN LIST OF “AT STARTUP” OPTION

5

FROM THE DROP DOWN, SELECT “OPEN OBJECT EXPLORER AND NEW QUERY

6

CLICK ON OK AND CLOSE AND RESTART SQL SERVER MANAGEMENT STUDIO

 

After both the above changes are implemented, SQL Server Management Studio will need to be closed. When you launch SQL Server Management after making the above changes, it will bypass the security dialog box and will connect you straight to the database you want to work with and will also launch query editor along with object explorer. This could potentially save the time of a developer by not having to go thru multiple manual steps.

 

SQL SERVER MANAGEMENT STUDIO CAN OPEN UP 4 DIFFERENT TYPES OF WINDOWS AT STARTUP

1.       OBJECT EXPLORER (This is the default window)

2.       NEW QUERY WINDOW

3.       OBJECT EXPLORER AND QUERY EDITOR (You should select this for this example)

4.       EMPTY ENVIRONMENT

 

Important: You will need to close SQL Server Management studio and launch it again for the above changes to take effect.

 

DamirB-BlogSignature

Comments (0)

Skip to main content