Getting R to run on Sql Server 2016

In Uncategorized on 2016/10/06 by phejndorf


I have slowly been moving into the R space for a couple of years, and see it as both an exciting DSL as well as a journey from Sql and C# experience into the realm of serious data analysis and data science (and visualization).

It is a giant contribution that Microsoft is making by making the results of it’s Revolution Analytics acquisition more or less freely available to the world. I was able to attend the EARL conference on R applications in London some weeks back, and it was an exciting view into the world of analytics!

Today I got around to fire up some R on an Sql 2016, with an EARL session by Adam Rich as my starting point. It was somewhat laborious and the documentation and samples around the web were really convoluted or sometimes plain misleading. I really had to cut through a lot of extraneous information and so for future reference, here is what I did.

I had already installed Microsoft R and RStudio (and VS R tools, though I didn’t use them for this) as well as Sql Server 2016 with R, but at first I just wanted to check out the server with a very simple script:

EXEC sp_execute_external_script  @language =N’R’,   
    @input_data_1 = N’SELECT 42 AS TheAnswer’,
    @script = N’OutputDataSet <- InputDataSet’
    WITH RESULT SETS (([TheAnswer] int not null));

This failed with the message:

‘sp_execute_external_script’ is disabled on this instance of SQL Server. Use sp_configure ‘external scripts enabled’ to enable it.

The answer to this was to run these commands and then to restart the database service:

sp_configure ‘external scripts enabled’, 1; 


Now it was time to try my hand at an R script that I would write locally on my PC and run remotely on the Sql Server with a trusted connection – in R parlance in a remote compute context – pulling a bit of data. First, however, the Microsoft R Client had to be installed – the version I had apparently didn’t  include the MRO stuff required to connect to the server – you can check the “c:\Program Files\Microsoft\MRO”  directory to verify that you have both an “MRO” and “R Client” directory.

A major problem was this message when setting up the server-connection (RxInSqlServer):

Error in .rxEnsureOdbcCredentials(connectionString, server, databaseName,  : 
  Trusted_Connection or User must be specified either in the connection string with the uid keyword or as a named parameter.

Most samples included a connectionstring for the RxInSqlServer-function, but this seems to be unnecessary.  I had to fiddle quite a bit with the parameters for the various special R server functions, ie those prefixed with RX. Also note that sometimes the prefix is Rx and sometimes rx… It appears that 2 connections are necessary: one for the R server computing context and one when executing Sql in the R context. But, to cut a long story short, this is what I got to work as a sort of minimum solution:

# Set up a trusted connection to R in Sql 2016

sqlCompute <- RxInSqlServer(
    server = “LC_SQL”,
    #wait = TRUE,
    #consoleOutput = FALSE,
    #shareDir = “e:/”,

    user = “Trusted_Connection”

rxSetComputeContext(sqlCompute) # Switch script execution to Sql server

sqlConnString <- “Driver=Sql Server; Server=localhost; Database=master; Trusted_Connection = True”;

sqldata <- RxSqlServerData(sqlQuery = “select name, database_id from sys.databases”, connectionString = sqlConnString)
rxGetVarInfo(data = sqldata) # Show our variables
myframe <- rxImport(inData = sqldata) # Get data as a dataframe
rxSetComputeContext(RxLocalSeq()) # Switch script execution back to local machine


Hope this can help someone struggling with the same challenges Smile


Calculate quarter and month in T-SQL

In Uncategorized on 2016/03/08 by phejndorf

A quick note to self for converting quarter to (first) month in quarter and month to quarter by calculation:

Q = (M + 2) / 3

M = (Q * 3) – 2


Use Powershell function parameters like Write-Host

In Powershell on 2016/02/11 by phejndorf

It would seem straightforward, but I’ve spent a couple of hours googling and experimenting to solve the riddle, so here is what I found.

What I wanted to do, was to write a Powershell function with parameters that work like the Write-Host commandlet, where you can have both named parameters (ie –BackgroundColor) and a number of other parameters (words) that are not positional parameters but get collected into a string like so:


I my case I wanted a Text-Out function with an optional –Filename parameter where the output could be appended as well as written to the console like this:

Text-Out Lorem ipsum dolor -Filename "e:\test.txt" sit amet

Here is my Text-Outfunction:

function Text-Out

     [string] $filename,
     [string[]] $remainingargs

    Write-Host ([string]::Join(" ", $remainingargs))

    if (-not $filename) {return}

    Add-Content $filename ([string]::Join(" ", $remainingargs))



The first point to note is the CmdletBinding, this is required in order that Powershell does not interpret the first word as a positional parameter, ie sets $filename = Lorem, in case the filename named parameter has been left out.

Second, the Parameter(ValueFromRemainingArguments=$true) ensures that all the words that are neither positional nor name parameters as such get collected into an array of strings.

Third, these words are then joined with the .Net [string]::Join() function. If the separating spaces were not needed, the built in Powershell –join function could be used instead. If Add-Content was just given the $remaingargs array, it would write one line for each word.


Can’t install Sql Management Studio on Windows 8, .Net 3.5 missing

In Uncategorized on 2015/09/24 by phejndorf

It is really odd that it should be so hard getting SSMS 2014 on a Windows 8 box.

The problem is that SSMS needs .Net 3.5, but that the ordinary methods  to get .Net 3.5 installed (Windows Features in Programs and Features) do not work but only result in some very unhelpful messages!

Kunal Chowdry has made a nice description of the whole thing here, but to cut a long story short here is the gist of it:

DISM  /Online  /Enable-Feature  /FeatureName:NetFx3  /All  /LimitAccess  /Source:x:\sources\sxs

You run from an administrative command prompt – remember to replace “x:” whith the path to your Windows 8 installation path, ie a DVD.

DISM (Deployment Image Servicing and Management) has come to my help before. So sad that Windows has always lacked proper package management, so we need to rely on a mix of Control Panel, DISM, Installers, Chocolatey, Boxstarter etc.


T-SQL UNPIVOT’ing columns with different datatypes

In Uncategorized on 2015/09/01 by phejndorf

Well, you can’t, really. What you have to do is to make sure that all the the columns have the same datatype and then perform the unpivot.

Here is the table I want to partially unpivot:


So what I do is to cast all columns to a suitable common format, in this case VARCHAR(100), before the actual pivoting:

SELECT Id, ColumnName, Value
        CAST(SysNr AS Varchar(100)) SysNr,
        CAST(KredsNr AS Varchar(100)) KredsNr,    
        CAST(EjendomsNr AS Varchar(100)) EjendomsNr,    
        CAST(Navn AS Varchar(100)) Navn,
        CAST(EjendomStatusId AS Varchar(100)) EjendomStatusId,
        CAST(Aktiv AS Varchar(100)) Aktiv
    FROM Ejendom
) AS S
    Value FOR ColumnName IN (SysNr, KredsNr, EjendomsNr, Navn, EjendomStatusId, Aktiv)
) AS U

