Getting Started With SQLCMD

Hi All,

Recently, I was given a task that was a bit complex, as this involves Unix, Autosys, and calling SQL Server from Unix. In this task, my major challenge was how to connect to SQL Server instance from the Unix machine. Frankly speaking, this gave me goose bumps, as I have never done anything like this before.

In my previous experience, I worked with the Unix environment and connected Oracle and MySQL DB instance from Unix. But calling Microsoft Technology from UNIX was like a big OMG.

I searched a bit and found that SQL Server 2016 is now available with Unix version, and we can install this database on Unix as well, but previous versions are not supported. So, what is used to connect SQL Server from the command prompt (even in windows environment)? I searched and got a list of tools in which SQLCMD was most promising as this was provided by Microsoft it self.

Here, I'll be explaining little bit about SQLCMD, how to get started, and how to write your first query and get a result. So let's get started!

Installing SQLCMD

1. If SQL Server is not installed on your local machine, then first install Microsoft® ODBC Driver 11 for SQL Server — Windows, with filename msodbcsql.msi. Download from here.

2. Install Microsoft Command Line Utilities 11 for SQL Server with filename MsSqlCmdLnUtils.msi. Download from here.

3. Open command prompt and type sqlcmd -? (if a list of options appears, then we are good)

And that's it. We are done installing.

Connecting to SQL Server Instance and First Query

There are 2 ways to connect to SQL Server instance i.e. with Windows Authentication and with SQL Server Authentication.

As you can predict, in the Unix environment, we will not be able to use the Windows Authentication method, so I'll be mentioning both methods.

Option 1: Windows Authentication (Works only in Windows environment)

C:\> sqlcmd -S <serverInstanceName> -E

Ex. C:\> sqlcmd -S QTDFEDBDV02\Feeds2 -E

Option 2: SQL Server Authentication (Works on Windows and Unix environment both)

C:\> sqlcmd -S <serverInstanceName> -U <username> -P <password>

$ sqlcmd -S <serverInstanceName> -U <username> -P <password>

Once you connect, you'll get screen like the screenshot below. 1> means first line. As you press enter, the line number increases. You can start writing your TSQL from this line.

NOTE: When using sqlcmd to write TSQL, GO in last is must. As soon as you press enter after "GO", TSQL will execute and the result will be displayed:

So, folks, that's it. Please let me know your thoughts in comments and share this with others. Also, you are welcome to correct me if something is wrong in this or if something can be improved or added. Thank you!