microsoft/sql-server-language-extensions

language-extensions-sqlserver

Microsoft SQL Server Language Extensions

Welcome to the SQL Server Language Extensions project!

Starting in SQL Server 2019, we have added support for language extensibility, which means that you can now execute code in various languages like R, Python and Java from SQL Server.

To download the released prebuilt versions of these extensions, go to the Releases section of this repository. Download the latest version of whichever extension corresponds to your machine configuration (Windows or Linux). There are separate releases for each language extension.

We hope you enjoy using Microsoft SQL Server and the language extension feature.

-SQL Server Team

Documentation

You can read more about how to use language extensions and how to use Java in SQL Server 2019 in the Microsoft documentation.

Tutorials and documentation for the R and Python extensions can be found here:

Python Documentation

R Documentation

Custom Builds

To build your own version of the language extensions, refer to each respective language's README file.
For example, to modify and build a custom version of the R Extension, go to language-extensions/R/README.md.

Contributing

This project welcomes contributions and suggestions. Most contributions require you to agree to a Contributor License Agreement (CLA) declaring that you have the right to, and actually do, grant us the rights to use your contribution. For details, visit https://cla.microsoft.com.

When you submit a pull request, a CLA-bot will automatically determine whether you need to provide a CLA and decorate the PR appropriately (e.g., label, comment). Simply follow the instructions provided by the bot. You will only need to do this once across all repos using our CLA.

This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact [email protected] with any additional questions or comments.

sql-server-language-extensions's Issues

Sql Server 2022, python 3.11

I followed the instructions to build a python 3.11 extensibility dll for SQL Server 2022. But I received a series of warnings on the last step of the build (see attached file with list), so I thought it might not be wise to rely on that dll.

I also noticed that the last date on these project files is over a year ago--perhaps this is not meant to be used with SQL Server 2022? or used to build a python 3.11 (or later) dll? (Sql Server 2022 has instructions for installing with python 3.10 only, so perhaps that is the last version you advise.)

Thank you.
_CompileWarningsAfterBuild.txt

The C# extension targets .NET 5 which goes out of support in May 2022

The C# extension targets a "Current" release, .NET 5, that goes out of support in just 8 months, limiting the usefulness of the current release.

Other adoption issues:

• The extension doesn't work on Linux, which means
• It's impossible to use this extension on official Docker images since the Windows images were withdrawn
• There's still no documentation available that would allow the community to create .NET Core language extensions for C# and (more importantly for data analysis) F#

The Linux/Docker limits even experimentation with this extension.

Besides, the top-level statements and global usings in C# 10/.NET 6 are far more suitable for this kind of scripting

Error (HRESULT 0x80072746) when executing the sp_execute_external_script part of the CSharp demo.

I went through the steps in the Dotnet example and I am getting the message below when executing the "sp_execute_external_script" part of the demo.

Msg 39004, Level 16, State 19, Line 25
A 'Dotnet' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80072746.

Message in Event Viewer:
SQL Server encountered error 0x800704d4 while communicating with the 'Dotnet' runtime for request id: 6B9D5162-000F-4ECC-850C-39844B3F018C. Please check the configuration of the 'Dotnet' runtime.

Searching online I found some info relating to the R and Java extensions but answers were related to supporting libraries for the underlying language.. so didn't get much insight into what might be missing in this Dotnet setup.

Suggestion for edit to "Permissions to execute external language".

The current text is "a user needs to be granted ALL APPLICATION PACKAGES read permissions to the \MSSQL folder" which makes it sound to me that "All APPLICATIONS PACKAGES" is an access right. I think the sentence would make more sense as "the user ALL APPLICATION PACKAGES needs to be granted read permissions to the \MSSQL folder".

Do you have a plan supports nvarchar?

Currently, the extension supports the following data types: SQL_C_SLONG, SQL_C_ULONG, SQL_C_SSHORT, SQL_C_USHORT, SQL_C_SBIGINT, SQL_C_UBIGINT, SQL_C_STINYINT, SQL_C_UTINYINT, SQL_C_BIT, SQL_C_FLOAT, SQL_C_DOUBLE, SQL_C_CHAR. It supports the following SQL data types: int, bigint, smallint, tinyint, real, float, bit, varchar(n).

Do you have a plan supports nvarchar?

Streaming Support

Are there any plans to incorporate streaming support for language extensions? For my specific use case it would be beneficial for instances where a large volume of data is generated on the scripting side for output. Currently it appears that the Execute interface method is only/can only be called a single time by the Extensibility Framework?

C# regex sample "Could not load file or assembly"

Hi all,

I am attempting to get the sample for .Net C# working, but I get the following error in the EXEC sp_execute_external_script step
Error: Could not load file or assembly 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\ExternalLibraries\5\65537\1\regex'. The specified module could not be found. (Full error message below).

