You have a new updated mssql-cli, the interactive command-line query tool for SQL Server

The public preview was announced in December last year. Now there is an update.

Read here.

You can download mssql-cli from here:


Using mssql-cli you get to do all this:

    T-SQL IntelliSense
    Syntax highlighting
    Pretty formatting for query results, including Vertical Format
    Multi-line edit mode
    Configuration file support

You can install mssql-cli if you have Python on your computer. For Windows you can install Python from here and make sure it is added to your PATH.

https://www.python.org/downloads/

Starting SQL Server with minimum configuration

The option to start up Microsoft SQL Server when you have configuration problems is to use the -f start up option. With this option SQL Server start with a minimum configuration placing the server in single-user mode.

Be aware of the following in this mode:

• Only a single user can connect, and the CHECKPOINT process is not executed.
• Remote access and read-ahead are disabled.
• Startup stored procedures do not run.

Use the sqlcmd utility and the dedicated administrator connection to connect to SQL Server.

You can use the SC command line utility as you can use the start up parameters. SC is a command line program for communicating with the Service Control (SC) Manager and Services.

If you do not know how to use SC (Services Control Manager) review this post.

If you now start the SQL Server with a - f option the server with minimum configuration as shown:

I have a named instance of SQL Server 2016 called OHANA on my computer (Hodentek8) and I will now start it with minimum configuration by running the following. I have stopped this server before running the code:

 StartWithMin.png

 Verify with the Task Manager (there are other ways as well, Task Manager is open now).

StartWithMin_2

How do you query a database using PowerShell?

You can invoke the SQLCMD from PowerShell with the invoke-sqlcmd commandlet.

MSDN defines Invoke-SQL CMD thus,

"Invoke-Sqlcmd is a SQL Server cmdlet that runs scripts that contain statements from the languages (Transact-SQL and XQuery) and commands that are supported by the sqlcmd utility."

Now as to the exact procedure, you need to get to Powershell with the SQL Server snap-ins.

If you run the SQLPS.exe which is installed with your SQL Server installation(SQL Server 2012 for example) then you have got yourself a good start.

 

Double clicking SQLPS.exe in your computer will bring up the PowerShell command line for SQL Server as shown for both 2012 and 2016 CTP2 versions of SQL Server.

 

 

 

sqlps2012.png

sqlps2016ctp2.png

I will be using the 2012 version for this post.
Now you need to run the invoke-sqlcmd commandlet as shown. This is specific for my installation of SQL Server and you need to change parameters around. I have a SQL Server named instance RegencyPark on my laptop called Hodentek8 and I have chosen Windows authentication for access.
Now I run the following statement:
PS SQLSERVER:\> invoke-sqlcmd -ServerInstance Hodentek8\RegencyPark -Database AdventureWorks2012 -Query "Select top 2 * from Person.person"

I immediately get a response to my query as shown:
-----------------------------------
PS SQLSERVER:\> invoke-sqlcmd -ServerInstance Hodentek8\RegencyPark -Database AdventureWorks2012 -Query "Select top 2 * from Person.person"

BusinessEntityID      : 1
PersonType            : EM
NameStyle             : False
Title                 :
FirstName             : Ken
MiddleName            : J
LastName              : Sánchez
Suffix                :
EmailPromotion        : 0
AdditionalContactInfo :
Demographics          : <IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adv
                        enture-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></Indi
                        vidualSurvey>
rowguid               : 92c4279f-1207-48a3-8448-4636514eb7e2
ModifiedDate          : 2/8/2003 12:00:00 AM
BusinessEntityID      : 2
PersonType            : EM
NameStyle             : False
Title                 :
FirstName             : Terri
MiddleName            : Lee
LastName              : Duffy
Suffix                :
EmailPromotion        : 1
AdditionalContactInfo :
Demographics          : <IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adv
                        enture-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></Indi
                        vidualSurvey>
rowguid               : d8763459-8aa8-47cc-aff7-c9079af79033
ModifiedDate          : 2/24/2002 12:00:00 AM
That is all there is to it.

Querying a SQL Server database with SQLCMD

Commandline access to the program is perhaps one of the nicest features any program can have. They can do most things you want done as long as you understand the switches and when & how to use them. A good wizard can take you to most of the things these days, but cannot beat the commandline. Did I hear you say, short and sweet?

SQLCMD is a commandline tool for working with SQL Server. Well regarding the various arguments(switches) it takes are obtained from this help file.

Switches.png

Now let me start SQLCMD with the switch -S. S stands for Server. In the present case, the SQL Server Instance 'RegencyPark' is installed in my Windows 8.1 laptop named 'Hodentek8'. I will then ask the program to change the database context to 'Northwind', the default is 'master' using the statement 'Use Northwind and Go'. Now follow the script here as I query the Customers table:

C:\Users\Jayaram>sqlcmd -S Hodentek8\RegencyPark
1> Use Northwind
2> go
Changed database context to 'Northwind'.
1> Select * from Customers
2> Where CompanyName like 'A%'
3> GO
CustomerID CompanyName                              ContactName                    ContactTitle
              Address                                                      City            Region
       PostalCode Country         Phone                    Fax
---------- ---------------------------------------- ------------------------------ -----------------
------------- ------------------------------------------------------------ --------------- ---------
------ ---------- --------------- ------------------------ ------------------------
ALFKI      Alfreds Futterkiste                      Maria Anders                   Sales Representat
ive           Obere Str. 57                                                Berlin          NULL
       12209      Germany         030-0074321              030-0076545
ANATR      Ana Trujillo Emparedados y helados       Ana Trujillo                   Owner
              Avda. de la Constitución 2222                                México D.F.     NULL
       05021      Mexico          (5) 555-4729             (5) 555-3745
ANTON      Antonio Moreno Taquería                  Antonio Moreno                 Owner
              Mataderos  2312                                              México D.F.     NULL
       05023      Mexico          (5) 555-3932             NULL
AROUT      Around the Horn                          Thomas Hardy                   Sales Representat
ive           120 Hanover Sq.                                              London          NULL
       WA1 1DP    UK              (171) 555-7788           (171) 555-6750
(4 rows affected)
1>
-----------------------------------------
If you type EXIT for the last line above, you will return to:
C:\Users\Jayaram>

How do I use SQLCMD in SQL Server 2005?

SQLCMD lets you write SQL Commands as well as T-SQL Statements and it is the preferred command-line utility in SQL Server 2005. SQLCMD can also be run within SQL Server Managment Studio and runs in side a query pane by enabling the SQLCMD Mode in SQL Server Management Studio from the Query menu's drop-down list.

One of the requirements for running the SQLCMD from the command line (in a DOS screen) is that the client and the server should be listening on/to the same TCP Port. This can be set using the SQL Server Configuration Manager.

Here is an example fo SQLCMD run from command-line.

Commandline access to the program is perhaps one of the nicest features any program can have and the keyboard wizards love them. They can do most things you want done as long as you understand the switches and when & how to use them. A good wizard does most of the things these days, but cannot beat the command-line.

Did I hear you say, short and sweet?