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
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,568
  • 3
  • 24
  • 33
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,034
  • 11
  • 41
  • 61
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
  • 46,089
  • 10
  • 89
  • 172
11
votes
1 answer

Which version of .NET framework SQL Server supports?

I want to create SQLCLR based stored procedures for SQL Server. As you know I can build a dll against multiple versions of .NET framework. I can't find a reference that describe which version of SQL Server supports which version of .NET framework.…
Just a learner
  • 21,448
  • 45
  • 133
  • 206
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,085
  • 11
  • 59
  • 88
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,338
  • 4
  • 48
  • 77
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
  • 385
  • 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
  • 178,910
  • 32
  • 227
  • 302
10
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,671
  • 3
  • 32
  • 50
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,349
  • 1
  • 27
  • 43
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
  • 53,646
  • 63
  • 151
  • 228
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
  • 3,735
  • 11
  • 58
  • 102
10
votes
2 answers

Pass table as parameter to SQLCLR TV-UDF

We have a third-party DLL that can operate on a DataTable of source information and generate some useful values, and we're trying to hook it up through SQLCLR to be callable as a table-valued UDF in SQL Server 2008. Taking the concept here one step…
Skeolan
  • 3,738
  • 2
  • 17
  • 20
10
votes
4 answers

What are IBinarySerialize Interface methods used for?

When you create a custom aggregate function you need to specified the enumeration format: Format Enumeration is used by SqlUserDefinedTypeAttribute and SqlUserDefinedAggregateAttribute to indicate the serialization format of a user-defined type…
gotqn
  • 36,464
  • 39
  • 145
  • 218
9
votes
2 answers

SQL CLR Debugging getting "Canceled by user"

When I'm trying to debug a SQL CLR via a SQL Test script in VS2010 (with MSSQL 2008) I get "Canceled by user" in the Debug output window as soon the deployment finish (which is successful.) I have been up and down the Internet and tried all found…
Quintium
  • 469
  • 5
  • 21
1 2
3
…
64 65