Skip to main content

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
3 votes
2 answers
297 views

How can I access SQLCLR assembly functions in MySQL

We have an SQLCLR assembly (DLL-file) built in C# that contains about 100 functions that we call from store procedures in SQL Server databases. Is is possible to call these functions in MySQL store ...
user291050's user avatar
1 vote
0 answers
338 views

CLR unloaded due to security data definition language

Today, we got a very weird situation. We added a SQL login to our database server. After adding this login, SQL unloaded a critical CLR routine "due to security data definition language" (...
Wilfred van Dijk's user avatar
2 votes
2 answers
953 views

Security Considerations/Risks/Best Practices for Enabling CLR Integration (Assemblies) for SSIS Catalog

The major pre-requisite in utilizing SSIS Catalog is enabling CLR Integration. The instance in question is running SQL Server 2014. I'm looking for feedback and resources I can mine which specifically ...
user3621633's user avatar
0 votes
1 answer
463 views

Do Azure SQL and SQL Server still call-out into the CLR for FORMAT and PARSE? If so, which CLR version and how does marshalling affect performance?

The documentation for SQL Server's T-SQL FORMAT function implies calls are forwarded to .NET's ToString methods on .NET types corresponding to T-SQL types, and TRY_PARSE and PARSE's documentation is ...
Dai's user avatar
  • 592
5 votes
1 answer
10k views

How to register CLR assembly as trusted in SSDT deployment

I have CLR assembly in SSDT and to deploy that it has to be trusted. What I understand there are 4 options how to do that First option, use TRUSTWORTHY EXEC sp_configure 'clr enabled', 1; ...
Muflix's user avatar
  • 1,109
6 votes
2 answers
5k 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 ...
Glorfindel's user avatar
  • 2,201
1 vote
1 answer
6k 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 ...
user1664043's user avatar
4 votes
1 answer
1k 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 ...
Kris's user avatar
  • 432
1 vote
0 answers
123 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 ...
Crono's user avatar
  • 111
0 votes
1 answer
60 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 ...
Jack.Frost's user avatar
3 votes
2 answers
596 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 ...
atevm's user avatar
  • 337
6 votes
1 answer
778 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 ...
Oscar's user avatar
  • 249
2 votes
1 answer
258 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 ...
Roeland's user avatar
  • 133
8 votes
1 answer
2k 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 ...
KevinP's user avatar
  • 105
0 votes
1 answer
238 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 ...
blizz's user avatar
  • 157
1 vote
1 answer
389 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 ...
DraftinBandit's user avatar
5 votes
1 answer
867 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 ...
Doug Coats's user avatar
6 votes
2 answers
6k 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 (...
Stockburn's user avatar
  • 419
2 votes
2 answers
385 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 ...
Pxtl's user avatar
  • 151
5 votes
2 answers
2k 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 ...
JamesP's user avatar
  • 127
8 votes
3 answers
14k 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 ...
WillG's user avatar
  • 327
6 votes
1 answer
867 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 ...
Joe Obbish's user avatar
  • 32.5k
9 votes
1 answer
613 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 ...
Russ Suter's user avatar
2 votes
1 answer
590 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 ...
CodingWookiee's user avatar
6 votes
1 answer
4k 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 ...
user1443986's user avatar
3 votes
2 answers
2k 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....
SpaceGhost440's user avatar
2 votes
2 answers
363 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 ...
Pரதீப்'s user avatar
31 votes
4 answers
3k 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 ...
Joe Obbish's user avatar
  • 32.5k
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, ...
McNets's user avatar
  • 23.8k
4 votes
1 answer
895 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), @...
Camadas's user avatar
  • 145
4 votes
2 answers
3k 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....
Murali Dhar Darshan's user avatar
3 votes
1 answer
944 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 ...
nnhesh's user avatar
  • 33
6 votes
1 answer
248 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 ...
JW-OP's user avatar
  • 141
0 votes
2 answers
8k 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 ...
user160248's user avatar
7 votes
4 answers
2k 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 ...
Brandon Huber's user avatar
0 votes
0 answers
38 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 ...
Dbot's user avatar
  • 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: "...
Paul White's user avatar
  • 86.3k
1 vote
0 answers
251 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 ...
Julian Goldsmith's user avatar
1 vote
1 answer
4k 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'...
Tom Pažourek's user avatar
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. (...
andyabel's user avatar
  • 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 ...
user1193463's user avatar
6 votes
1 answer
331 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 ...
K_foxer9's user avatar
  • 163
1 vote
1 answer
184 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, ...
MrTCS's user avatar
  • 709
4 votes
1 answer
700 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 ...
Evan Carroll's user avatar
  • 63.6k
3 votes
1 answer
4k 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 ...
d-_-b's user avatar
  • 1,154
3 votes
1 answer
1k 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 (...
mpag's user avatar
  • 968
3 votes
1 answer
482 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 ...
Reza Paidar's user avatar
6 votes
2 answers
939 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 ...
Martin Smith's user avatar
  • 84.9k
3 votes
1 answer
8k 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 ...
Solomon Rutzky's user avatar
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 ...
Jeff Sacksteder's user avatar