Issues and Resolutions in starting R and R Server on SQL Server 2017

I am helping some people learn Data Science and we are having a ton of fun! There are lots of things to remember. So I am noting things here, in case I forget!

We noted the following error message, when we saw that R was not running on our SQL Server 2017 install:

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

Here is the longer version:

Msg 39023, Level 16, State 1, Procedure sp_execute_external_script, Line 1 [Batch Start Line 3]

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

Msg 11536, Level 16, State 1, Line 4

EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

Grr! What’s happened here? We had installed R as part of the SQL installation, and we had run the command to enable it, too. In case you are wondering, here is the command:

EXEC sp_configure ‘external scripts enabled’, 1
RECONFIGURE WITH OVERRIDE

So what happens next? Initial things to check:

Is R Server installed properly along with SQL Server? Here are some guidelines to help you.

Is the Launchpad service running? One of my colleagues and friends Tomaž Kaštrun  wrote a nice article on SQL Server Central. If not, this could be due to a lack of permissions in being able to start the service.

Did you restart the MSSQL Service on the machine? This will also restart the Launchpad service as well. If you didn’t restart the service, you will need to do that so it can pick up the results.

Once R is running properly, you can check it by using the following command, borrowed from the official installation guide over at Microsoft:

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

If that returns a 1, then you are all set! To prove it works properly, you can retrieve the world famous Iris dataset using the following command, borrowed from the official documentation on sp_execute_external_script:

DROP PROC IF EXISTS get_iris_dataset;

go

CREATE PROC get_iris_dataset

AS BEGIN

EXEC sp_execute_external_script @language = N‘R’ , @script = N‘iris_data <- iris;’ , @input_data_1 = N , @output_data_1_name = N‘iris_data’ WITH RESULT SETS ((“Sepal.Length” float not null, “Sepal.Width” float not null, “Petal.Length” float not null, “Petal.Width” float not null, “Species” varchar(100)));

END;

GO

Once you’ve created the command, execute the following SQL command and you will see the iris dataset:

exec get_iris_dataset

You’re all set! Enjoy R!

Data Science VC follow up: Intro to R and Statistics for the Rookie Part 1

I’ve decided to hold a five week introductory statistics and R course. Here, I am sharing the slide deck and the code. The video will go up on our YouTube Data Science Virtual Chapter channel, which is accessible from here.

In the first week, we talked about the relationship between statistics and data visualisation, and how it is extremely useful to have a good grounding in both topics. The slides are here, followed by the code:

The R code can be copied and pasted into your RStudio file:

# Loads sample datasets
data()

# Let’s look at the data
# This command tells you the metadata. What does R see, when it sees ‘iris’?
str(iris)

# What are the attributes?
# This gives us more information.
attributes(iris)

# Let’s see more of the data
iris

class(iris)
# A data frame has columns which can have different types.
# The column names and types constitute the schema.
# how do we know what is in our data frame?

# Column Names
colnames(iris)

# how can we see data in one of the columns?
iris$Petal.Length
# or we could also use iris[,3] to get the same column data.

iris[,3]
# of course, we want to visualise the data.
# Let’s do a simple scatter plot.

# How can we see the first five rows?
iris[1:5,]

# how can we see the Petal Length of the first 5 rows?
iris[1:5, “Petal.Length”]

# This shows us some of the descriptive statistics of each variable
summary(iris)

table(iris$Species)

# Let’s have some dataviz fun!
plot(iris$Petal.Length, iris$Petal.Width, main=”Anderson’s Iris Data”)
# You can now see the plot appear in the right hand side frame of RStudio.
# we can make it slightly more interesting
plot(iris$Petal.Length, iris$Petal.Width, pch=23, bg=c(“orange”, “blue”, “green”) [unclass(iris$Species)], main=”Anderson’s Iris Data”)

# we can make it even more interesting
pairs(iris[1:4], main = “Anderson’s Iris Data”, pch = 23, bg = c(“orange”, “green”, “blue”)[unclass(iris$Species)])
# pie charts!
pie(table(iris$Species))

# ooh, 3D!

library(scatterplot3d)
scatterplot3d(iris$Petal.Width, iris$Sepal.Length, iris$Sepal.Width)

# ooh, even more 3D!
library(rgl)
plot3d(iris$Petal.Width, iris$Sepal.Length, iris$Sepal.Width)

#Save your work!

savehistory(“~/Topic 1 Getting familiar with R A.Rhistory”)