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

969 questions
18
votes
4 answers

Does or does not SQL Azure support CLR assemblies?

We've got a couple of on-premises dbs and we're seeing if we can migrate them to SQL Azure. Some of those dbs have a couple of user defined functions written in C# in an assembly (SAFE). After running a search, I've found a couple of posts which…
Luis Abreu
  • 3,136
  • 4
  • 28
  • 51
17
votes
3 answers

CLR Strict Security on SQL Server 2017

MSDN on this article says: CLR uses Code Access Security (CAS) in the .NET Framework, which is no longer supported as a security boundary. A CLR assembly created with PERMISSION_SET = SAFE may be able to access external system resources, call…
Jesús López
  • 6,739
  • 2
  • 26
  • 55
17
votes
2 answers

Why is HashSet attributed with MayLeakOnAbort, but Dictionary not?

I noticed when trying to code a CLR procedure for SQL Server that HashSet is not allowed due to being attributed with [HostProtectionAttribute(SecurityAction.LinkDemand, MayLeakOnAbort = true)]. SQL Server CLR procedures do not allow the use of…
BlueMonkMN
  • 23,447
  • 8
  • 73
  • 134
17
votes
5 answers

How to notify a windows service(c#) of a DB Table Change(sql 2005)?

I have a table with a heavy load(many inserts/updates/deletes) in a SQL2005 database. I'd like to do some post processing for all these changes in as close to real time as possible(asynchronously so as not to lock the table in any way). I've looked…
Mike Tours
  • 741
  • 2
  • 6
  • 13
16
votes
12 answers

Are CLR stored procedures preferred over TSQL stored procedures in SQL 2005+?

My current view is no, prefer Transact SQL stored procedures because they are a lighter weight and (possibly) higher performing option, while CLR procedures allow developers to get up to all sorts of mischief. However recently I have needed to debug…
Ash
  • 57,515
  • 31
  • 146
  • 168
16
votes
2 answers

CLR SQL Assembly: Get the Bytestream?

I have a SQL CLR dll I want to deploy, but have found you can embed the byte stream/varbinary_literal/ varbinary_expression/assembly bits into a text file to get around the messy hassle of packaging a DLL and making sure it's accessible for the…
OMG Ponies
  • 300,587
  • 73
  • 490
  • 482
16
votes
6 answers

What are good problems to solve using CLR stored procs?

I have used CLR stores procedures in SQL server for awhile now, but I'm still wondering what the best situations to use them are. MSDN provides some guidelines for use like heavy string manipulation (regex), or replacing T-SQL that declares lots of…
Tj Kellie
  • 5,927
  • 2
  • 27
  • 37
16
votes
3 answers

SSRS 2012 Custom Code .NET Framework 4.0 not supported

I was very hopeful that since SQL Server 2012 SQLCLR supports .NET 4.0 that SQL Server Reporting Services (SSRS) 2012 would support .NET 4.0 custom code assemblies. When attempting to upload an rdl (via the Report Manager or via RSBuild) that…
Adam Caviness
  • 3,255
  • 30
  • 36
14
votes
3 answers

Unable to create the tSQLtCLR assembly in SQL Server 2017

I recently installed SQL Server 2017 Express and localdb (general availablity). While attempting to install the tSQLt framework I've discovered a new security feature implemented in SQL Server 2017: the "clr strict security" option. This new…
Brent
  • 143
  • 1
  • 1
  • 5
14
votes
5 answers

Multithreaded caching in SQL CLR

Are there any multithreaded caching mechanisms that will work in a SQL CLR function without requiring the assembly to be registered as "unsafe"? As also described in this post, simply using a lock statement will throw an exception on a safe…
Matt Johnson-Pint
  • 197,368
  • 66
  • 382
  • 508
13
votes
1 answer

Is it possible to create table-valued *methods* in a SQL CLR user-defined type?

I have a CLR UDT that would benefit greatly from table-valued methods, ala xml.nodes(): -- nodes() example, for reference: declare @xml xml = '12510' select c.value('.','int') as id from @xml.nodes('/id') t (c) I…
Peter Radocchia
  • 10,032
  • 1
  • 29
  • 55
13
votes
2 answers

Should I run F# in SqlClr?

I need to run .Net code in Sql and I'm trying to decide between F# and C#. I'm doing more and more code in F# nowadays so if it's not too impractical, I'd like it to be F#. Is it possible to coerce VS2010 to deploy my F# assemblies (and their…
Robert Jeppesen
  • 7,671
  • 3
  • 32
  • 50
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,037
  • 7
  • 38
  • 54
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,046
  • 4
  • 35
  • 42
12
votes
2 answers

Sql Server CLR load assembly failed

I am trying to deploy an CLR TVF (table value function). In the code I am using JavaScriptSerializer to parse some JSON string, so I reference the System.Web.Extensions dll and my problems start there. Project builds fine, but when I try to…
Klark
  • 7,530
  • 3
  • 32
  • 59
1
2
3
…
64 65