ALTER EXTERNAL LIBRARY (Transact-SQL)

Applies to: SQL Server 2017 (14.x) and later Azure SQL Managed Instance

Modifies the content of an existing external package library.

Note

In SQL Server 2017, R language and Windows platform are supported. R, Python, and external languages on the Windows and Linux platforms are supported in SQL Server 2019 and later.

Syntax for SQL Server 2019

ALTER EXTERNAL LIBRARY library_name
[ AUTHORIZATION owner_name ]
SET <file_spec>
WITH ( LANGUAGE = <language> )
[ ; ]

<file_spec> ::=
{
    (CONTENT = { <client_library_specifier> | <library_bits> | NONE}
    [, PLATFORM = <platform> )
}

<client_library_specifier> :: =
{
      '[\\computer_name\]share_name\[path\]manifest_file_name'
    | '[local_path\]manifest_file_name'
    | '<relative_path_in_external_data_source>'
}

<library_bits> :: =
{ 
      varbinary_literal 
    | varbinary_expression 
}

<platform> :: = 
{
      WINDOWS
    | LINUX
}

<language> :: = 
{
      'R'
    | 'Python'
    | <external_language>
}

Arguments

library_name

Specifies the name of an existing package library. Libraries are scoped to the user. Library names must be unique within the context of a specific user or owner.

The library name cannot be arbitrarily assigned. That is, you must use the name that the calling runtime expects when it loads the package.

owner_name

Specifies the name of the user or role that owns the external library.

file_spec

Specifies the content of the package for a specific platform. Only one file artifact per platform is supported.

The file can be specified in the form of a local path or network path. If the data source option is specified, the file name can be a relative path with respect to the container referenced in the EXTERNAL DATA SOURCE.

Optionally, an OS platform for the file can be specified. Only one file artifact or content is permitted for each OS platform for a specific language or runtime.

library_bits

Specifies the content of the package as a hex literal, similar to assemblies.

This option is useful if you have the required permission to alter a library, but file access on the server is restricted and you cannot save the contents to a path the server can access.

Instead, you can pass the package contents as a variable in binary format.

platform

Specifies the platform for the content of the library. This value is required when modifying an existing library to add a different platform. In SQL Server 2019, Windows and Linux are the supported platforms.

language

Specifies the language of the package. The value can be R, Python, or the name of an external language (see CREATE EXTERNAL LANGUAGE).

Remarks

For the R language, when using a file, packages must be prepared in the form of zipped archive files with the .ZIP extension.

For the Python language, the package in a .whl or .zip file must be prepared in the form of a zipped archive file. If the package already is a .zip file, it must be included in a new .zip file. Uploading a package as .whl or .zip file directly is currently not supported.

The ALTER EXTERNAL LIBRARY statement only uploads the library bits to the database. The modified library is installed when a user runs code in sp_execute_external_script (Transact-SQL) that calls the library.

A number of packages, referred to as system packages, are pre-installed in a SQL instance. System packages cannot be added, updated, or removed by the user.

Permissions

By default, the dbo user or any member of the role db_owner has permission to run ALTER EXTERNAL LIBRARY. Additionally, the user who created the external library can alter that external library.

Examples

The following examples change an external library called customPackage.

Replace the contents of a library using a file

The following example modifies an external library called customPackage, using a zipped file containing the updated bits.

ALTER EXTERNAL LIBRARY customPackage 
SET 
  (CONTENT = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\customPackage.zip')
WITH (LANGUAGE = 'R');

To install the updated library, execute the stored procedure sp_execute_external_script.

EXEC sp_execute_external_script 
@language =N'R', 
@script=N'library(customPackage)'
;

For the Python language, the example also works by replacing 'R' with 'Python'.

Alter an existing library using a byte stream

The following example alters the existing library by passing the new bits as a hexadecimal literal.

ALTER EXTERNAL LIBRARY customLibrary 
SET (CONTENT = 0xABC123...) WITH (LANGUAGE = 'R');

For the Python language, the example also works by replacing 'R' with 'Python'.

Note

This code sample only demonstrates the syntax; the binary value in CONTENT = has been truncated for readability and does not create a working library. The actual contents of the binary variable would be much longer.

See also

CREATE EXTERNAL LIBRARY (Transact-SQL)
DROP EXTERNAL LIBRARY (Transact-SQL)
sys.external_library_files
sys.external_libraries