Questions tagged [sql-clr]

SQL CLR is Microsoft's Common-Language-Runtime implementation for SQL Server. Use this tag for questions about how to implement and troubleshoot SQLCLR on Microsoft SQL Server.

Filter by
Sorted by
Tagged with
4 votes
2 answers
2k views

Is there a way to use standard CLR functions on Azure SQL Edge for Ubuntu Docker on an M1 Mac?

I have a MacBook with an M1 chip, so (about) the only option for me to run SQL Server is to run it as a Docker container. This works fine for standard SQL, but our application uses some CLR features ...
  • 2,129
1 vote
1 answer
1k views

Sql Server 2019, migrating clr assemblies, clr strict security

We have some old code running in Sql Server 2008 servers, and we're looking to upgrade to Sql Server 2019. The old clr code is really old (like .net framework 2.0 old), so I knew I'd have to rebuild ...
2 votes
1 answer
279 views

Load assembly from a certificate in SQL Server

We received an assembly from external vendor as CREATE ASSEMBLY MYCALC_DLL AUTHORIZATION dbo FROM 0x42A728....<300,000 binary values> WITH PERMISSION_SET = UNSAFE GO And this DLL is been ...
  • 340
1 vote
0 answers
48 views

Service Broker's SEND statement causes security context switch

We have triggers in multiple tables that will send messages to a Service Broker service upon INSERT, UPDATE and DELETE operations. These operations may be nested as parts of bigger, transactional ...
  • 111
0 votes
1 answer
54 views

I am wanting to use UDTT in my database, but we have CLR turned off, and according to the docs for create type, all types require a CLR assembly