Voila – quite a bit of typing, but pretty easy peasy otherwise.


Switching your IP setting to static with PowerShell

In Uncategorized on 2015/08/19 by phejndorf


In our Hyper-V test environment we use a mixture of dynamic and static IP addresses. Some servers need to have static IP addresses and in order to facilitate the process of provisioning a new server I wanted to have a script that toggles the current network adapter setting from dynamic (DHCP) to static.

Do remember to mark that IP in the DHCP server as unavailable as well in order not to get IP confilcts…

Write-Host "Switch Current IP configuration to to Static IP"

$wmiip = Get-WmiObject win32_networkadapterconfiguration -filter "DHCPEnabled = 'true' and ipenabled = 'true'"

if ($wmiip)
    Write-Host "Switching..."

    $ipaddress = $wmiip.IPAddress[0] 
    $ipgateway = $wmiip.DefaultIPGateway[0]
    $ipdnsordr = $wmiip.DNSServerSearchOrder

    $wmiip.EnableStatic($ipaddress, "")
    $wmiip.SetGateWays($ipgateway, 1)
    Write-Host "Static already set"

Get-WmiObject win32_networkadapterconfiguration -filter "ipenabled = 'true'"
"DNS              : " + $wmiip.DNSServerSearchOrder

Write-Host "Done..." 


This script was inspired by an article by Scripting Guy


T-Sql to generate a table of datetimes (time series)

In Uncategorized on 2015/04/16 by phejndorf

The other day I needed a table of datetimes for each second for a given period, and investigated various suggestions of which there are many around the web.

What I came up with as the, in my opinion, easiest solution was this code. I already had a very large table that could provide me with a set of records to iterate over (millions), avoiding some complicated T-Sql looping.

        ROW_NUMBER() OVER (ORDER BY Id) AS Number,
            ROW_NUMBER() OVER (ORDER BY Id), 
            DATEDIFF(DAY, 0, GETDATE()))) AS DateTime
        FROM [DB]..[LargeTable]

The number 86399 corresponds to  the number of seconds (One day = (60*60*24) – 1) to generate in the table.