Basic SQLCMD–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I had the need to connect from the command line recently, and decided to make a quick post on using SQLCMD, as I had an issue.

SQLCMD is a command line utility that comes with SQL Server. I know many people don’t use command lines, but they are handy at times. I recently opened a command prompt.

2016-04-06 12_47_33-Photos

I then typed SQLCMD. After a delay, I got this:

2016-04-06 12_51_23-Photos

The issue here is that I don’t have a default instance on this machine. All of mine are named. I need to provide a –S parameter, with a server name (and possibly instance name).

2016-04-06 13_01_33-Photos

I do that and I’m connected. By default, SQLCMD (and osql) try to use Windows Auth. The 1> indicates that the utility is ready for T-SQL queries. You need to know your language here as there’s no help.

I can enter code, and check my user name. I do this, and get a 2>. The end of a batch is indicated with “GO” and this will execute the batch. You can see how this works below:

2016-04-06 13_01_47-Photos

I can use this to make my code easier to read. I can format code as I would in an editor, though be aware you can’t go back and edit previous lines.

2016-04-06 13_07_03-Photos

If I enter go, I’ll get this:

2016-04-06 13_07_15-Photos

Not so easy to read. I have to scroll up to even figure out what the display is:

2016-04-06 13_07_26-Photos

As you can see, using SELECT *, or retrieving too many columns make results hard to read. You would to wise to pick only those columns you need to return.

To leave SQLCMD, you can type exit, which will return you to the command prompt.

2016-04-06 14_00_07-Start

This is a short look at SQLCMD. The older, osql, utility functions the same way, and both are good, lightweight ways to connect to your SQL Server instance.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

4 Responses to Basic SQLCMD–#SQLNewBlogger

  1. Great article Steve. One thing you might find useful is if you type :ed it brings up the curent command buffer (everything since the last GO batch separator) in notepad. I think if you do it immediately after a batch it loads the last batch you sent.

    If you don’t like notepad, and want it to load the query in vim or notepad ++ set the SQLCMDEDITOR environment variable. See this article: http://www.haidongji.com/2005/10/25/change-sqlcmd-default-editor/

    Note I have no idea about how to get that working with the new ubuntu subsystem on windows 10. That assumes the regulat windows build of vim.

    • way0utwest says:

      Thanks, I’ll play around with those. I’m trying to decide what editor i want outside of SSMS. I may go with notepad++, though I don’t love it.

  2. If you do go notepad++, poor mans t-sql formatter is a great plugin.

Comments are closed.