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

String_split() is a new function in SQL Server 2016

When compared to an earlier version, SQL Server 2016 has two new string functions shown in the next image.

String2016.png

The syntax for the new function:

STRING_SPLIT ( string , separator )

Let us take this string:

'quote, substring, toLowerCase, toUpperCase, charAt,
      charCodeAt, indexOf, lastIndexOf'

Now write the following code in the query pane of SQL Server 2016 as shown. When the query is evaluated we see that the string is split at the comma (,) as shown. The white spaces are preserved.

The latest update to SQL Server Management Studio does not really update

Most recent upgrade to SSMS was released.

Download the latest here (https://msdn.microsoft.com/en-us/library/mt238290.aspx)

SSMS-Setup_ENU.exe:

New build is supposed to be this:
SQL Server Management Studio (16.4.1, build version 13.0.15900.1).


You probably had this version (13.0.15600.2) like I had.

SSMS_9_23_2016

The latest has some bug fixes as well as some new changes.

The link above gives access to the changelog links to issues fixed and new items or features.

These are new in this update:

New 'Read-SqlTableData', 'Read-SqlViewData', and 'Write-SqlTableData' cmdlets to view and write data using PowerShell.
Trello Read-SqlTableData Card
Microsoft Connect item #2685363

New 'Add-SqlLogin' cmdlet to enable new login management scenarios using PowerShell.
Microsoft Connect item #2588952

I downloaded and executed the downloaded program. I saw the usual splash screen such as this one:

After restarting it takes a while to finish the uploading and completing. I am in no great hurry but for a busy shop this is some what of a problem.

But after all this the program has not made any change to SSMS. This is what I see in the SSMS  | About after upgrading.

SSMS_9_23_2016_NewIsIt
The build version should have been this:
build version 13.0.15900.1
but it is still the old one, 13.0.15600.2

Hello! Microsoft

Building up a table in SQLite using Visual Studio 2015

SQLite's Build Table...creates a UI interface to design the columns to create a script for creating a table.

It will be assumed that you have installed the necessary extensions.
-
If you do not have SQLite follow this link.
If you do not have Visual Studio 2015 Community go here.
--
This is how the SQL Compact/SQLite Toolbox connections look like. There are three nodes, Tables, Views and Triggers and they are all empty.

Table_SQLite_00

Right click table to get this drop-down

Table_SQLite_01

Click on Build Table(beta)...
Build Table window appears as shown.

Table_SQLite_02

Give a name to the table(herein Sept22) and insert the columns you want in the table as shown where the Fname column with nvarchar() data type has been added (still incomplete).

Table_SQLite_03

We have finished adding a few more columns as shown.

Table_SQLite_04

Now the option is to click Script!. Click Script!.
The script is created as shown.
---
-- Script Date: 9/22/2016 6:07 PM  - ErikEJ.SqlCeScripting version 3.5.2.58
CREATE TABLE [Sept22] (
  [Id] INTEGER NOT NULL
, [Fname] nvarchar(50) NULL
, [LName] nvarchar(50) NOT NULL
, [Age] int NOT NULL
, CONSTRAINT [PK_Sept22] PRIMARY KEY ([Id])
);
-----
Now all that remains is to run this code. Click on the icon for execute as shown.

Table_SQLite_05

This creates the table as shown.

Table_SQLite_06

Of course the table is empty it has only the meta data that we have incorporated.

String_escape() new in SQL Server 2016

When compared to an earlier version, SQL Server 2016 has two new string functions shown in the next image.

String2016.png

The syntax for the new function:
STRING_ESCAPE(text, type)

text is nvarchar expression representing the object to be escaped and type is the the rule to be applied and presently the rule is only for type 'json'. This was required because SQL Server 2016 supports JSON and some of the special characters in the data may not give the correct JSON format.

The return type of json supported are shown in this table.

String_escape_0

Some of these tested in the SQL Server Management Studio 2016 are shown here:

Form Feed is seldom used and I do not know what the purpose is and the same with backspace.

Read more here:
 https://msdn.microsoft.com/en-us/library/mt684589.aspx

SQLite using Visual Studio Community 2015 Update 3

SQLite is a relational database well suited for mobile applications.

SQLite was described in a previous post here. As the Internet of Things is the trending area on the Internet, SQLite with its no administration feature is well suited. It is also the relational database for smart devices, cell phones, TVs, cameras etc. If you want fast and reliable data provider than SQLite is recommended. The other option which is gaining in importance and a HTML5 feature supported by many browsers is the IndexedDB. IndexedDB is not relational.

sqlite_00

You can download SQLite from here:

http://sqlite.org/download.html

There are many options depending on your operating system. These are the options for Windows alone.

sqlite_01

Connecting/Accessing SQLite from Visual Studio 2015 Community (herein VS for short).

SQLite has no GUI for its management and VS is well suited.

Launch Visual Studio 2015 Community (Update 3 used here).

From Tools click on Extensions and Updates.

Well I seem to have installed the  SQL Server Compact/SQLite toolbox

sqlite_02

Indeed, I can see the SQL Server Compact/SQLite Toolbox as shown. However I see only (SQL Server Compact 4.0) database.

sqlite_03

Right click Data Connections to display the menu as shown.

sqlite_04

Click Add SQLite Connection to open the detailed window for adding a SQLite Connection as shown.

sqlite_05a

I try to Browse for one and It is looking for the shown file types in the default location.

sqlite_05

I try to create one assuming that this toolbox  interface allows me to create. I click the Create... button in the Add SQLite Connection window shown earlier.

It seems to create the file in this directory.

C:\Users\Jayaram\Documents\Blog2015\HodentekMSSS2015\DownloadedPrograms\C#\CSWindowsStoreAppAccessSQLServer\AccessSQLService\bin\System.Data.SqlServerCe.Entity

I choose a different directory (C:\Users\Jayaram\Documents\Blog2016\HodentekMSSS2016\SQLite) with a file name, Sept17-2016) as shown.

