Configure SQL Server Python and R customer External Languages

01.activate-external-scripting.sql

USE master;
GO
-- enable external scripting at instance level
EXEC sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO

02.create-external-languages-multi-dbs.sql

USE master;
GO

DECLARE	@Databases			TABLE (DatabaseName sysname primary key);
DECLARE	@DatabaseName		sysname;
DECLARE @sqlstmt_replaced	nvarchar(max);

DECLARE @sqlstmt_template	nvarchar(max) =
N'
USE [?];

IF EXISTS(SELECT * FROM sys.external_languages WHERE [language] = ''my_R'')
	DROP EXTERNAL LANGUAGE [my_R];

CREATE EXTERNAL LANGUAGE [my_R]
FROM (CONTENT = N''C:\SQLExternalLanguageExtensions\R-lang-extension-windows-release.zip'', 
	FILE_NAME = ''libRExtension.dll'',
	ENVIRONMENT_VARIABLES = N''{"R_HOME": "C:\\Program Files\\R\\R-4.2.2"}'');

IF EXISTS(SELECT * FROM sys.external_languages WHERE [language] = ''my_python'')
DROP EXTERNAL LANGUAGE [my_python];

CREATE EXTERNAL LANGUAGE [my_python]
FROM (CONTENT = N''C:\SQLExternalLanguageExtensions\python-lang-extension-windows-release.zip'', 
	FILE_NAME = ''pythonextension.dll'', 
	ENVIRONMENT_VARIABLES = N''{"PYTHONHOME": "C:\\Program Files\\Python37"}'');
';

INSERT	@Databases
VALUES	('DatabaseName1'), ('DatabaseName2'), ('DatabaseName3');

-- first iteration
SET		@DatabaseName = (SELECT MIN(DatabaseName) FROM @Databases);

WHILE @DatabaseName IS NOT NULL
BEGIN
	-- better to always wait between executions to be able to cancel the query...
	WAITFOR DELAY '00:00:00.300';
	-- execute for each database
	SET	@sqlstmt_replaced = REPLACE(@sqlstmt_template, N'?', @DatabaseName);
	BEGIN TRY
		EXEC (@sqlstmt_replaced);
	END TRY
	BEGIN CATCH
		IF ERROR_NUMBER() = 942 -- database offline (may have changed state meanwhile)
		BEGIN
			DECLARE	@ErrorMessage nvarchar(max) = FORMATMESSAGE('Msg %d, Level %d, State %d, Line %d - %s', ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_MESSAGE());
			PRINT	@ErrorMessage
			DELETE @Databases WHERE DatabaseName = @DatabaseName;
			SET @DatabaseName = (SELECT MIN(DatabaseName) FROM @Databases);
			CONTINUE;
		END
		ELSE
			THROW;
	END CATCH
	-- next iteration
	DELETE	@Databases WHERE DatabaseName = @DatabaseName;
	SET		@DatabaseName = (SELECT MIN(DatabaseName) FROM @Databases);
END
GO

03.check-external-languages-config.sql

-- Test Python configuration
EXECUTE sp_execute_external_script
	@language =N'my_python'
,	@script=N'
import sys
print(sys.path)
print(sys.version)
print(sys.executable)';

EXECUTE sp_execute_external_script
	@language = N'my_python'
,	@script = N'OutputDataSet = InputDataSet'
,	@input_data_1 = N'SELECT 1 AS dummy'
WITH RESULT SETS(([dummy] INT));
GO

-- Test R configuration
EXEC sp_execute_external_script
 @language =N'my_R',
 @script=N'
print(R.home());
print(file.path(R.home("bin"), "R"));
print(R.version);
print("Hello RExtension!");'

EXECUTE sp_execute_external_script
	@language = N'my_R'
,	@script = N'OutputDataSet = InputDataSet'
,	@input_data_1 = N'SELECT 1 AS dummy'
WITH RESULT SETS(([dummy] INT));
GO
-- Registrazione metadata
SELECT * FROM sys.external_languages;
SELECT * FROM sys.external_language_files;
GO