LandLord325 on Fri, 14 Oct 2016 02:47:04
Hi, I wrote a class library in vb.net(VS2015). Basically a set of functions to determine dates. I want to be able to use that library in SQL Sever. I am using SQL Server 2014/2016.
I think I've found bits and parts of how to do but I must be missing something. I've not tired it yet, but have just started looking up information.
Is this doable? What is it going to take? Where is a good place to get started on reading about this?
Olaf Helper on Fri, 14 Oct 2016 04:15:15
Sure it is possible (with some limitations), see Introduction to SQL Server CLR Integration
Bob Beauchemin on Fri, 14 Oct 2016 04:31:21
What you're going to end up doing is:
1a. Move your assembly to a directory that SQL Server (specifically the SQL Server service account) has access to it.
1b. Enable SQLCLR in your SQL Server instance with sp_configure (https://technet.microsoft.com/en-us/library/hh239760.aspx)
2. Choose a database to deploy your assembly. Use CREATE ASSEMBLY, pointed at the assembly location, to catalog it to the database. If you're referencing system assemblies that are not supported by SQL Server, you may get (hopefully correctable) errors at this point.
3. Use CREATE FUNCTION or CREATE PROCEDURE to catalog the .NET functions in your assembly to be available to SQL Server as SQL Server functions. To do this you'll need to correspond your CLR datatypes to SQL Server datatypes with this chart: https://msdn.microsoft.com/en-us/library/ms131092.aspx
There are VS SQL Server projects and also SSDT (SQL Server data tools) that do things like automatic DDL generation and deployment, but trying it "by hand" may give you a better understanding of what's going on.
To start out with SQL Server CLR concepts, start here: https://msdn.microsoft.com/en-us/library/ms131046.aspx There should be example code and DDL you can try in this section.
For specific information about SQL Server CLR user-defined functions, start here: https://msdn.microsoft.com/en-us/library/ms131077.aspx
AV111 on Fri, 14 Oct 2016 05:04:03
Please click Mark As Answer if my post helped.
LandLord325 on Tue, 18 Oct 2016 03:25:59
Ok, I have read through most of what was posted here... but I am missing the function part... and I think that is due to that fact that I don't understand how to fully created it...
the function I'll call from my assemble requires a DATE and will return a string....
Bob Beauchemin on Tue, 18 Oct 2016 16:19:49
In order to be definable as a SQL Server function (with CREATE FUNCTION https://msdn.microsoft.com/en-us/library/ms131043.aspx ) your function must be defined in your .NET assembly as a public shared (static) method in a public class. CREATE FUNCTION creates a SQL Server user-defined function and your assembly is called when you call that function in SQL. In your example, you need to use CREATE FUNCTION to create a scalar function in SQL Server. This database function will be called using SQL. The example (link is above) uses SQL inside the user-defined function as well; here's another example that doesn't use SQL inside the function: http://www.codeproject.com/Articles/680161/Getting-Started-With-SQL-Server-CLR-User-Defi
CREATE FUNCTION requires SQL data types that correspond to your .NET data types. Chart of data type correspondence is here: https://msdn.microsoft.com/en-us/library/ms131092.aspx
SQL Server has no "string" data type, the correspondence is NVARCHAR([string length]) to string. You can use SqlString instead of string in your assembly if your function could return database-NULL.
.NET has no "date" data type, the correspondence is to .NET SqlDateTIme or DateTime.
I don't understand "the function I'll call from my assemble". You call SQL Server user-defined functions by using (only) SQL from the client (e.g. *client* code would contain something like SELECT dbo.myfunction(@mydate) after connecting to the database).
LandLord325 on Sun, 23 Oct 2016 16:53:24
That is awesome thank you.... I went back and read the documentation.. the part I was missing was in creating function..... Seems if the assembly name is long.... it needed to be enclosed with the 
Bob Beauchemin on Sun, 23 Oct 2016 18:49:41
Optional brackets around a name (or part of a multi-part name) is part of T-SQL syntax. It's required in class names when the name contains a character like a dot (which multi-part class names do). Watch out in VB class names because the default is some VB Visual Studio projects is to have a "default namespace" named after the project, which must be included in the DDL CREATE statement, unless it's removed in the project properties.