How do you change the compatibility level of a SQL Server database?

The compatibility level of a SQL Server database matters since the T-SQL code from a higher compatibility level will not work in a database with lower compatibility level.
How do you change the compatibility level?

Launch SQL Server Management Studio and pick the database for which you want to find the compatibility level.

Right click the database node. From the drop-down pick Properties. In the properties page click Options and you will find the compatibitlity level as shown.

Compatibility_00.png

The TestPubs database (a renamed Microsoft legacy sample, pubs database) has a compatibility level of 110 (SQL Server 2012). This database was brought over to SQL Server 2016 by a process which did not change the comaptibility level.

Click on the Compatibility level and from the drop-down choose the one you want. For example to be cmpatible with SQL Server 2016 you would choose 130.

Compatibility_01.png

Of course you can always go back to the previous level any time.
 

What is a SQL Server Query Store and how do you enable it?

This is a new feature in SQL Server 2016 that provides with an insight into query plan choice and performance. As the query plan changes it helps you find the performance differences caused by the change.

Query store is automatic in that Query Store captures a history of queries, plans and runtime statistics for later review. It separates information chronologically by time windows so that you can view database usage patterns and monitor query plan changes that has taken place.

You need to enable the database to use this feature which an be done using SQL Server Management Studio as well as T-SQL Alter Database Set Option.

How do you turn it on using SQL Server Management Studio?

Since it is database related right click the database node after launching the SQL Server Management Studio and connecting to the server instance. The next image shows the result of right clicking the AdventureWorks 2014 database in SQL Server 2014.

QS_00.png

Click Properties to open the next window as shown.

QS_01

The Query Store Property has the following items:
General:
    Operation mode(actual)       default Off(disabled)
    Operation mode (Requested)   default Off but can be Read only or Read/write
Monitoring
    Data Flush Interval (Minutes)
    Statistics Collection Interval
Query Store Retention
    Max Size(MB)
    Query Store Capture Mode
    Size-based cleanup Mode
    Stale query Threshold(Days)

The previous window also shows the Current Disk Usage for database as well as Query Store.
As it is not enabled, the Query Store used is 0.0. You also have the option to purge the data.

You enable the Query Store feature by changing the Operation Mode(requested) from OFF to ON as shown.

QS_02

After it is enabled, a Query Store is added to the database as shown:

QS_03a.png

Why so many user accounts are created while using R Gui?

R programs were integrated with SQL Server 2016 for the first time. The R Server was installed when the SQL Server 2016 Developer Edition was installed. The shortcut in Windows 10 All apps show the details:

RWindows10

R GUI (x64) in the above was used (sometimes from R Server and sometimes from R Client)  to launch R. It has been used quite a number of times and what is intriguing is that each time it was used, the program has made an entry into the USERs node of the Local Users and Groups in the Computer Management window. If left without some sort of maintenance this could grow quite large.

RWindows10_1

One of the Local User Account OHANA01 created has the following property:

RWindows10_2

I am not usre if it is by design or a bug that creates these accounts. I will try to troubleshoot.

Looks like the above is by design according to an answer from SQL Server Forum:

I quote the answer here:
Looks like that is by design it self, please refer the BOL for sql R not for windows point of view.

As part of the installation process for R Services (In-database), a new Windows user account pool is created to support execution of tasks by the SQL Server Trusted Launchpad service. The purpose of these worker accounts is to isolate concurrent execution of R Scripts by different SQL users.

The number of user accounts in this pool determines how many R sessions can be active simultaneously. If the same user executes multiple R scripts concurrently, all the sessions run by that user will use the same worker account. As a consequence, a single user might have 100 different R scripts running concurrently as long as resources permit.

How do you create a data manipulation language trigger?

DML stands for Data Manipulation Language and DML Triggers are initiated automatically whenever a DML event occurs.

DML events include events that takes place in a Table or View and they are:

INSERT
DELETE
UPDATE statements.

Why do you need a DML Trigger?

In order to enforce business rules in the following cases/situations:

Enforce Data Integrity
Querying other tables
Auditing data changes

These can include complex Transact SQL statements.

As triggers are created for (on) a table or view, a 'Triggers' node exist for all tables in a database.

