Archive for the ‘Python’ Category

Get started with Python on SQL Server – Run Python with T-SQL on SSMS

August 10, 2018 2 comments

With SQL Server 2016 Microsoft added Machine Learning support with R Language in SQL engine itself and called it SQL Server R Services.

Going ahead with the new SQL Server 2017 version Microsoft added Python too as part of Machine Learning with existing R Language, and thus renamed it to SQL Server Machine Learning Services.


Here are few steps to get you started with Python programming in SQL Server, so that you can run Python scripts with T-SQL scripts within SSMS:

1. Feature Selection: While installing SQL Server 2017 make sure you’ve selected below highlighted services


2. Configure “external scripts enabled”: Post installation run below SQL statements to enable this option

sp_configure 'external scripts enabled'
sp_configure 'external scripts enabled', 1;
sp_configure 'external scripts enabled'


3. Restart SQL Server service: by “services.msc” program from command prompt, and run below SQL statement, this should show run _value = 1

sp_configure 'external scripts enabled'


If still you don’t see run _value = 1, then try restarting the Launchpad service mentioned below in Step #4.

4. Launchpad Service: Make sure this service is running, in “services.msc” program from command prompt. Restart the service, and it should be in Running state.

Its a service to launch Advanced Analytics Extensions Launchpad process that enables integration with Microsoft R Open using standard T-SQL statements. Disabling this service will make Advanced Analytics features of SQL Server unavailable.

Post restarting this service, it should return run _value = 1 on running the query mentioned at Step #3

Run Python from SSMS

So as you’ve installed SQL Server with ML services with Python & R, and enabled the components, now you can try running simple “Hello World!” program to test it:

EXEC sp_execute_external_script 
	@language = N'Python', 
	@script = N'print(''Hello Python !!! from T-SQL'')'


Let’s do simple math here:

EXEC sp_execute_external_script 
@language = N'Python', 
@script = N'
x = 5
y = 6
a = x * y
b = x + y


If you still face issues you can go through addition configuration steps mentioned in [MSDN Docs link].