You need Python to install the command-line query tool mssql-cli

In my previous post you have seen how to download mssql-cli. Installing mssql-cli requires a Python command-line tool for managing Python packages.

In this post you will learn downloading and installing Python that will enable to use PIP.

You downlaod Python from here:

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

This site has a vast number of downloads not only for Windows but also for other platforms. For Windows 10 there are a number of them.

I installed the following:

python-3.7.0b2-webinstall

Python_00.png

Double click the downloaded file to begin installation.

Python_0

Python_2

You get these files into your computer:

Python_4

The PIP tool is in the Scripts directory in the above.

On a Windows 10 computer the following are installed in the All Programs

Python_6

You can get help on PIP as shown here by launching Python 3.7 from the above shortcut and when you are in the Python (>>>) use HELP:

Python_5

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/

Get started with latest release of Microsoft SQL Operations Studio

Read my previous articles (9) on previous versions.

Download the February (latest) release of Microsoft SQL Operations Studio here.

There are couple of enhancements over the previous release. The following has been fixed or added:

Added Auto-Update Installation feature
Added Connection Dialog ‘Database’ Drop-down
Added functionality for new query tabs keeping active connection
Fixed bugs in SQL Editor and auto-completion

This is the version I have now.

When I update, my version is:

When I launched my previous release, I could access the latest. Clicking on this I could install the latest release.

Set up preparatory stage.

I did not see a drop-down of all databases. It did not find the databases. It just reverted to default in the drop-down.

However, it could keep two query windows open at the same time, maintaining two different connections.

Accessing POWER SHELL in Microsoft SQL Operations Studio

You can access the console in Microsoft SQL Operations Studio by clicking this option in the User Interface as shown.

PS_SQLOPS_0.png

This displays the terminal.

PS_SQLOPS_1.png

The following version of Power Shell is supported:

PS_SQLOPS_2.png

Executing a stored procedure in Microsoft SQL Operations Studio(SQLOPS)

Executing a stored procedure is as easy as running query in SQLOPS.

For example, I restored the Northwind database from a backup file from CodePlex site to the default instance of SQL Server 2017 Developer's Edition.

I connected to the SQL Server 2017 in Microsoft SQLOPS as shown. Here you can see all the objects in the server.

SP_SQLOPS_0

Right click the stored procedure (herein, dbo.CustOrderHist) to display a pick list as shown.

SP_SQLOPS_1

Click Script as Execute.
A query is created as shown:

SP_SQLOPS_2

In the TODO section add-in the value of CustomerID.
Look up the data type of CustomerID as shown here:

SP_SQLOPS_3

You need to modify this query somewhat to see the results (this is Preview software). Add statements to the SQLOPS created script to read as shown here:
---------------------
USE Northwnd
GO
DECLARE @RC int
DECLARE @CustomerID nchar(5)
-- TODO: Set parameter values here.
Set @CustomerID='ALFKI'
EXECUTE @RC = [dbo].[CustOrderHist]
   @CustomerIDGO
-----------------
Now click Run and you can see the response as shown:

That's all.

JSON response from a SQL Query in SQL Server 2016

JSON is supported in SQL Server and it is very easy to obtain JSON formatted response from a query easily. For example,

while connected to Northwind database you get the JSON formatted data by running a query such as this one using the Products table:

---------------------------------

SELECT        ProductName, QuantityPerUnit, UnitPrice
FROM            Products FOR JSON Auto;

---------------------------------

The response will be as shown:

JSON_0

You could also return a single row of data as shown by running this query:

-----------------------

SELECT [ProductID]
      ,[ProductName] 
FROM [Northwind].[dbo].[Products]
WHERE ProductID=5
For JSON AUTO

-------------------------

JSON_1

The result comes in an array even if the returned data is just one row.

You could remove the array wrapper ([ ]) surrounding the result set by the following query:

-------------------

SELECT [ProductID]
      ,[ProductName] 
FROM [Northwind].[dbo].[Products]
For JSON AUTO, WITHOUT_ARRAY_WRAPPER

---------------------------------------

JSON_2

Notice that the array wrapper is gone in the result set.

You can further qualify where the data (Which table, for example) by adding a 'root' element by issuing this query:

------------------------

SELECT [ProductID]
      ,[ProductName] 
FROM [Northwind].[dbo].[Products]
For JSON AUTO, Root('Products')

------------------------

JSON_3

You are not allowed to use WITHOUT_ARRAY_WRAPPER and Root in the same sql query because you get the following message, if you do:

Msg 13620, Level 16, State 1, Line 5
ROOT option and WITHOUT_ARRAY_WRAPPER option cannot be used together in FOR JSON. Remove one of these options.

I am using SQL Server 2016 Developer's edition on my Windows 10 Pro laptop.

If you are new to JSON read the following:

http://hodentekhelp.blogspot.com/2014/11/how-do-you-work-with-javascript-object.html

If you are motivated to learn there are more here:

http://hodentekhelp.blogspot.com/search?q=json

Connecting to SQL Server 2017 using Microsoft SQL Operations Studio with an IP Address

Take for example, SQL Server 2017 Developer's edition. It is the latest release version. When you install it you can only access using 'localhost' as shown.

Enter localhost

SQLServrConfig0

Click Connect and you are connected as shown.

SQLServrConfig00

Now instead of localhost, try the IPAddress as shown.

SQLServrConfig4

Trying to Connect you get the following message.

The reason for this is that the Protocols for MSSQLSERVER (the instance we are considering) has only Shared Memory enabled as in the SQL Server Configuration Manager.

SQLServrConfig1

The error message indicated that it was due to Named Pipe provider error.

Let us enable Named Pipes protocol in SQL Server Configuration Manager as shown.

SQLServrConfig2

Make a note that when you change (enable/disable), you need to stop and start the server. You can do this in SQL Server Configuration Manager.

Now attempt to connect using IPAddress as before.

SQLServrConfig4

Click Connect and you are connected as shown.

SQLServrConfig3

That is all there is to it.