Presently there are no triggers set up for the Person.PersonPhone table in the AdventureWorks 2014 database as its Triggers node is empty as shown.

DML_00

In order to create a trigger you need to provide a name, and the SQL Statement that starts the trigger; and the table on which the trigger will fire. Execute the following statement which creates a trigger called PlNotify that triggers whenever a Insert or an Update statement is run:

CREATE TRIGGER PlNotify 
ON Person.PersonPhone
AFTER INSERT, UPDATE  
AS RAISERROR ('Notify Customer Relations', 16, 10); 
GO

When the trigger is created it gets into the Triggers node as shown.

DML_01

Let us see what happens when we try to update a phone number. Here are some of the phone numbers (only 5 from top is shown):

DML_02

Now let us update a phone number in the above table using the following:

UPDATE dbo.Person.PersonPhone
SET PhoneNumer='808-722-6655'
WHERE BusinessEntityID=1
--Print 'After update trigger fired'

The PhoneNumber of Business Entity with BusinessEntityID was 697-555-0142 before the trigger fired.

And after the above update statement is run it becomes 808-722-6655 and the trigger is completed.
With the following response:

"Msg 50000, Level 16, State 10, Procedure PlNotify,
Line 4 [Batch Start Line 8]
Notify Customer Relations(1 row(s) affected)
After update trigger fired"

The trigger fires after the update and the changed table is as shown:

DML_03

While this is a very simple example of creating a data manipulation trigger there is a lot things happening in the database. Two temporary, memory resident tables- inserted and deleted are created to take stock of the changes being made.

Also only the After Insert ,Update trigger was considered and there are other types of triggers as well.

What does the keyword 'auto' signify in C++?

This post describes the use of auto keyword in C++ projects in Visual Studio 2015 Community.

The meaning of auto keyword depends. It depends on the version of C++ you are using.
Before C++ version 11 appeared it simply meant that the keyword auto together with the datatype signified that the scope of the variable with which it was used has automatic storage duration (same as local variable). The keyword auto in that sense was used to explicitly specify the local variable to have automatic storage duration.

With C++ 11 version auto has a different meaning. You need not specify what the variable datatype is, it is inferred by the value it is assigned.
For example, instead of saying:

int x=5;
i.e., the datatype of x is integer, you can just say,

auto x=5;

This is called automatic type deduction. One thing to note is that the variables have to be assigned a value then only automatic type deduction works.

So, no initialization, no automatic type deduction.

In C++ 14, the automatic type detection is available not only for variables with assigned values but also for return types of funcitons. Here isan example of auto retrun type. The project is a blank C++ project type in Visual Studio 2015.

c

plusplus14

There was an intention to include auto keyword to be associated with function parameters but it was dropped. Visual Studio 2015 Community supports most of C++ 14 features but does not support the auto keyword for the function parameters as seen here.

Cplusplus14_1

If you try to build the project anyway, you will end up with the following errors:

Cplusplus14_2

How do you correctly create a JSON formatted string?

Although the rules are simple you could run into problems because of the violation of the rules from those described in the RFC 4627.

For example:

{"company":"Hodentek", "phone":"808-722-6785", "city":"Honolulu"}

is correctly JSON formatted and represent a JSON Object. The object is surrounded by curly brackets({ }).

The above is an example of the structured JSON CLR Object type

JSON admits of another structure, an array as shown here:

["Red", "Blue", "Yellow", "Magenta"]

is a correctly JSON formatted array. The array is surrounded by square brackets([ ])

The above is an example of the structured JSON CLR array type

Here is a JSON formatted version of an XML that I had used in a 2007 post to explain fine points of JSON.

