Friday, September 23, 2016

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.

Sunday, September 18, 2016

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





Saturday, September 17, 2016

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.








Saturday, September 10, 2016

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