1,020 questions
72
votes
4
answers
90k
views
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?
- 1,298
48
votes
2
answers
25k
views
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
{
...
- 259k
43
votes
7
answers
104k
views
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 ...
- 1,020
36
votes
5
answers
46k
views
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 ...
- 1,167
27
votes
4
answers
2k
views
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 ...
- 2,245
26
votes
3
answers
28k
views
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 ...
- 9,348
25
votes
4
answers
22k
views
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 ...
- 4,682
22
votes
5
answers
27k
views
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 ...
- 1,885
20
votes
3
answers
14k
views
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 ...
19
votes
5
answers
20k
views
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 ...
- 772
18
votes
4
answers
21k
views
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: ...
- 183
16
votes
12
answers
9k
views
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 ...
- 62.5k
16
votes
2
answers
10k
views
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 ...
- 334k
14
votes
2
answers
23k
views
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....
- 8,328
14
votes
1
answer
5k
views
.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?
- 179
13
votes
2
answers
1k
views
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 ...
- 7,907
13
votes
3
answers
8k
views
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 ...
- 151
12
votes
7
answers
16k
views
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 '...
- 3,084
12
votes
3
answers
10k
views
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 ...
- 5,052
11
votes
2
answers
11k
views
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 = ...
- 190k
11
votes
1
answer
10k
views
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")]
...
- 2,662
11
votes
1
answer
391
views
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 ...
- 6,480
10
votes
1
answer
5k
views
Cannot find the asymmetric key -- because it does not exist or you do not have permission
I am trying to be able to run a .Net dll through SQL using the CLR - I am doing so unsuccessfully.
I am following the instructions here
So I am doing the following:
CREATE ASYMMETRIC KEY ...
- 34.8k
10
votes
2
answers
14k
views
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 ...
- 4,534
10
votes
9
answers
8k
views
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 ...
- 59.1k