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:

SQLOPS_JanPublicPreview_01

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.

SQLOPS_JanPublicPreview_02

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.

SQLOPSStudio_03

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

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

SQLOPS_JanPublicPreview_04

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.

SQLOPS_JanPublicPreview_05

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.

SQLOPS_JanPublicPreview_06

It could not be simpler than this, really.

Read related article here:
https://hodentekmsss.blogspot.com/2016/06/exporting-data-from-sql-server-2016-as.html

Starting and stopping a SQL Server using Services Control Manager

SC is a command line program for communicating with the Service Control (SC) Manager and Services. This can be used to start and stop a service including SQL Server.

Typing SC at command line (preferably with administrative privileges) will give you the details of this command.

SC_00

The syntax for using SC is:
        Service Control Manager and services.
USAGE:
        sc <server> [command] [service name] <option1> <option2>...

In this post we will just start and stop the SQL Server 2016 instance  and monitor it on the Task Manager.

The Service Name for the SQL Server Instance (OHANA) can be found by looking at the  details of SQL Server Instance (OHANA) in the Services window in Control Panel shown here:

SC_01

To start the SQL Server instance just type in the command

SC Start MSSQL$OHANA

SC_02

The Server has started. Bring up the Task Manager as shown and verify it has started. Note that the PID is the same.

SC_03

Now that the Server is running we can stop it by running the following command:

SC Stop MSSQL$OHANA

SC_04

Verify it has stopped in Task Manager.

SC_05

Starting and stopping using SC does not produce windows messages that you get while using Control Panel|..|Services. Using Task Manager you do not need to refresh window to see changes.

It is possible to use parameters to start a service as well.

Querying a SQL Server Database in SSMS 2016 using R - 1

I described in an earlier post how to query a SQL Server Database using the ODBC DSN which uses the RODBC package.

After the integration of R with SQL Server 2016, it is possible to query a SQL Server database from within SQL Server Management Studio. However at present there are limitations for certain types of data such as XML.

In order to use R in SQL Server Management Studio make sure;

• The server has started and you can connect to it
• The Launch Pad service has started as shown:

QueryWithR_00.png

If this service has not started you get the MSG 39011 message as shown.

QueryWithR_01

After launching Launch Pad Service the query runs as shown.

QueryWithR_02

The result is to be understood as result set containing 29 rows and seven columns. It has returned informational message.

If you specify the columns your message will have information about those columns only.

QueryWithR_03

This query shows what data types have no support.

QuerywithR_04