Category Archives: Tools

Starting SSMS with a specific connection and script file

Have you ever wanted SSMS to start and automatically and establish a connection to your DBA \ hub server and have your daily routine queries already open ready to run?

Good news, that is totally possible. SSMS actually uses the SSMS.exe in the background which has multiple options such as connecting to a specific SQL Server instance and opening a file. All these options are accessible through a few very simple command line parameters, if you are familiar with SQLCmd these parameters will like be really similar to you.

Let’s explore the different options available through command line, open PowerShell or CMD and running the following command:

 ssms -help 

As you may noticed a pop-up window showed up displaying the multiple options as server name, database name, user password, file name, log name, etc..

Now that we know the parameters, let’s have fun with these parameters!! Do you remember what I just mentioned at the beginning of this post? Let’s do a test, let’s call SMS from PowerShell and ask it to open a connection to a specific server (MASTER01 in this case) loading a SQL script file which contains a daily script:

Click for fullscreen

Ok, but you know what let’s make this more interesting, because we know already SSMS.exe it’s an executable and we have all these cool parameters to play with let’s modify the existing SSMS shortcut this time we will make SSMS to load a solution containing multiple SQL script files:

Click for fullscreen

As you may noticed from animation, I just added a set of specific parameters to the existing text, to be specific this is what I just added:

 F:\DailyChecks\DailyChecks\DailyChecks.ssmssqlproj 
-S master01 
-d master 
-nosplash

This modification will make SSMS to start not showing the traditional splash screen (because yes I want to save a second of my time), it also pre-loads a SQL Server solution which contains a collection of scripts I intents to run from my SQL Server central instance (master01). In case I choose the “master” database as default database to connect to but as you can figure out at this point all these options can be changed 🙂

So this is how you can set a specific configuration for SSMS to use every time you start this application, I consider this tip a very handy trick you can use to save yourself some time.

Stay tuned for more DBA mastery tips.

I’m very experienced multi platform Database Administrator (Oracle, SQL Server, MySQL), working for large scale US based companies and some other companies around the world.

I have multiple Microsoft SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), and currently taking the Microsoft Data Science program.

SQLCMD mode in SSMS

I really like Microsoft philosophy nowadays, they are putting a lot of time developing good open source tools like SQLCLI, SQLOps and Visual Studio Code, those tools has something in common “a built-in terminal” a multi-platform and code geek like me really appreciate those details. It is pretty neat to use the built-int terminal to do stuff without leaving your development enviroment.

This functionality is also available on SSMS (in some way), it is called SQLCMD mode. This feature allows you to interact directly with Windows command line without leaving your SSMS environment, for example long time ago I had the need to generate a SQL file from an output of a query, I knew I can use SQLCMD for such thing but I didn’t want to create an external script to be call from command … so I did my research and figure out SQLCMD mode in SSMS can do such thing without having to run outside SSMS.

Go to Query in main menu, then click SQLCMD mode to enable it

Here is how you can enable SQLCMD mode in SSMS:

Alright! so, now let’s do a quick example. Imagine you don’t want to lose the list of roles assigned to all your database users for a specific database, in order to script out the roles we will run a query and generate an output directly to Windows having the T-SQL script ready to run when needed.

Here is the T-SQL code:

BEGIN
    SET NOCOUNT ON
    :OUT F:\MyDB_Roles.sql

    DECLARE @Role varchar(255)
    SET @Role= '%'

    -- Add Users to roles
    SELECT
        'EXEC sp_addrolemember ''' + RTrim(r.name) + ''',''' + RTrim(Coalesce(l.name,u.name)) + '''' as '-- Add Role Users'
    FROM dbo.sysusers u
        INNER JOIN sysmembers m
        ON u.uid = m.memberuid
        INNER JOIN sysusers r
        ON m.groupuid = r.uid
        LEFT OUTER JOIN master.dbo.syslogins l
        ON u.sid = l.sid
    WHERE r.issqlrole = 1
        AND u.name <> 'dbo'
        AND r.name LIKE '%'
    ORDER BY r.name,u.name

    PRINT '-- End of script'
END

Note the third line, I’m using the :OUT command which will take care of redirecting the query result into a SQL file within Windows:

:OUT F:\MyDB_Roles.sql

Once the query is executed, here is how the SQL File output file looks from Visual Studio code:

As you can see this is a normal T-SQL script generated from SSMS, it contains the roles we wanted to script out from our database. As you may realize by now this is a very handy option in SSMS, and to be fair with SQLCMD mode this is just a simple use case there is more scenarios where this SSMS functionality can help you to achieve very neat things.

For more information about other parameters for SQLCMD,  please check this link from Microsoft docs.

Stay tuned for more DBA mastery tips!

I’m very experienced multi platform Database Administrator (Oracle, SQL Server, MySQL), working for large scale US based companies and some other companies around the world.

I have multiple Microsoft SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), and currently taking the Microsoft Data Science program.