Is this true? Or can I use User defined Table Types with having CLR off? (I.e. they don't require the use of CLR) If they do require CLR, is there an alternative I can use for my stored procedure ...
3 votes
2 answers
266 views

Is using CLR for regular expressions safer than using external scripts?

Problem The main problem we need to use regular expression on MS SQL Server 2019, with the capability of at least the level on the POSIX Regular expression. Possible solutions This Q/A from ...
  • 335
6 votes
1 answer
453 views

How to return several arrays of doubles as a table in C# SQLCLR function

What I have is a CLR SqlFunction that produces 3 arrays of doubles. I would like this function to return something appropriate so that the FillRowMethod can output it for me as a table in T-SQL. It ...
  • 249
2 votes
1 answer
116 views

SQL Server Assembly on Failover cluster after Framework update

We have database that relies on a CLR assembly. The assembly references other assemblies. After a Windows Framework update we need to drop and reinstall the assembly to have it work. Now the issue is ...
  • 133
8 votes
1 answer
1k views

Security implications of enabling CLR on shared instance

My database is currently isolated in its own instance and VM. My customer is retiring that server and I need to migrate the DB to a new environment. My DB uses CLR, so the new environment needs to ...
  • 105
0 votes
1 answer
150 views

MS SQL - CLR Stored Procedure runs as deleted user

I have a SQL Server 2017 database which contains some CLR stored procedures. When trying to call those CLR SP's, I get a "login failed" error back from the CLR SP. My user is connected and works ...
  • 147
1 vote
1 answer
80 views

View T-SQL in CLR Assembly

I have a production database with numerous scalar functions. My goal is to create set based alternatives for reporting. Unfortunately, I don't have a good way to view the T-SQL actually being executed ...
5 votes
1 answer
424 views

System.Web in SQL Server CLR Function

I have done some light research into this topic and I would like to know what are all the pro's and con's or enabling/registering this particular .dll within SQL Server? Back information - we are ...
7 votes
2 answers
4k views

Unknown,AppDomain 66 (master.sys[runtime].65) is Marked for Unload Due to Memory Pressure

I have noticed this error occasionally in the SQL error log: spid20s,Unknown,AppDomain 79 (master.sys[runtime].78) is marked for unload due to memory pressure. I am using SQL Server 2016, SP1 CU5 (...
  • 425
2 votes
2 answers
263 views

How to make Indexed Views work for SQLCLR?

In SQL Server, the indexed view is a hellscape of limitations. But I need one. I have a formatter SQLCLR function that creates a prettified version of a domain key - users want to be able to search ...
  • 131
5 votes
2 answers
1k views

CLR Stored Procedure on Azure SQL Managed Instance error on execution: "Assembly in host store has a different signature than assembly in GAC"

I have a CLR stored procedure which executes correctly when deployed to local SQL Server instances from SQL Server 2012 - 2017. I can successfully deploy to an Azure SQL Managed Instance but when I ...
  • 117
8 votes
3 answers
8k views

Access to the path 'c:\some\path' is denied for MSSQL CLR

I think this is a permissions problem, but I'm having trouble locating it. I have a group of CLRs on one server (SQL Server 2016) and they work as they should. All are marked UNSAFE and they do ...
  • 317
6 votes
1 answer
568 views

Is it safe to use Strings instead of SqlStrings for CLR function input parameters?

I have a CLR scalar UDF implemented through C# code. I've noticed that using the String data type for input parameters significantly improves performance compared to the SqlString data type. In ...
  • 30.9k
9 votes
1 answer
452 views

Apparently, my CLR assembly function is causing deadlocks?

Our application needs to work equally well with an Oracle database or a Microsoft SQL Server database. To facilitate this, we created a handful of UDFs to homogenize our query syntax. For example, SQL ...
2 votes
1 answer
277 views

Bind dll in SQL Server

I have a little side project at my work. My task is to deploy some SQLCLR code into our dev-database (SQL Server 2017) to test it. If the tests are successfull, we want to develop triggers and ...
4 votes
1 answer
3k views

Added .Net assembly to SQL CLR without turning TRUSTWORTHY ON

I'm trying to add "System.Messaging.dll" using the guidance provided by Solomon Rutzky in his post Assembly deployment with permission UNSAFE or EXTERNAL_ACCESS using asymmetric key but I'm failing at ...
3 votes
2 answers
1k views

CREATE ASSEMBLY System.DirectoryServices.AccountManagement.dll without enabling TRUSTWORTHY

First, this is for SQL Server 2016. If I was on 2017+, I would be using sp_add_trusted_assembly. Just wanted to clarify that before asking the question. How do you register the assembly System....
2 votes
2 answers
321 views

How to import Azure blob file into Sql Server 2008

Is there a way to import Blob files into sql server 2008 other than using CLR ? The file size is around 300MB so CLR isn't efficient enough. Is there a possibility to use BULK INSERT or OPENROWSET in ...
31 votes
4 answers
2k views

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

As part of our ETL process, we compare rows from staging against the reporting database to figure out if any of the columns have actually changed since the data was last loaded. The comparison is ...
  • 30.9k
3 votes
1 answer
2k views

Error publishing SQLCLR C# Function using Visual Studio 2017

I'm trying to (learn) publish a SQLCLR function using Visual Studio 2017. (It's a simple function that sends an email.) As a reference I used this articles on CodeProject and MSSQLTips: Create, Run, ...
  • 23k
4 votes
1 answer
571 views

SQL Function, get value to send to CLR

I have a CLR to use the powershell to have access to the RichTextBox Object from .net. On the SQL Function I have created like this: CREATE FUNCTION [dbo].[FicheiroChines](@rtf [nvarchar](max), @...
  • 145
2 votes
2 answers
2k views

Assembly deployment with permission UNSAFE or EXTERNAL_ACCESS using asymmetric key

When we are trying to deploy an assembly with PERMISSION_SET=UNSAFE or EXTERNAL_ACCESS, we are getting following error message by SQL Server: CREATE ASSEMBLY for assembly 'System.ServiceModel....
3 votes
1 answer
604 views

How to pass an array of arguments to User Defined Aggregate in SQL Server

I have a custom aggregation (UDA) and I want to pass an array of arguments like so: select dbo.MyAggregation( t.type, N'My aggregation property', t.param1, t.param2, t.param3 ) from ...
  • 33
6 votes
1 answer
176 views

Table with persisted computed CLR originated data marked with has_unchecked_assembly_data = 1, but DBCC CHECKTABLE does not unset this same flag

We have an OrderLines table with columns: Quantity int not null QtyCancelled int not null QtyBackorder int not null QtyPicking int not null QtyPacking int not null QtyShiped int not null with ...
  • 131
0 votes
2 answers
3k views

Error 0x80131904 failed to load assembly id 65536 / HRESULT: 0x8013150A for microsoft.sqlserver.integrationservices.server

I am running SSIS Packages through an Agent job and getting error 0x80131904: Message: Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version ...
7 votes
4 answers
1k views

Experiencing CLR error on SQL Azure DB

Out of nowhere we started seeing this error and it seems to occur fairly frequently when making calls to the database or any other database in our Elastic Pool. The DTUs aren't being maxed out and the ...
0 votes
0 answers
31 views

SSMS sessions calling CLR packages intermittently stops processing completely, but SSMS appears to still be processing

It's a third party app on SQL Server 2008R2. In Test, they are trying to run some reporting, and they have to use the SPs with many built in CLR functions. Running the script in SSMS on a system with ...
  • 63
7 votes
1 answer
2k views

Does lightweight pooling disable built-in CLR facilities?

Running SQL Server in fibre mode (lightweight pooling) disables the use of SQL CLR: Common language runtime (CLR) execution is not supported under lightweight pooling. Disable one of two options: "...
  • 73.8k
1 vote
0 answers
200 views

Poor UnionAggregate (CLR aggregate) performance - CPU cost hint?

I'm trying to use SQL Server Spatial to work with data related to geographic regions defined by a third party, and am having trouble getting acceptable performance. The following query takes quite a ...
1 vote
1 answer
3k views

Why is my SQL Server reporting CLR_AUTO_EVENT waits when I don't even use SQL CLR?

When I run this query, I see high wait time for CLR_AUTO_EVENT: SELECT * FROM sys.dm_os_wait_stats WHERE wait_type IN ( 'CLR_AUTO_EVENT', 'CLR_CRST', 'CLR_JOIN'...
7 votes
2 answers
2k views

Which CLR Stored Proc is causing memory pressure leading to sqlservr.exe terminating unexpectedly?

Every few days my SQL Server Instance terminates unexpectedly and restarts. In the logs, see: A fatal error occurred in the .NET Framework common language runtime. SQL Server is shutting down. (...
  • 169
1 vote
1 answer
1k views

SQL Server CLR method failing due to TLS 1.0 shutdown

I have an old CLR assembly that was using .Net 2 and when our TLS 1.0 connections were shut down for security reasons, the calls I was making out of the assembly (rest posts to https end point) we ...
6 votes
1 answer
297 views

SQL Server 2016 concurrency limitations? Tuning for db concurrency

I have a table with ~1 billion timestamped records, and each record holds an FK to a session table (one session per day & 3-500,000 records per day), so finding the records for a given day is ...
  • 163
1 vote
1 answer
169 views

Creating SQL Assembly error Method parent has circular class type parameter constraints

I'm trying to add an assembly to SQL server 2012 but I'm getting the following errors. I've tested the dll and it works fine from C# so don't think it's a code issue. Any suggestions? Msg 6218, ...
  • 689
4 votes
1 answer
592 views

Can I extend SQL Server with C++ or C?

So, I created a PE32+ DLL, in an attempt to load it into SQL Server, $ file example_dll.dll example_dll.dll: PE32+ executable (DLL) (console) x86-64, for MS Windows but when I try to compile it as ...
  • 57.3k
3 votes
1 answer
3k views

SQL Parallel Stored Procedure execution using CLR - Performance

We have a main analytics SP that calls 100 other sub SPs, all of which act upon the same set of data (Claims), check it for some business rules, and output discrepancies. The SPs need not all be run ...
  • 1,096
3 votes
1 answer
945 views

T-SQL: CSV->table pipeline with custom parsed numeric data, lookup values

Problem Wish to insert thousands of records into a table [Child] from a CSV file (and do this from tens of files into the same table). However, I want to A. substitute a lookup value into the table (...
  • 968
3 votes
1 answer
418 views

How to enable CLR on a remote SQL Server?

I have been created a dll for SQL Server 2014 to add some custom functions for my stored procedures for my asp.net websites. I don't have any problem in the local server but can't enable the CLR ...
6 votes
2 answers
765 views

Why would I get PREEMPTIVE_OS_AUTHORIZATIONOPS using tSQLt when Working at Home?

I have an SSDT project containing tSQLt unit tests. I always find when working at home that publishing this and running all tests (from a post deploy script) is problematic (against both localdb and ...
  • 78.6k
3 votes
1 answer
5k views

Error Msg 10314, Level 16, State 11 with SAFE assembly after upgrade to SQL Server 2017

I have a pre-SQL Server 2017 database that I restored into / upgraded to SQL Server 2017. In this database I have a SQLCLR Assembly. The Assembly is marked as SAFE since it doesn't do anything ...
2 votes
0 answers
3k views

SQL Server CLR 'Failed to create AppDomain' debugging

I am having the 'Failed to create AppDomain' issue in a production instance. This is typically attributed to memory pressure(not the case here) or pending .Net updates. The OS has been restarted and ...
3 votes
1 answer
295 views

UPDATE STATISTICS and User-Defined Types (UDT) from Assemblies

I have a problem with UPDATE STATISTICS where the column is a user-defined type (UDT) from an assembly. Step by step: Assembly: CREATE ASSEMBLY [UDT.TParams] FROM 0x4D...(stripped) WITH ...
2 votes
1 answer
697 views

SSDT Publish fails because 'System.DirectoryServices' failed verification

Imported an existing database (SQL2012) to an SSDT project in VS2017 and when trying to deploy it to my local instance (SQL2016) I get the error message: CREATE ASSEMBLY for assembly 'System....
3 votes
1 answer
125 views

Compile TSQL stored procedures into assembly

I am trying to protect my business logic and I don't want to use "With Encryption" as it is not enough and can be broken. I don't want to re-write the procedures again in CLR language. Is there an ...
  • 131
3 votes
1 answer
1k views

Assembly 'EDCLR' references assembly 'system.servicemodel, version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089 error

I'm try to create clr function in c# with this code: [SqlFunction] public static string Decrypt(string Input) { var test = ""; var url = "http://www.url/feed/";...
  • 31
4 votes
1 answer
15k views

Assembly is not authorized for PERMISSION_SET=UNSAFE when creating a CLR assembly

The login running all these commands is a member of sysadmin server role on a SQL 2012 Developer instance. It is the owner of the DB where this is being deployed to. EXTERNAL ACCESS ASSEMBLY ...
  • 911