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.
134
questions
0
votes
0
answers
245
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" (...
2
votes
2
answers
456
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 ...
- 275
0
votes
1
answer
304
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 ...
- 554
5
votes
1
answer
5k
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;
...
- 1,059
6
votes
2
answers
4k
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,181
1
vote
1
answer
4k
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 ...
- 307
4
votes
1
answer
953
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 ...
- 402
1
vote
0
answers
110
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
59
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
480
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 ...
- 337
6
votes
1
answer
698
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
170
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
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 ...
- 105
0
votes
1
answer
210
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 ...
- 157
1
vote
1
answer
265
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
678
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 ...
- 349
7
votes
2
answers
5k
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 (...
- 427
2
votes
2
answers
345
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 ...
- 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 ...
- 117
8
votes
3
answers
12k
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 ...
- 327
6
votes
1
answer
750
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 ...
- 31.8k
9
votes
1
answer
539
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 ...
- 91
2
votes
1
answer
489
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 ...
5
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 ...
- 53
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....
- 332
2
votes
2
answers
357
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 ...
- 1,359
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 ...
- 31.8k
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, ...
- 23.5k
4
votes
1
answer
745
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
3
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....
3
votes
1
answer
836
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
233
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 ...
- 141
0
votes
2
answers
6k
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
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 ...
0
votes
0
answers
35
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: "...
- 82.1k
1
vote
0
answers
237
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 ...
- 171
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'...
- 413
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 ...
- 117
6
votes
1
answer
317
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
180
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, ...
- 699
4
votes
1
answer
662
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 ...
- 61.4k
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 ...
- 1,134
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 (...
- 968
3
votes
1
answer
460
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 ...
- 145
6
votes
2
answers
860
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 ...
- 83.1k
3
votes
1
answer
7k
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 ...
- 68.1k
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 ...
- 1,307
3
votes
1
answer
324
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 ...
- 223