sqlite_06

I try to test the connection and it looks like it succeeded. But I do not see all the information except for the few shown in the next image.

sqlite_07

I click OK and close the window. The SQLite connection I created gets added to the Data Connections.

sqlite_08

I expand the Sept17-2016.db file and I see the following default objects (all empty): Tables, Views and Triggers

sqlite_09

Come back to this blog for more on SQLite.

SQLite using Visual Studio Community 2015 Update 3

SQLite is a relational database well suited for mobile applications.

SQLite was described in a previous post here. As the Internet of Things is the trending area on the Internet, SQLite with its no administration feature is well suited. It is also the relational database for smart devices, cell phones, TVs, cameras etc. If you want fast and reliable data provider than SQLite is recommended. The other option which is gaining in importance and a HTML5 feature supported by many browsers is the IndexedDB. IndexedDB is not relational.

sqlite_00

You can download SQLite from here:

http://sqlite.org/download.html

There are many options depending on your operating system. These are the options for Windows alone.

sqlite_01

Connecting/Accessing SQLite from Visual Studio 2015 Community (herein VS for short).

SQLite has no GUI for its management and VS is well suited.

Launch Visual Studio 2015 Community (Update 3 used here).

From Tools click on Extensions and Updates.

Well I seem to have installed the  SQL Server Compact/SQLite toolbox

sqlite_02

Indeed, I can see the SQL Server Compact/SQLite Toolbox as shown. However I see only (SQL Server Compact 4.0) database.

sqlite_03

Right click Data Connections to display the menu as shown.

sqlite_04

Click Add SQLite Connection to open the detailed window for adding a SQLite Connection as shown.

sqlite_05a

I try to Browse for one and It is looking for the shown file types in the default location.

sqlite_05

I try to create one assuming that this toolbox  interface allows me to create. I click the Create... button in the Add SQLite Connection window shown earlier.

