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
58
votes
4 answers

Difference between scalar, table-valued, and aggregate functions in SQL server?

What is the difference between scalar-valued, table-valued, and aggregate functions in SQL server? And does calling them from a query need a different method, or do we call them in the same way?
Ehsan Jeihani
  • 1,158
  • 2
  • 9
  • 22
49
votes
14 answers

SQL Server stops loading assembly

We have developed an assembly for SQL Server 2008 R2. The assembly has been working for a week. The managed stored proc inside the assembly was working fine for the whole week and then it stops working. We have been seeing this problem couple…
Harvey Kwok
  • 10,758
  • 6
  • 33
  • 53
44
votes
2 answers

SQL Server: Could not find type in the assembly

Assume the assembly dll: using Microsoft.SqlServer.Server; using System.Data.SqlClient; using System.Data.SqlTypes; using System; using System.Text; namespace CLRFunctions { public class T { [SqlFunction(DataAccess =…
Ian Boyd
  • 220,884
  • 228
  • 805
  • 1,125
44
votes
1 answer

SQL CLR project in vs2012

After installing the release version of VS2012, I am unable to find SQL CLR proect template. How can I go about creating a project of this type. Thanks!
laconicdev
  • 6,085
  • 11
  • 59
  • 88
37
votes
7 answers

How to grant the database owner (DBO) the EXTERNAL ACCESS ASSEMBLY permission?

When I try to create assembly in SQL 2008 from .Net assembly (.Net 3.5) I am getting the below error, error says that I have to set either of the below properties as true, how can I do that? The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY…
Dev
  • 870
  • 2
  • 15
  • 35
35
votes
5 answers

FileLoadException / Msg 10314 Error Running CLR Stored Procedure

Receiving the following error when attempting to run a CLR stored proc. Any help is much appreciated. Msg 10314, Level 16, State 11, Line 1 An error occurred in the Microsoft .NET Framework while trying to load assembly id 65752. The server may be…
homeskillet
  • 1,163
  • 1
  • 10
  • 10
35
votes
5 answers

How to PRINT a message from SQL CLR function?

Is there an equivalent of PRINT 'hello world' which can be called from CLR (C#) code? I'm trying to output some debug information in my function. I can't run the VS debugger because this is a remote server. Thanks!
Serguei
  • 2,833
  • 3
  • 22
  • 32
33
votes
6 answers

SQL Server: How to list all CLR functions/procedures/objects for assembly

Question: In SQL Server 2005, how can I list all SQL CLR-functions/procedures that use assembly xy (e.g. MyFirstUdp) ? For example a function that lists HelloWorld for query parameter MyFirstUdp CREATE PROCEDURE HelloWorld AS EXTERNAL NAME…
Stefan Steiger
  • 68,404
  • 63
  • 337
  • 408
28
votes
4 answers

SQLCLR database name adds _1 every time I make a change to the project

This is a new phenomenon I am seeing, my Database name is: MySQLCLR, there is a script that always give this name in it: :setvar DatabaseName "MySQLCLR" all of a sudden now, everytime I make any change to my sqlclr project code and re-compile, the …
Pacman
  • 1,955
  • 2
  • 32
  • 64
25
votes
2 answers

The dbType NVarChar is invalid for this constructor

[Microsoft.SqlServer.Server.SqlProcedure] public static void MyMethod() { string connectionString = "context connection=true"; using (SqlConnection connection = new SqlConnection(connectionString)) { …
P.Brian.Mackey
  • 39,360
  • 59
  • 210
  • 327
24
votes
6 answers

SQLCLR using the wrong version of the .NET Framework

During a recent restart of our development server the SQL Server started using .NET 4.0 for the SQLCLR. This means that nothing using the CLR in SQL works, or at least that's my understanding by reading these…
Tobias Rundbom
  • 436
  • 1
  • 3
  • 12
22
votes
5 answers

Failed to CREATE AN ASSEMBLY in SQL

I've already dived into SQL clr programming. Unfortunately my first attempt is troubled. My C# assembly code is just so: enter code here public partial class FirstCLRRoutines { public static int GetCLRFrameworkMajorVersion() { return…
Mesut
  • 1,745
  • 4
  • 21
  • 29
20
votes
7 answers

Extracting a .NET Assembly from SQL Server 2005

I am trying to help a personal friend (who now also is a client) with a SQL CLR related problem. He has a SQL Server with a database that has 3 .NET assemblies embeded in it. He asked me to help him extract the assemblies from within the database…
Leo Bontemps
  • 417
  • 1
  • 4
  • 9
19
votes
3 answers

Create CLR stored procedure using the dll created by .net framework 4.0 in sql server 2008. Is shows error

I am using the below code for CLR stored procedure creation. While I am creating the assembly. it shows the below issue. My target framework is 4.0. sql server is 2008 r2 SQL code: create assembly SampleSearch from 'E:\CLR…
Kirupananthan.G.S
  • 255
  • 1
  • 3
  • 9
19
votes
1 answer

Problems Registering Oracle.DataAccess as SQLCLR assembly in MS SQL Server 2012

EDIT 3.5 (Meant to put Item 3 below in the last update, but overlooked. Alas...) Looking at how the assembly registration fails for my problem and looking at what limited info I am able to infer from ProcExplorer traces leads me to make some…
David W
  • 9,822
  • 30
  • 59
1
2 3
…
64 65