Stack Overflow
Stack Overflow

Questions tagged [sqlclr]

SQLCLR (SQL Common Language Runtime) is technology for hosting of the Microsoft .NET common language runtime engine within SQL Server. The SQLCLR allows managed code to be hosted by, and run in, the Microsoft SQL Server environment.

SQL CLR or SQLCLR (SQL Common Language Runtime) is technology for hosting of the Microsoft .NET common language runtime engine within SQL Server. The SQLCLR allows managed code to be hosted by, and run in, the Microsoft SQL Server environment. This technology, introduced in Microsoft SQL Server 2005, allow users for example to create the following types of managed code objects in SQL Server in .NET languages such as C# or VB.NET.

  • Stored procedures (SPs) which are analogous to procedures or void functions in procedural languages like VB or C,
  • triggers which are stored procedures that fire in response to Data Manipulation Language (DML) or Data Definition Language (DDL) events,
  • User-defined functions (UDFs) which are analogous to functions in procedural languages,
  • User-defined aggregates (UDAs) which allow developers to create custom aggregates that act on sets of data instead of one row at a time,
  • User-defined types (UDTs) that allow users to create simple or complex data types which can be serialized and deserialized within the database.

The SQL CLR relies on the creation, deployment, and registration of .NET assemblies, which are physically stored in managed code dynamic load libraries (DLLs). These assemblies may contain .NET namespaces, classes, functions and properties.

Source: http://en.wikipedia.org/wiki/SQL_CLR

1004 questions
13
votes
4 answers

Advantage of SQL SERVER CLR

What advantages does SQLServer CLR offer over T-SQL? Is using .NET syntax easier than T-SQL? I see that you can define user types, but I'm not quite clear on why that's better. For example, you could define an email type and it would have a prefix…
Anthony Potts
  • 8,842
  • 8
  • 41
  • 56
13
votes
4 answers

How to register System.DirectoryServices for use in SQL CLR User Functions?

I am porting an old 32-bit COM component that was written in VB6 for the purpose of reading and writing to an Active Directory server. The new solution will be in C# and will use SQL CLR user functions. The assembly that I am trying to deploy to…
Saul Dolgin
  • 8,624
  • 4
  • 37
  • 43
12
votes
3 answers

How do I update an assembly and its dependent assemblies in MS-SQL?

This is the situation: I have a Trigger.dll and a Trigger.XmlSerializer.dll. I use CREATE ASSEMBLY to register them in MSSQL. Now, I have compiled new versions of both. I want to use ALTER ASSEMBLY to update them, however you can only update one at…
Chris KL
  • 4,882
  • 3
  • 27
  • 35
12
votes
7 answers

SQL Server 2008 and HashBytes

I have quite a large nvarchar which I wish to pass to the HashBytes function. I get the error: "String or binary would be truncated. Cannot insert the value NULL into column 'colname', tbale 'table'; column does not allow nulls. UPDATE …
Bob
  • 3,074
  • 11
  • 43
  • 62
11
votes
4 answers

What are the pitfalls of using sql_variant?

I've read and heard several times that sql_variant should be avoided. I think I have a great use case for it. I've used varchar(max) in the past to store different types in the same column, but it seems sensible to avoid the de/serialization…
Daniel
  • 47,404
  • 11
  • 101
  • 179
11
votes
3 answers

The specified option 'clr enabled' is not supported by Azure SQL Edge

I'm a .NET developer and new to mac (m1 pro). I use docker and pull the azure-sql-edge image. When I try to create a table with a geometry column or insert a value in the geometry type column that was generated by code first solution, I encounter…
Abbas Behjatnia
  • 131
  • 1
  • 5
11
votes
1 answer

.Net Core assemblies in SQL CLR

Can .Net Core 3 assemblies be used in SQL CLR? And if not so, what will happen with the arrival of .Net 5? Or is it only possible to use only .Net Standard 2 and implement it as >net Framework?
Lorin_F
  • 149
  • 1
  • 7
11
votes
3 answers

How is a CLR table valued function 'streaming''?

The MSDN Docs on table-valued Sql Clr functions states: Transact-SQL table-valued functions materialize the results of calling the function into an intermediate table. ... In contrast, CLR table-valued functions represent a streaming …
Robert Jeppesen
  • 7,837
  • 3
  • 35
  • 50
11
votes
1 answer

Computed columns in SQLCLR project in Visual Studio 2010

It appears that MS has a bug when dealing with VS 2010 SQL CLR project and computed columns. I am using Pre/PostDeployScript.sql to drop/add the computed column. However, if I try to deploy from VS2010 it, I get dependency errors. The same project…
laconicdev
  • 6,360
  • 11
  • 63
  • 89
11
votes
4 answers

SSDT SQL Server Debugging Doesn't Hit CLR Breakpoints

I applied the SQL Server Data Tools patch to Visual Studio 2012 (Premium) and created a SQL Server CLR user-defined function project in C#: public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static…
Buggieboy
  • 4,636
  • 4
  • 55
  • 79
10
votes
2 answers

CLR Stored Procedures: how to set the schema/owner?

I am working on a Linq based CLR Stored Procedure for some complex filtering and manipulation, which would otherwise require a lot of messy and poorly performant T-SQL code, if implemented in a more "traditional" Stored Procedure. This is working…
Vito Botta
  • 395
  • 3
  • 10
10
votes
2 answers

SqlFunction fails to open context connection despite DataAccessKind.Read present

I've got a SqlServer project with a very simple test for a Table-Valued-Function:- [SqlFunction(TableDefinition = "forename nvarchar(50)", FillRowMethodName = "TestFillRow", DataAccess = DataAccessKind.Read)] public static IEnumerable TestConn(int…
AnthonyWJones
  • 187,081
  • 35
  • 232
  • 306
10
votes
1 answer

CLR Table-valued function with array argument

I have a SQL CLR function like this one: public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction(TableDefinition = "number int", FillRowMethodName = "FillRow")] public static IEnumerable MyClrFunction(object obj)…
Václav Dajbych
  • 2,584
  • 2
  • 30
  • 45
10
votes
9 answers

Get the T-SQL CREATE statement for SQLCLR stored procedures

I have an application that retrieves the text of stored procedures using sp_helptext. It works great on all my stored procedures except for CLR stored procedures. If I try to use sp_helptext on a SQLCLR stored procedure, I get this error: There…
JosephStyons
  • 57,317
  • 63
  • 160
  • 234
10
votes
2 answers

VS SQLCLR: Function X has unresolved reference to schema Y

I am creating a new SQL CLR using Visual Studio 2013 and in the Project Properties have set the Default Schema to 'decASM' (was 'dbo'). When I make this change and rebuild the project VS generates a sql file as…
BrianKE
  • 4,035
  • 13
  • 65
  • 115
1 2
3
…
66 67