Friday, October 14, 2016

Using R in Windows 10 creates too many Local User Accounts

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:


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.


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


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.

Starting SQL Server with minimum configuration

The option to start up Microsoft SQL Server when you have configuration problems is to use the -f start up option. With this option SQL Server start with a minimum configuration placing the server in single-user mode.

Be aware of the following in this mode:

  • Only a single user can connect, and the CHECKPOINT process is not executed.
  • Remote access and read-ahead are disabled.
  • Startup stored procedures do not run.
Use the sqlcmd utility and the dedicated administrator connection to connect to SQL Server.

You can use the SC command line utility as you can use the start up parameters. SC is a command line program for communicating with the Service Control (SC) Manager and Services.

If you do not know how to use SC (Services Control Manager) review this post.

If you now start the SQL Server with a - f option the server with minimum configuration as shown:

I have a named instance of SQL Server 2016 called OHANA on my computer (Hodentek8) and I will now start it with minimum configuration by running the following. I have stopped this server before running the code:


 Verify with the Task Manager (there are other ways as well, Task Manager is open now).


Thursday, October 13, 2016

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.


The syntax for using SC is:
        Service Control Manager and services.
        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:


To start the SQL Server instance just type in the command



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


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



Verify it has stopped in Task Manager.


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.

Creating a simple DML trigger

An action is taken when a trigger is fired. 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:

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.


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:

ON Person.PersonPhone
AS RAISERROR ('Notify Customer Relations', 16, 10); 

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


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):


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:


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.

Wednesday, October 5, 2016

Starting and stopping a SQL Server instance using PowerShell

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.


Keep the Services screen open.

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.


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


Pick the DisplayName parameter.

Now you need to provide a display name for SQL Server (OHANA).

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

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

Now, remove that white space and hit F8.

You get the following response:


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.

Tuesday, October 4, 2016

SQL Server Configuration Manager shortcut is missing in my Windows 10

Under Microsoft SQL Server 2016 desktop apps I should have found the SQL Server Configuration Manager. I see the Reporting Services Configuration Manager but not the SQL Server Configuration Manager.

When you search for it specifically also you do not find it.

Where is it?

The following is the reason for this is given here: TechNet.

Because SQL Server Configuration Manager is a snap-in for the Microsoft Management Console program and not a stand-alone program, SQL Server Configuration Manager does not appear as an application in newer versions of Windows (8,10)

In Windows version 10  if you have to open SQL Server Configuration Manager for SQL Server 2016  the following item should be searched in  the  search charm of Windows 10:


When this is found you just click and display the SQL Server Configuration Manager as shown:

From the Help menu you can find the version.

Thursday, September 29, 2016

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:

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


After launching Launch Pad Service the query runs as shown.


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.


This query shows what data types have no support.