Monthly Archives: August 2018

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.

First time speaker at Denver’s SQL local group

06/21 was the date that marked my career as data professional, for the first time ever I decided to participate as technical speaker in United States. Probably this is not a big deal for most people, but it is for a Guatemalan boy whose started dreaming as big as possible and giving what it takes to make his dreams comes true.

Once I heard that there are three types of people in this world, those who make things happen, those who watch things happen and those who ask what happened, I definitely consider myself the first one, fighting for my dreams and giving 101% to make things happen! Because most of the time, you have to make the things happen to you!

I started my speaker journey in my home country Guatemala, regardless I have been living in United States for more than a year I made the decision to submit a couple of sessions to participate as technical speaker in Guatemala’s SQL Saturday from this year. It was my first time as speaker, but I felt very confident because I couldn’t have chosen a best scenario as my home country to start, I was going to be presenting between colleagues and coworkers. The experience was great and I felt I was ready for the next challenge, to speak in United States.

With the help of Marc Beacon (@marcbeacom), I was introduced to the local group organizers during the Colorado Springs SQL Saturday, and volunteer myself to participate as speaker in the next monthly meeting in Denver. I was not sure if I was going to be accepted, I’m very new in the US #SQLFamily but my surprise was that SQL local groups are always looking for local speakers, so it was a perfect opportunity to take my shot and do my presentation in front a total different crowd.

This was a whole new experience for me, presenting in a different language without knowing a single person from the audience it was quite a challenge but I know I was there to talk about something I know very well it was just a matter to work on my anxiety. I couldn’t ask for a better audience the people who attends to the Denver’s SQL local group is simply the best!!

Probably I made a few mistakes on the road but it was ok, at the end of the day it was my first time speaking in this local group and I have some merging for improvement.

By the end of the session I was so glad to know I reached my goal, I was a little bit nervous about the Q/A part of the presentation but my biggest fear ended it been the most interesting part. The participation of Kellyn Pot’Vin-Gorman (@DBAKevlar) was key and truly wonderful, we made a lot of analogies about how ADD and AWR works in Oracle and how similar Query store is to these Oracle tools. For those doesn’t know her, she is a very recognized Oracle and SQL Server DBA and because I’m also a multi-platform DBA you can guess we had a lot of fun talking about SQL Server and Oracle performance tools.

Microsoft Tech Center – Denver

Marc Beacom making the monthly announcements

Without any doubt, It was a fantastic experience, learned a lot and also this experience helped me to have some kind of solid ground to embark in this new experience as speaker in United States. People learned from what I presented that day, and that was my goal, take the opportunity to speak in public and give something back to the #SQLFamily.

Hopefully this is not going to be the last time, I’m looking forward to participate in more monthly meetings and also who knows probably be part of the next Denver SQL Saturday to happen in September who will have the participation of big names as Bob Ward, Glenn Berry, Jess Borland, Tracy Boggiano, Kellyn Pot’Vin-Gorman, and more.

Thanks for reading!

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.