Microsoft SQL Server (MSSQLSource)

In an application, right-click on Sources -> select Create source -> type any Source name and select MSSQLSource in the Source type field -> click Create.

• connStr - connection string, e.g. jdbc:mysql://<hostname>:3306/padb_test
• user - user name
• password - password

Using stored procedures implemented in a host programming language (e.g. C#)

Microsoft SQL Server is known to be slow in calling scalar functions from select/where expressions. A solution to efficiently support scalar functions is to implement them in a host language such as C#. We implemented several such functions and you can contact us for example. To call your host-language-implemented functions from SQL you must first register them by creating Assembly. Note that you must have admin permissions to do create an assembly and you do not have such permissions when you use some hosted versions of SQL Servers such as Amazon Relational Database Service (RDS). You can find an example on how to register your C# functions below.

DROP ASSEMBLY IF EXISTS CLRDemo;
Create Assembly CLRDemo from '<path-on-the-sql-server-disk>\MSSQLSource-Functions.dll' with Permission_set = SAFE
GO                    

Recent versions of SQL Server is more restrictive. If you cannot create the assembly, try the following:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE;
EXEC sp_configure 'clr strict security', 0;
RECONFIGURE;

Then create a SQL function as follows:

CREATE FUNCTION [dbo].[fn_funcName](@str [varchar](max))
RETURNS 
   varchar(max) 
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [YourSqlAssemblyName].[YourNameSpace.YouClassName].[YourMethodName]

You can now call it from SQL:

EXEC dbo.fn_funcName ‘This is my first sql clr assembly run’