With SQL Server 2016, the ability to execute R code in SQL Server was added. SQL Server 2017 added functionality to run Python code as well. This functionality for either language can be installed as part of the SQL Server installation, or added to an existing instance.
My initial attempt at Python installation didn’t go smoothly. Multiple attempts at install would hang up and not progress. However, once I removed the Python 3 installation from the machine, the install was able to complete.
The Launchpad service gets installed as part of the Python/R package as well. This service needs to be running in order to execute Python or R code. At first I created a new user for the service, but I ran into errors trying to execute code.
Msg 39021, Level 16, State 1, Line 55
Unable to launch runtime for ‘Python’ script. Please check the configuration of the ‘Python’ runtime.
Msg 39019, Level 16, State 2, Line 55
An external script error occurred:
Unable to launch the runtime. ErrorCode 0x80070490: 1168(Element not found.).
I ended up giving the service an administrator account to run under, which cleared up this issue.
The ‘External Scripts Enabled’ setting should be set to true in order to run Python/R code.
EXEC sp_configure 'external scripts enabled', 1; GO RECONFIGURE; GO
I’ll create a small table to have some test data.
drop table if exists dbo.Team; create table dbo.Team(Team varchar(20) not null primary key); insert into dbo.Team values('Falcons'), ('Saints'), ('Panthers'), ('Buccaneers');
And then run a simple loop to read the team names from the table and write them to the results pane.
exec sp_execute_external_script @language = N'Python', @script = N'for x in InputDataSet.Team: print(x)', @input_data_1 = N'select Team from dbo.Team';
InputDataSet is the default name for the data set read in from the database. There is a parameter for the sp_execute_external_script proc to set a different name, if so desired.