SQLCMDEDITOR – Productivity Feature – Edit SQLCMD scripts in Notepad!


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

image

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!

image

image

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

image

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

image 

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)

image

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.

image  

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.

Advertisements

4 thoughts on “SQLCMDEDITOR – Productivity Feature – Edit SQLCMD scripts in Notepad!

  1. Rui Carvalho

    command line tools are always under appreciated on windows and sqlcmd is really usefull.
    with this tip, it is really even more usefull!
    thanks!

    Like

    Reply
  2. Jacob Sebastian

    I think people started their career in the DOS era might appreciate the command line tools more [I am one of them :-)]

    Like

    Reply
  3. Rui Carvalho

    Jacob, don’t say it too loud, people will think you’re extremely old 😉

    btw, if we have a deeper and partial look at our applications we can see that user interfaces are not well adapted in general : they are most of the times not enough efficient (compared to cmd tools) for admins or hardcore users, and in the other side, too much complicated for generic users. Global interfaces that are supposed to handle anything are finally a fail (most of the times)…

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s