The file exists and is indeed the dll of the regex sample that I built and installed successfully. Suspecting permissions issues, I checked permissions on the directory and icacls tells me: ExternalLibraries APPLICATION PACKAGE AUTHORITY\ALL APPLICATION PACKAGES:(I)(OI)(CI)(RX). I have also tried with APPLICATION PACKAGE AUTHORITY\ALL APPLICATION PACKAGES:(I)(OI)(CI)(F) but to no avail.

I am using the .NET C# language extension v1.1.0 on a fresh install of Microsoft SQL Server Developer (64-bit) v 16.0.1000.6, on a Windows 10 Pro (10.0) machine.

I am not sure on how to proceed at this point. Any help would be greatly appreciated.

Thanks in advance!

Msg 39004, Level 16, State 20, Line 1
A 'Dotnet' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004. External script request id is 471D2C07-9D10-47AC-A47F-F805057E320F.
STDOUT message(s) from external script: 
   at System.Runtime.Loader.AssemblyLoadContext.<LoadFromPath>g____PInvoke|5_0(IntPtr ptrNativeAssemblyBinder, UInt16* ilPath, UInt16* niPath, ObjectHandleOnStack retAssembly)
   at System.Runtime.Loader.AssemblyLoadContext.LoadFromAssemblyPath(String assemblyPath)
   at System.Reflection.Assembly.LoadFrom(String assemblyFile)
   at Microsoft.SqlServer.CSharpExtension.DllUtils.GetUserDll(String userClassName, List`1 dllList) in C:\__w\1\s\sql-server-language-extensions\language-extensions\dotnet-core-CSharp\src\managed\utils\DllUtils.cs:line 45Error: Could not load file or assembly 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\ExternalLibraries\5\65537\1\regex'. The specified module could not be found.
   at Microsoft.SqlServer.CSharpExtension.DllUtils.GetUserDll(String userClassName, List`1 dllList) in C:\__w\1\s\sql-server-language-extensions\language-extensions\dotnet-core-CSharp\src\managed\utils\DllUtils.cs:line 62
   at Microsoft.SqlServer.CSharpExtension.CSharpUserDll.Instantiat
STDOUT message(s) from external script: 
eUserExecutor() in C:\__w\1\s\sql-server-language-extensions\language-extensions\dotnet-core-CSharp\src\managed\CSharpUserDll.cs:line 84
   at Microsoft.SqlServer.CSharpExtension.CSharpSession.Execute(UInt64 rowsNumber, Void** data, Int32** strLenOrNullMap, UInt16* outputSchemaColumnsNumber) in C:\__w\1\s\sql-server-language-extensions\language-extensions\dotnet-core-CSharp\src\managed\CSharpSession.cs:line 185
   at Microsoft.SqlServer.CSharpExtension.CSharpExtension.<>c__DisplayClass14_0.<Execute>b__0() in C:\__w\1\s\sql-server-language-extensions\language-extensions\dotnet-core-CSharp\src\managed\CSharpExtension.cs:line 432
   at Microsoft.SqlServer.CSharpExtension.ExceptionUtils.WrapError(Action func) in C:\__w\1\s\sql-server-language-extensions\language-extensions\dotnet-core-CSharp\src\managed\utils\ExceptionUtils.cs:line 35Error: Unable to find user class with full name: UserExecutor.CSharpRegexExecutor
Please provide user class in the form of LibraryName;Namespace.Classname or Namespace.Classname

.NET Core support

With the release of https://devblogs.microsoft.com/dotnet/an-introduction-to-dataframe/ i would love to have support for .NET Core based on the new DataFrame API.

Microsoft.Data.Analysis Latest ver error

Microsoft.Data.Analysis
0.4.0
No problem

Latest 0.19.0
Override error Execute method of AbstractSqlServerExtensionExecutor class

Why and how to fix?

best regards

Any plan to support .net core?

CLR Integration can not ued in linux anymore, I think.
So is there Microsoft Extensibility SDK for .net core for Microsoft SQL Server​?

Error: The type initializer for 'VendorProductsParserLib.Parser' threw an exception.

Hello,

I am new to SQL Server Language Extensions framework development and therefore, ran into some issues which I couldn't resolve by myself. More specifically, I want to use an external library (written in C# on .Net 6.0 platform) and invoke some methods to get string results and pass this result back to SQL Server calling procedure with sqlParams output parameter (SqlDataType varchar(max)). The library method works fine when invoked by console app but fails with the following error messages when invoked from within AbstractSqlServerExtensionExecutor-derived class by running sp_execute_external_script from SQL Server instance. External Language and External Library both are installed correctly and worked properly when I tested with a simple input/output dataset but as soon as I tried to link my DataIntegrationExecutor class (derived as I already mentioned above from AbstractSqlServerExtensionExecutor) I get errors. Below I give a fragment of my code (which I think causes error when initializing type but I can't understand a reason for the error). So, here is a code fragment:

