Saturday, February 17, 2018

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.

Friday, February 2, 2018

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.


This displays the terminal.


The following version of Power Shell is supported:


Thursday, January 25, 2018

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.


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


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


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


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
-- TODO: Set parameter values here.
Set @CustomerID='ALFKI'
EXECUTE @RC = [dbo].[CustOrderHist]
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:


You could also return a single row of data as shown by running this query:
SELECT [ProductID]
FROM [Northwind].[dbo].[Products]
WHERE ProductID=5


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]
FROM [Northwind].[dbo].[Products]


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]
FROM [Northwind].[dbo].[Products]
For JSON AUTO, Root('Products')


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:

If you are motivated to learn there are more here:

Wednesday, January 24, 2018

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

Click Connect and you are connected as shown.


Now instead of localhost, try the IPAddress as shown.


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.


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.


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.


Click Connect and you are connected as shown.


That is all there is to it. 

Monday, January 22, 2018

Restoring legacy database to SQL Server 2017 using Microsoft SQL Operations Studio

You can restore a database to SQL Server using the SQL Server Management Studio(SSMS). It is a feature rich tool for restoring and backing up databases. Besides restoring and backing up SSMS can carry out a ton of other tasks.

Here is an example of using SSMS to restore a database to SQL Server.

Why use Microsoft SQL Opertions Studio?

For one thing it has small foot print and it is free (preview now). It is lightweight and can do only a few of the things. You can restore a database with fewer clicks than with SSMS.

On my Windows 10 Professional, I have installed SQL Server 2017 Developer edition. I will restore the Northwind database from its backup file on CodePlex. Being a preview, Microsoft SQL Operations Studio has a few kinks and I have a workaround to bypass the kink and get the result.

Watch the video for the restore opeation:

Restore operation using Windows PowerShell

Friday, January 19, 2018

Generating query output in JSON is easy using Microsoft Operations Studio

Installing the January Preview (latest as of now) Microsoft SQL Operations Studio is easy. Follow the steps here .

Connecting to SQL Server 2016 is explained in details here.

Connection is very easy, especially if you are using Windows Integrated authentication as shown here:


This screen in the January Preview is not quite what I expected. The two tabs (RECENT CONNECTIONS which is active and SAVED CONNECTIONS greyed out) in the background does not do anything.

Just click Connect and you are connected as shown.


Either we can run a new query or browse and get a saved query (with extension .sql).  I am trying a saved query from an earlier connection with an earlier version.


The query is displayed in the MSFT SQLOPS application as shown. But I see a reddish no-no on the connection on the left.

Anyway, just click Run to execute the query and the response appears in a pane below as shown.


Since this is a query from a query using another connection, the connection screen is displayed as shown.

Enter the conneciton information as before. The data entered is accpeted and the query is executed as shown.


Now click on the Save as JSON on the right. You will save it to the default folder that opens. After saving to folder JSON fromatted query result is displayed as shown.


It could not be simpler than this, really.

Read related article here:

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 . T...