CREATE EXTERNAL LANGUAGE (Transact-SQL)
Applies to: SQL Server 2019 (15.x)
Registers external language extensions in the database from the specified file path or byte stream. This statement serves as a generic mechanism for the database administrator to register new external language extensions on any OS platform supported by SQL Server. For more information, see Language Extensions.
Note
R and Python are reserved names and no external language can be created with those specific names. For more information on how to use R and Python, see SQL Server Machine Learning Services.
CREATE EXTERNAL LANGUAGE language_name
[ AUTHORIZATION owner_name ]
FROM <file_spec> [ ,...2 ]
[ ; ]
<file_spec> ::=
{
( CONTENT = { <external_lang_specifier> | <content_bits> },
FILE_NAME = <external_lang_file_name>
[ , PLATFORM = <platform> ]
[ , PARAMETERS = <external_lang_parameters> ]
[ , ENVIRONMENT_VARIABLES = <external_lang_env_variables> ] )
}
<external_lang_specifier> :: =
{
'[file_path\]os_file_name'
}
<content_bits> :: =
{
varbinary_literal
| varbinary_expression
}
<external_lang_file_name> :: =
'extension_file_name'
<platform> :: =
{
WINDOWS
| LINUX
}
<external_lang_parameters> :: =
'extension_specific_parameters'
language_name
Languages are database scoped objects. Language names must be unique within the database.
owner_name
Specifies the name of the user or role that owns the external language. If not specified, ownership is given to the current user. Depending on permissions, other users may need to be granted explicit permission to execute scripts using a specific language.
file_spec
Specifies the content of the language extension. Only one filespec is allowed for a specific language, per platform.
external_lang_specifier
The full file path to the .zip or tar.gz file containing the extensions code. This content can either be a path to a .zip file (on Windows) or tar.gz (on Linux).
content_bits
Specifies the content of the language as a hex literal, similar to assemblies.
This option is useful if you need to create a language or alter an existing language (and have the required permissions to do so), but the file system on the server is restricted and you cannot copy the library files to a location that the server can access.
external_lang_file_name
Name of the extension .dll or .so file. This is required to identify the correct file, in cases where there are several .dll or .so files in the <external_lang_specifier> .zip or tar.gz.
external_lang_parameters
This provides a possibility to give a set of parameters to the external language runtime. Parameter values are provided to the external runtime after the external process has started. Environment variables however, are accessible to the language extension prior to the external process startup.
external_lang_env_variables
This provides a possibility to give a set of environment variables to the external language runtime prior to the external process startup. An example of an environment variable is for example the home directory of the runtime itself. For example: JRE_HOME.
platform
This parameter is needed for hybrid OS scenarios. In a hybrid architecture, the language needs to be registered once per platform. If no platform is specified, the current OS is assumed.
Requires the CREATE EXTERNAL LANGUAGE
permission. By default, any user who has dbo who is a member of the db_owner role has permissions to create an external language. For all other users, you must explicitly give them permission using a GRANT statement, specifying CREATE EXTERNAL LANGUAGE as the privilege.
To modify a library requires the separate permission, ALTER ANY EXTERNAL LANGUAGE
.
You can use EXECUTE EXTERNAL SCRIPT permissions, so that external script execution can be granted on specific languages. This is different from EXECUTE ANY EXTERNAL SCRIPT database permission, which do not allow granting execution permission on a specific language.
This means that non-dbo users need to be granted permission to execute a specific language:
GRANT EXECUTE EXTERNAL SCRIPT ON EXTERNAL LANGUAGE ::language_name
TO database_principal_name;
Similar to assemblies, reference permissions are needed for external languages so that there is a link between external libraries and external languages. For example, if an external language is going to be dropped, first the user needs to make sure all the external libraries referencing that language are dropped. You can view the external language as a higher level object than external libraries, in a hierarchy.
The following example adds an external language called Java to a database on SQL Server on Windows.
CREATE EXTERNAL LANGUAGE Java
FROM (CONTENT = N'<path-to-zip>', FILE_NAME = 'javaextension.dll');
GO
You can specify up to two <file_spec>
, one for Windows and one for Linux.
CREATE EXTERNAL LANGUAGE Java
FROM
(CONTENT = N'<path-to-zip>', FILE_NAME = 'javaextension.dll', PLATFORM = WINDOWS),
(CONTENT = N'<path-to-tar.gz>', FILE_NAME = 'javaextension.so', PLATFORM = LINUX);
GO
The following example grants the mylogin principal access to execute scripts using the Java external language.
GRANT EXECUTE EXTERNAL SCRIPT ON EXTERNAL LANGUAGE ::Java
TO mylogin;
ALTER EXTERNAL LANGUAGE (Transact-SQL)
DROP EXTERNAL LANGUAGE (Transact-SQL)
sys.external_languages
sys.external_language_files