Fix: Getting R and Python to actually work on SQL Server 2022

R and Python running in SQL Server 2022

One of my more surprisingly popular blog posts in the past talked about Machine Learning: Testing your installation of R and Python in SQL Server 2017. The problem is that SQL Server 2022 changed things.

Now the SQL Server team has published an article on how to make it work.  What I've been finding though, is that people are struggling to follow that article. And more concerning, people using named instances of SQL Server (like SQLEXPRESS) couldn't get it to work at all, no matter how much they tried.

It took me a while to work out what was needed, so I figured it was time to share it with others. I've also submitted a few pull requests to get that page updated. So, by the time you read this, it might already be updated.

What you need to do for R and Python on SQL Server 2022

You need to start by installing SQL Server and making sure you choose at least BOTH the Database Engine Services and the Maching Learning Services and Language.

Unlike earlier versions, the R and Python libraries are not installed or configured for you. If you try to execute R code at this point, even after configuring SQL Server, you will see this type of error:

The error for Python is similar:

I've included both in case someone is searching for this. At least it tries to link to a solution, even though the link is still a docs link not a Learn link. (I'll put in a pull request for that too)

Getting R to work

Let's start by getting R to work.

You need to download and install the latest R 4.2. I got this from https://cran.r-project.org/bin/windows/base/old/4.2.3/ and I installed it with all default options.

R download

You then need to install some standard R dependencies. You do this by running RTerm. I found it here:

Location of RTerm

I right-clicked and ran as administrator, just in case. Then you execute this code step one row at a time, making sure that each installs ok. Note that on the first one you install, you'll probably be prompted for a CRAN mirror site. Pick one near you.

Then there are some Microsoft packages that need to be installed.  Execute these statements one at a time, making sure that each works. You can ignore warnings at this point. (And note this is two lines even though they show line-wrapped here)

Now the first challenge

The next step is where things went wrong. You need to find a program called RegisterRext.exe. For me, it was in this folder: C:\Program Files\R\R-4.2.3\library\RevoScaleR\rxLibs\x64

RegisterRext location

You open a command prompt (cmd.exe) as an administrator. Then you need to configure the instance of SQL Server with the version of R. The article says you do this by executing:

That works fine if you have a default instance of SQL Server and if it's called MSSQLSERVER (the default name). Not so much if you have a named instance. I was trying this on a machine with a SQLEXPRESS named instance as I'd heard this was an issue.

The article didn't give you any idea or example for a named instance. So I tried all the things I normally would for a named instance, including not knowing if I needed to escape the backslash:

  • ".\SQLEXPRESS"
  • "MACHINENAME\SQLEXPRESS"
  • ".\\SQLEXPRESS"
  • "MACHINENAME\\SQLEXPRESS"

and so on. I was about to give up when I finally just tried "SQLEXPRESS". And that worked ! I understand why people are confused. Nowhere else in SQL Server land do you refer to a named instance that way.

So the command needed in that case was just:

Configure and test R in SQL Server

Then it's time to test the SQL Server end.  You need to start by executing:

And you then need to restart the SQL Server service.

Next you can see if it's working. Execute the following in SQL Server:

That should show you 1 as a response.

But no cigar yet

All good so far. So, then I tried an actual R query, as suggested in the article:

Well, I didn't get a great response with that. It should have worked at that point but instead, after quite a while, it returned this:

OK, that looked a bit scary. I started spending ages trying to work out what on earth was wrong with sqlsatellite.dll.

And the weird thing? After a while, and a few more attempts, it just started working. No clue as to why it didn't just work the first time. But I've now seen that on two machines, so it's no coincidence. Be patient. Eventually it returned:

More complex R query

I then was able to execute something more complex:

And R is working

And then R is working !

Python

Then it was time to see how I went with Python. Once again, not as smooth sailing as I'd hoped.

Again, you need to download Python installer. But the link for Python 3.1 that's included in the article goes to a page that now tells you that it's too old to have binaries available. (For Python apparently that means less than a year old). I started to see if it would work with Python 3.11 (the earliest with binaries) but eventually scared myself off, as I knew that wasn't going to be good.

I searched for "Python 3.10 for Windows" and ended up with a link at the same site, and it did have binaries. That link was:

https://www.python.org/downloads/release/python-3100/

I downloaded it, but importantly, I chose a custom install. (Very easy to miss on the first page). The main thing you need to change is the install folder. It will want to install it in one of your private folders by default, but instead, choose C:\Program Files\Python310 as the install folder. Near the end of the install I also chose hte option to Disable the path length limit. I doubt that's needed here but I've run into it before so I decided to choose it.

Next in an admin command window (same one as before if it's still open), you need to step through these three commands:

That took a little while but was all good.

Another challenge

You then need to set some permissions on folders. The instructions say to execute this command:

Again though, that assumes that the service that's running your SQL Server Launchpad service is "NT Service\MSSQLLAUNCHPAD".

Mine wasn't.

You can find what it is (if you don't remember from when you installed it), by opening (just Windows start/run) services.msc and looking for the service.

services.msc output

Being a standard SQLEXPRESS installation, mine was actually "NT Service\MSSQLLaunchpad$SQLEXPRESS", so instead of the above command, I needed to execute:

That then worked ok. We also need to set one more permission (and it's unrelated to your instances):

We're not done yet

At this point, we're back to needing to use a different version of RegisterRext.exe to configure the SQL Server instance for the version of Python.

The instructions say to execute:

Once again, that's not going to work for my SQLEXPRESS named instance, so I needed to execute this instead:

Now it's time to test Python in SQL Server

Because you've already enabled external scripts in SQL Server, if you've previously installed R, you don't need to enable them again.

Instead, I tested Python:

And that worked:

Simple Python test

As did a more complex query:

More complex Python query

And we're finally done !

I hope that helps someone else get started. (I've also pushed pull requests to the Learn site to get some of this updated. Hopefully it will be by the time you read this).

 

 

 

2 thoughts on “Fix: Getting R and Python to actually work on SQL Server 2022”

  1. If I execute this command
    .\RegisterRext.exe /configure /rhome:"%ProgramFiles%\R\R-4.2.3" /instance:"MSSQLSERVER"

    Then I get an error:
    Error: The rxLibs %ProgramFiles%\R\R-4.2.3\library\RevoScaleR\rxLibs\x64 under the given runtime home is not valid for R script type.

    It also weird, my RevoScaleR is not installed at your location but in C:\Users\jaden\AppData\Local\R\win-library\4.2\RevoScaleR
    on my PC.

    1. The problem there seems to be with your R install. It's important to have R installed in a location that's available to all users on the machine. You currently have it in your personal profile area. I think you'll need to remove R and reinstall it to a different path.

Leave a Reply

Your email address will not be published. Required fields are marked *