It seems to create the file in this directory.

C:\Users\Jayaram\Documents\Blog2015\HodentekMSSS2015\DownloadedPrograms\C#\CSWindowsStoreAppAccessSQLServer\AccessSQLService\bin\System.Data.SqlServerCe.Entity

I choose a different directory (C:\Users\Jayaram\Documents\Blog2016\HodentekMSSS2016\SQLite) with a file name, Sept17-2016) as shown.

sqlite_06

I try to test the connection and it looks like it succeeded. But I do not see all the information except for the few shown in the next image.

sqlite_07

I click OK and close the window. The SQLite connection I created gets added to the Data Connections.

sqlite_08

I expand the Sept17-2016.db file and I see the following default objects (all empty): Tables, Views and Triggers

sqlite_09

Come back to this blog for more on SQLite.

Power BI Charts 3: Box Plot

This kind of data plot is required while showing statistical analysis of distributed financial data. It is usually called Box plot and sometimes Box and Box & Whisker plot.

Here is an example of how it looks:

BoxPlot_00

The plot represents some Financial data (Amount) over 4 quarters for three years.

The details of what the plot shows is in the next image:

BoxPlot_01

Here are the details of this plot:

This data is from the demo database on SAP SQL Anywhere 17 server. The data visualization is carried out by using R Script in Power BI.

Power BI Charts 2: Histogram

This is a also a very common basic chart. Histogram plots the frequency of occurrences. The input required for this is just one column of data.

Here is an example of a Histogram of quantity of items ordered from the Northwind databases' [Order Details] column.

Histogram.png
Note that R does not interpret Quantity as a numeric and further processing of data is necessary before it can be plotted using R Script in Power BI.

Note: Without the unlist() dataset1 will be double. With unlist() it becomes a numeric.

===============
dataset1=as.numeric(unlist(dataset))
hist(dataset1, main="Distribution of Quantity")
=============

Source of data: SQL Server 2012 | Northwind Database
Power BI: August 2016 Update

Power BI Charts 1: Strip Chart

This is a basic chart available in Power BI using R Script. It produces a plot of data, withe each data point a small rectangle along a single axis. The input required for this is just one column of data.

Here is an example of a strip chart of financial data with the Amount column in a table plotted along a line.

Source of data: SAP SQL Anywhere 17
Power BI: August 2016 Update

R Script for this is: straipchart(dataset) for the dataset shown in the image.

You can add a method to the above to show some separation between the data points as in the next image.

 

Enabling R Service and Verifying Local R script execution

There are several steps  involved in setting up R Services work with SQL Server 2016() and one of which is enabling R Service and Verifying Local R script execution.

In my previous post (http://hodentekmsss.blogspot.com/2016/07/problem-enabling-r-service-in-sql.html) I have completely described the installation of SQL Server 2016 Developer's edition during which the R Server was also installed as shown here:

Rserver_00
In order to run R in SQL Server 2016 you need to use the sp_execute_external_scripts stored procedure. In order to do use this, you need first set the external scripts enabled option, which by default is OFF.
The suggested way is to configure by running the following statement:
============
sp_configure 'external scripts enabled', 1; 
RECONFIGURE; 
============
When run on the named instance on which R Server is also configured (Hodentek8\Ohana) we see the following result:

sp_config_00

Let us see if  running the sp_execute_external_script written in R works:

exec sp_execute_external_script @language=N'R',
@script=N'OutputDataSet <-InputDataset',
@input_data_1=N'select 1 as hello'
with result sets (([hello] int not null));
go

This returns the following error:

Msg 39011, Level 16, State 1, Line 4
SQL Server was unable to communicate with the LaunchPad service.
Please verify the configuration of the service.

You can open up Services window from Control Panel and start the service. This is the Launch Pad services to work with Analytics.

After this run the R script again as shown.

Booyah! It works! This was the response expected and the stored procedure works.