I recently completed running a series on the “Underappreciated Features of Microsoft SQL Server”, under which I introduced the SQLCMD mode available within the SQL Server Management Studio. SQLCMD mode is a means of using SQLCMD functionality within the SSMS, which would normally have been available on the command prompt.

Now, SQLCMD is a very powerful utility – highly productive and flexible. I learnt that myself when researching for the original post. Here’s one feature that caught my immediate attention, and I won’t shy away from saying that it has made me use SQLCMD even more!

Default Editor on SQLCMD

The need of a script editor in SQLCMD

SQLCMD, as we all know, is used to run T-SQL scripts against a SQL Server instance via the command line interface. For example, I can run the following query as shown below:

USE AdventureWorks2008R2
SELECT BusinessEntityID,JobTitle FROM HumanResources.Employee
GO

This works great on small queries, as shown in this example. However, what about complex or queries running into multiple lines? Those would become difficult to write on the command line. That leads us to a the need of a script editor in SQLCMD.

The default Editor of SQLCMD

SQLCMD comes with a set of highly commands. One such command is “[:] ED”. If we type in “ED” on the SQLCMD prompt and hit the Enter key, we find that it launches the default editor of the command prompt – the very familiar – blue & white interface!

Using the set script editor in SQLCMD

So, now that we have our SQLCMD editor open, we can write our multi-line/complex queries that we would want to run as a batch. Let’s use the following formatted query as an example:

-- Declare a Table variable, and insert some data into it
DECLARE @MyTable TABLE (ID INT IDENTITY (1,1), MyName VARCHAR(100))

INSERT INTO @MyTable
SELECT msc.name + CONVERT(VARCHAR(10),ROUND(RAND()*1000,0))
FROM msdb.sys.objects mso (NOLOCK)
CROSS JOIN msdb.sys.columns msc (NOLOCK)

SELECT * FROM @MyTable
GO

Once you save & exit out of the editor, the entire query is available in the SQLCMD editor:

 

Hit “Enter” and watch the query zoom by.

Setting a default SQLCMD Editor

Now, not everyone would want to use the default DOS editor. Lots of better editors are now available and in use, and here’s how you can set your environment to use this editor as default in SQLCMD:

1. Navigate out to Control Panel –> System
2. Click on “Advanced System Settings”
3. Click on the “Environment Variables” button in the “Advanced” tab
4. Add a user variable (to keep this setting restricted to your user/login):

Variable Name: SQLCMDEDITOR

Variable Value: Executable name (e.g. NOTEPAD.EXE)

Now, launch the editor from the SQLCMD, and NOTEPAD would open up as the query editor. Once the query editing is complete, simply save & exit out of the editor to return back to SQLCMD.

  

Clean Up – Where do my SQLCMD files go? 

We saw above that each of these SQLCMD editors create temporary files which are edited by the users. Where do all of these files go? They simply go to the path specified by the environment variable %TEMP%. Normally, after the SQLCMD session ends, this file should be removed. In case it is not removed, one can always remove this file from the user’s temporary folder.

Reference: http://msdn.microsoft.com/en-us/library/ms162773.aspx

Do let me know if you have any other cool SQLCMD tricks. I will publish them on my blog with due credit.

Until we meet next time,

Be courteous. Drive responsibly.