sp_execute_external_script: Run Python Inside SQL Server

sp_execute_external_script lets you run Python next to your data, but only if four things are switched on first. Check those before you write a single line of Python. On my test server two of the four were off, and this post shows exactly what that looks like.

Why you would run Python inside SQL Server

A data scientist walks over and says, “My model is ready. Can it run where the data lives?” Nobody enjoys exporting a million rows to a laptop and back. With sp_execute_external_script, SQL Server hands a query result to a Python script and returns whatever the script produces as a normal result set.

The Python runs in a separate process, not inside the database engine. That one fact explains most of the surprises later. It needs its own service, its own switch, and its own packages.

Check the server before you write any Python

Four checks, in this order. Is Machine Learning Services installed? Is Python registered as an external language? Is the Launchpad service running? Is the setting called external scripts enabled turned on?

SELECT SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS IsAdvancedAnalyticsInstalled;

SELECT language FROM sys.external_languages ORDER BY language;

SELECT servicename, status_desc
FROM sys.dm_server_services
ORDER BY servicename;

SELECT name, value, value_in_use
FROM sys.configurations
WHERE name = N'external scripts enabled';

On my test server the feature is installed, and the language list shows Python and R. But the Launchpad service shows Stopped, and external scripts enabled is 0. So the first two checks pass, and the last two do not.

See what a switched-off server tells you

Now call the procedure anyway. The script is as small as it gets. It reads one row and returns the same row. I wrapped the call in TRY and CATCH so the error shows up as a result you can read.

BEGIN TRY
    EXEC sys.sp_execute_external_script
        @language = N'Python',
        @script = N'OutputDataSet = InputDataSet',
        @input_data_1 = N'SELECT CONVERT(int, 3) AS InputValue'
    WITH RESULT SETS ((InputValue int));
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorText;
END CATCH;

The error is 39023. It says the procedure is disabled on this instance and points to sp_configure. That setting is server-wide, and this is a shared test server, so I left it alone. On your own server, decide that on purpose, with your security team in the room.

Send one row in and get one row back

@input_data_1 holds the query that feeds the script. Inside Python, that result arrives as a data frame called InputDataSet. Whatever you put into OutputDataSet is what comes back. WITH RESULT SETS tells SQL Server the column names and types to expect, so match them to your Python output.

This version checks the setting first and skips the call politely when scripts are off. On a server where they are on, the script is written to return one row, InputValue 3. On mine it printed the skip message.

IF (SELECT value_in_use FROM sys.configurations
    WHERE name = N'external scripts enabled') = 1
    EXEC sys.sp_execute_external_script
        @language = N'Python',
        @script = N'OutputDataSet = InputDataSet.copy()',
        @input_data_1 = N'SELECT CONVERT(int, 3) AS InputValue'
    WITH RESULT SETS ((InputValue int));
ELSE
    PRINT N'External scripts are switched off on this server, so the call was skipped.';

Pass values with @params

@params works like the parameter list in sp_executesql. You declare the type, then pass the value, and the Python script sees a variable with that name. Pass values this way instead of gluing them into the script text, especially when they come from an application.

The script below multiplies the input by a parameter named multiplier. It is a toy calculation, there only to show the mapping. With 3 and 2 it is written to return a column called ScaledValue holding 6.

IF (SELECT value_in_use FROM sys.configurations
    WHERE name = N'external scripts enabled') = 1
    EXEC sys.sp_execute_external_script
        @language = N'Python',
        @script = N'
OutputDataSet = InputDataSet.copy()
OutputDataSet["ScaledValue"] = OutputDataSet["InputValue"] * multiplier
OutputDataSet = OutputDataSet[["ScaledValue"]]',
        @input_data_1 = N'SELECT CONVERT(int, 3) AS InputValue',
        @params = N'@multiplier int',
        @multiplier = 2
    WITH RESULT SETS ((ScaledValue int));
ELSE
    PRINT N'External scripts are switched off on this server, so the call was skipped.';

What I could not run, and what to test on yours

I could not run the Python on this server, so I am not showing you a successful result. What you read above is the error I saw and the behavior the code is written for. Please run it on a server where the feature is on, and see the rows for yourself.

Then test the awkward cases before any real data goes through. Send a NULL, a decimal, a date and a Unicode string, and look at how each one arrives in Python and comes back. Do not assume types survive the trip. Packages installed on your desktop Python are not visible to the Python SQL Server uses.

Finally, remember the process boundary. Filter and aggregate in T-SQL first, and send only the columns the script needs. Moving a large table into Python costs memory, and no tiny smoke test will tell you how much.

Check the switches first, and the Python part gets a lot more fun.

An external script is not part of the engine, it is a separate runtime.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.