static Parser()
{
httpClient = new HttpClient();
connectionString = $"Server={Environment.MachineName};Initial Catalog={Settings.Default.InitialCatalog};Integrated Security=True";
connection = new SqlConnection(connectionString);
errorLoggingCommand = connection.CreateCommand();
errorLoggingCommand.CommandType = CommandType.StoredProcedure;
errorLoggingCommand.CommandText = "dbo.uspErrorLogging";
connection.Open();
}

Msg 39004, Level 16, State 20, Line 3
A 'CSharp' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
STDOUT message(s) from external script:
nativecsharpextension::Init
DotnetEnvironment::to_utf16_str
DotnetEnvironment::Init
DotnetEnvironment::load_hostfxr
DotnetEnvironment::load_library
DotnetEnvironment::get_export
DotnetEnvironment::get_export
DotnetEnvironment::get_export
DotnetEnvironment::get_dotnet
DotnetEnvironment::get_dotnet_load_assembly
DotnetEnvironment::to_utf16_str
DotnetEnvironment::to_utf16_str
CSharpExtension::Init
nativecsharpextension::InitSession
DotnetEnvironment::to_utf16_str
DotnetEnvironment::to_utf16_str
CSharpExtension::InitSession
CSharpSession::CSharpSession
nativecsharpextension::InitColumn
DotnetEnvironment::to_utf16_str
DotnetEnvironment::to_utf16_str
CSharpExtension::InitColumn
CSharpSession::InitInputColumn
CSharpInputDataSet::InitColumn
nativecsharpextension::InitColumn
DotnetEnvironment::to_utf16_str
DotnetEnvironment::to_utf16_str
CSharpExtension::InitColumn
CSharpSession::InitInputColumn
CSharpInputDataSet::InitColumn
nativecsharpextension::InitParam
DotnetEnvironment::to_utf16_s
STDOUT message(s) from external script:
tr
DotnetEnvironment::to_utf16_str
CSharpExtension::InitParam
CSharpSession::InitParam
CSharpParamContainer::AddParam
nativecsharpextension::Execute
DotnetEnvironment::to_utf16_str
DotnetEnvironment::to_utf16_str
CSharpExtension::Execute
CSharpSession::Execute
CSharpInputDataSet::AddColumns
CSharpInputDataSet::AddColumn
CSharpInputDataSet::AddColumn
CSharpUserDll::InstantiateUserExecutor
at VendorProductsParserLib.Parser.ParseVendorProducts(Vendors vendor, ProductTypes productType) in C:\Users\Zviad\source\repos\SqlCSharpLanguageExtensions\VendorProductsParserLib\Parser.cs:line 468
at DataIntegrationLib.DataIntegrationExecutor.Execute(DataFrame input, Dictionary`2 sqlParams) in C:\Users\Zviad\source\repos\SqlCSharpLanguageExtensions\DataIntegrationLib\DataIntegration.cs:line 24
at Microsoft.SqlServer.CSharpExtension.CSharpSession.Execute(UInt64 rowsNumber, Void** data, Int32** strLenOrNullMap, UInt16* outputSchemaColumnsNumber) in C:__w\1\s\sql-server-language-extensions\language-ext
STDOUT message(s) from external script:
ensions\dotnet-core-CSharp\src\managed\CSharpSession.cs:line 189
at Microsoft.SqlServer.CSharpExtension.CSharpExtension.<>c__DisplayClass14_0.b__0() in C:__w\1\s\sql-server-language-extensions\language-extensions\dotnet-core-CSharp\src\managed\CSharpExtension.cs:line 432
at Microsoft.SqlServer.CSharpExtension.ExceptionUtils.WrapError(Action func) in C:__w\1\s\sql-server-language-extensions\language-extensions\dotnet-core-CSharp\src\managed\utils\ExceptionUtils.cs:line 35Error: The type initializer for 'VendorProductsParserLib.Parser' threw an exception.
nativecsharpextension::CleanupSession
DotnetEnvironment::to_utf16_str
DotnetEnvironment::to_utf16_str
CSharpExtension::CleanupSession
CSharpSession::CleanupSession
CSharpParamContainer:HandleCleanup
CSharpOutputDataSet::HandleCleanup
nativecsharpextension::Cleanup

Is using of System.Data.SqlClient 4.8.5 package allowed in libraries which are loaded by External Library installed as a CSharp Language extension on SQL Server 2019 CU21 instance?

I would also be so grateful if you could instruct how to debug this library from within Visual Studio 2022 because starting debugging SQL Script from Visual Studio (which I used to use for debugging CLR stored procedures) didn't have an expected result i.e. debugging didn't work.

Regards,

Zviad Gogiashvili

Steps given in the description never works

Could you please be more specific on the folder structure , when I download the source code and run "restore-packages.cmd" does not works , could you please more specific in your steps. Basically i am talking about the C# language extension steps which you have mentioned to install studio and then run those .CMD files , but it is not working as it is , do we need to change the CMD files/ or environment variables or any variables in the CMD file ? I tried several ways but it does not works at all.

C# /.NET RegEx sample readme feedback

I got it to work but....

1: Why not simply refer to the 1.0 release .zip iso requiring the user to build?

2: exec sp_configure 'external scripts enabled', 1 is required on the SQL Server (I am using Express)

3: "Create a stored procedure that calls" - unclear, your sample just contains some SQL code

File access Permission Denied for R extension

I have registered external language myR in SQL2019 following tutorial using R v1.1 connector from R-lang-extension-windows-release.zip. When I execute script below, I'm getting Permission Denied error from R. If I run the same R script from R console, no problems reading file. Both Sql Server and Sql Server Launchpad accounts have full Read/Write access to folder and file.
R version is 4.0.5

Similar file access works for SQL-Python connector.

---

EXEC sp_execute_external_script
@language =N'myR',
@script=N'
myData = read.delim("C:\TestDir\Test.txt", header = FALSE)
print(myData);'

---

Msg 39004, Level 16, State 20, Line 8
A 'myR' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
STDOUT message(s) from external script:
Error in file(file, "rt") : cannot open the connection
In addition: Warning message:
In file(file, "rt") :
cannot open file 'C:\TestDir\Test.txt': Permission denied
2022-10-07 16:54:35.50 Exception occurred: Error evaluating: tryCatch(
{

myData = read.delim("C:\TestDir\Test.txt", header = FALSE)
print(myData);
},
error = function(err)
{
stop(err);
}
);

How does this relate to SQL CLR?

I just read the blog post announcing this project. (I would have asked this there but for some reason all commenting is disabled on the SQL blog)

Is this project a replacement for SQL CLR (which as we know is still on .NET Framework)? Is the expectation to have eventually have feature parity?

Thanks.

This repo is missing important files

There are important files that Microsoft projects should all have that are not present in this repository. A pull request has been opened to add the missing file(s). When the pr is merged this issue will be closed automatically.

Microsoft teams can learn more about this effort and share feedback within the open source guidance available internally.

Merge this pull request

java intellisense not working although Language extension installed

I was working on VS code-> 1.38.1 and java-11, IntelliSense was working fine but suddenly stopped working. I have reinstalled java and VS Code but still not working.

Current version mentioned below:

VS Code Version: 1.49.1
Java Version: 11.0.8

java Extension Pack

Language Support for Java(TM) by Red Hat: 67.0
Debugger for Java: 0.28.0
Java Test Runner: 0.24.2
Maven for Java: 0.24.2
Project Manager for Java: 0.13.0
Visual Studio IntelliCode: 1.2.10

Python extension truncates output

Python extension seems to truncate output string to 65535 characters. This is reproducible with SQL Server 2019 and Python 3.7.x and 3.9.x
The code snippet below reproduces the issue, assuming external language myPython is created.

CREATE OR ALTER PROCEDURE dbo.P_TestPython
AS
BEGIN
DECLARE @RequestStr VARCHAR(max)=replicate('A',10000),
@ResponseStr VARCHAR(max),
@i INT=1

WHILE @i<=4 BEGIN
	SELECT @RequestStr=@RequestStr+@RequestStr
	SET @i+=1
END
-- Prints 128000
select len(@RequestStr)

EXEC sp_execute_external_script
	@language=N'myPython',
	@script=N'

print(len(RequestStr))
ResponseStr = RequestStr
',
@params=N'@RequestStr VARCHAR(MAX),@ResponseStr VARCHAR(MAX) OUTPUT',
@RequestStr=@RequestStr,
@ResponseStr=@ResponseStr OUTPUT

-- Prints 65535
select len(@ResponseStr)

END
go
EXEC dbo.P_TestPython
go
-- Output 128000,65535

Are there "security levels" like in SQLCLR?

https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/assemblies/creating-an-assembly?view=sql-server-ver15

Says:

When creating an assembly into a SQL Server database, you can specify
one of three different levels of security in which your code can run:
SAFE, EXTERNAL_ACCESS, or UNSAFE. When the CREATE ASSEMBLY statement
is run, certain checks are performed on the code assembly which may
cause the assembly to fail to register on the server.

Are there equivalent security switches in SQL Language Extensions? What access does an extension have by default? Like can it access the file system or make HTTP requests using WebClient?