{ "wclass":[
{"student": {"name":"Linda Jones", "legacySkill":"Access, VB 5.0"}},
{"student": {"name":"Adam Davidson", "legacySkill":"Cobol, MainFrame"}},
{"student": {"name":"Charles Boyer", "legacySkill":"HTML, XML"}} ]

I recently noticed that I had committed an error although the code in the post worked quite well when parsed using JavaScript. At that time I had not verified whether it was strictly validated and I had used 'inspection' to write out the JSON looking at the XML.

I recently visited JSON formatting in order to answer a question that came up on http://stackoverflow.com/ and I wanted to check my old code. I found this site which is quite useful for such validation.

https://jsonformatter.curiousconcept.com/

On this site you can check your JSON very easily by just entering your JSON data or a URL reference to it. After typing in, just hit the "Process" button and the page gets updated showing the result. If there are errors the site also shows the errors.

Here is my first iteration:

jsontest_00

Here is the result after processing:

jsontest_001

You can go back to where you typed at the top of the web page, without refreshing the page and make necessary corrections and resubmit by hitting the 'Process' button again. In this way you can arrive at the correct formatting.

This is the correction to my old code:

["wclass",
{"student":{"name":"Linda Jones","legacySkill":"Access, VB 5.0"}
},
{  "student":{"name":"Adam Davidson","legacySkill":"Cobol, MainFrame"}
},
{"student":{"name":"Charles Boyer","legacySkill":"HTML, XML"}
}]

Here is the response that the above is properly validated.

jsontest_031.jpg

This is the error (I should have used a comma(,) instead of a colon(:))  I might have had in my old code:

jsonold.png

There is one more check I will do before I am completely satisfied. I will rerun the JavaScript code to see whether some things have changed.

Can you start and stop SQL Server Instance using PowerShell?

The short answer is yes.

Here is the long answer which was verified on my Windows 10 Computer which has installed on it three versions of SQL Servers.

Firstly let us see the status of the SQL Server 2016 on this computer. There are three SQL Servers 2012, 2014 and 2016. Let us take a look at the SQL Server named instance OHANA. It appears to be stopped as seen in the Services window of the Control Panel.

StoppedOHana

Keep the Services screen open (to open Services, just type Services in the search charm).

It is possible to start the SQL Server (OHANA) from the Services screen, but we want to start it using PowerShell.

Launch Windows PowerShelll ISE with Administrator Permission. Type in Start-Service to see the intellisense drop-down as shown.

StartService_00.jpg

Access the parameters available by just typing a hyphen (-) and waiting,

StartService_01.jpg

Pick the DisplayName parameter.

Now you need to provide a display name for SQL Server (OHANA). Enter the following code.

Start-Service -DisplayName 'SQL Server (OHANA) '

Run the code or, hit F8.

This will produce an error as shown:

PS C:\WINDOWS\system32> start-Service -DisplayName 'SQL Server (OHANA) 'start-Service : Cannot find any service with display name 'SQL Server (OHANA)
'.
At line:1 char:1
+ start-Service -DisplayName 'SQL Server (OHANA) '
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (SQL Server (OHANA) :String) [St
   art-Service], ServiceCommandException
    + FullyQualifiedErrorId : NoServiceFoundForGivenDisplayName,Microsoft.Powe
   rShell.Commands.StartServiceCommand

You notice that there is an extra space between (OHANA) and the single quote highlighted in the above code.

Now, remove that white space and hit F8.

You get the following response:

StartService_02.jpg

In the ControlPanel |..|Services window click Action | Refresh.

You will notice that the SQL Server (OHANA) has started.

You can also stop the SQL Server with the following statement:

PS C:\WINDOWS\system32> Stop-Service -DisplayName  'SQL Server (OHANA)'

WARNING: Waiting for service 'SQL Server (OHANA) (MSSQL$OHANA)' to stop...

Refresh the Services windows and verify that the server has stopped.

How do you convert a JSON file to a custom object using PowerShell?

The syntax for converting a JSON formatted string to a custom object is the following:
------
Parameter Set: Default
ConvertFrom-Json [-InputObject] [ ]
-----
ConvertFrom-Json cmdlet converts a JavaScript Object Notation (Json) formated string to a PSCustomObject which has a property for each field in the JSON string.

Read more here:
https://technet.microsoft.com/en-us/library/hh849898.aspx

OK. Let us take a practical example.

Here is a JSON formatted Key-value pairs.
-----------
[  {
  "color": "red",
   "value":"#f00"
 },
 {
   "color":"green",
    "value":"#0f0"
 },
 {
           "color":"blue",
   "value":"#00f"
 },
 {
   "color":"cyan",
  "value":"#0ff"
 }
]
-----------
Now we declare a variable to take the JSON formatted string in a Here-String as shown:
------------
$data=@'
 [  {
  "color": "red",
   "value":"#f00"
 },
 {
   "color":"green",
    "value":"#0f0"
 },
 {
           "color":"blue",
   "value":"#00f"
 },
 {
   "color":"cyan",
  "value":"#0ff"
 }
]
'@
-------------
The above is the correct format that PowerShell would accept for conversion into a JSON object.
With this Here-String in hand you just use the cmdlet as shown:
--------------

ConvertFromJSON.jpg

Now you can use this obejct to find items such as number of segments, items by number or write out the data as shown.

ConvertFromJSON2.jpg

How do you transfer items from a SD card to an external storage medium using RavPower FileHub?

Transferring files is an activity that we do often.  Now that cloud storage is available from most vendors it is not often that we need to transfer from one storage device to another. Whereas a SD card is small that you keep it in your pocket a 1 TB USB drive is not. Also the SD card may be used in your camera which you often to empty to make room for new photos.

In this post I describe transferring file(files) from a SD Card to a USB External drive using the RavPower Filehub described in my earlier post.

These are the storage devices:

SanDisk 2GB SD Card
My Passport Ultra Western Digital  2 TB USB Disk

The RAVPower  model: RP-WD03

File Transfer process:

You connect the SD card and the external drive using USB. Start the device pressing the on button and make sure Wi-Fi is working. On your computer choose to connect the Wi-Fi to RavPower. When you are connected to RavPower you cannot browse the internet. Details of RAV Filehub app referre to my link above.

Your RAV FileHub app can now find the device.

FileTfr_00

Click on Explorer to open window. It will show the two connected devices.

FileTfr_01

Transfer file from SD card to external drive on USB

Choose a file(files) to transfer in the SD card folder as shown. Click on the 'spanner' icon and click Copy.

Click the Back button to show both the drives as shown in the image one above the previous. Click open the USB folder. Click the spanner icon to open the menu and click paste.
The file is now on UsbDisk2_Volume1.

Now the chosen file is in the UsbDisk2_Volume1.

What is Windows Device family?

Windows evolved from just windows to include 'phones'.

When Windows 8.1 was introduced Windows Runtime(WinRT) which was evolution of Windows app model came up with the Universal Windows 8 apps for both Windows and Windows Phone. Windows and Windows Phone shared a ccommon piece of code.

Universal Windows Platform was introduced beginning Windows 10. The target was no more the Windows OS or Windows Phone OS but a common platform with a unified core. This core runs on all devices in the Windows family.

Devices running this core can target can not only call WinRT APIs that are common to all devices but also Win32 and .NET APIs that are specific to device family. This means all you need to develop is a single app package that can be installed on a wide variety of devices shown here.

UWP_00.PNG

On any device, you have therefore, the guaranteed core and you add APIs on top of it specific to the device. You will conditionally access the device specific features in your code.

Here is the present line up of the device family.

UWP_01.PNG

Here is a Video from Microsoft's Channel 9 with additional information:

Can you filter the contents of a text file with PowerShell?

In order to work with text files you should know where the text file is stored (saved) in your directory. Once you have the file reference; if you just want to open the file, you could call Notepad from within Powershell like this (type this in the powershell prompt and it will open the file (I am using a file in one of my directories):

PS C:\Users\Jayaram> notepad.exe "C:\Users\Jayaram\Documents\Blog2016\HodentekPlus2016\NoblePrizeMedicine2016.txt"

However if you want to see the contents of the file on your dos screen and filter it you need to use the Get-Content commandlet as shown:

Once you have a file you can search through the text as shown here (here I am looking for all instances of 2016:

PS C:\Users\Jayaram> get-content  "C:\Users\Jayaram\Documents\Blog2016\HodentekPlus2016\NoblePrizeMedicine2016.txt"|Select-String -Pattern "2016"

Yoshinori Ohsumi gets the 2016 Noble Prize for Medicine
Physics 10/4 2016
Chemistry 10/5/2016
Peace 10/6/2016
Literature 10/29/2016

Note: RegEx has enough power to search for almost anything.