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
1
vote
0answers
23 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 ...
0
votes
1answer
49 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
2answers
98 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 ...
6
votes
1answer
292 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 ...
2
votes
1answer
74 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 ...
8
votes
1answer
795 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 ...
0
votes
1answer
112 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 ...
1
vote
1answer
44 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
1answer
291 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
2answers
2k 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 (...
2
votes
2answers
188 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 ...
5
votes
2answers
959 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 ...
8
votes
3answers
6k 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 ...
6
votes
1answer
432 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 ...
9
votes
1answer
405 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
1answer
164 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
1answer
2k 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
2answers
897 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
2answers
287 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
4answers
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 ...
3
votes
1answer
1k 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, ...
4
votes
1answer
436 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), @...
2
votes
2answers
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
1answer
445 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 ...
6
votes
1answer
154 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 ...
0
votes
2answers
2k 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
4answers
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
0answers
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 ...
7
votes
1answer
1k 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: "...
1
vote
0answers
168 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
1answer
2k 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
2answers
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. (...
1
vote
1answer
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
1answer
271 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 ...
1
vote
1answer
154 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, ...
4
votes
1answer
512 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 ...
3
votes
1answer
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 ...
3
votes
1answer
866 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 (...
3
votes
1answer
392 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
2answers
659 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 ...
2
votes
1answer
4k 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
0answers
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
1answer
267 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
1answer
637 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
1answer
115 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 ...
3
votes
1answer
983 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/";...
4
votes
1answer
12k 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 ...
3
votes
2answers
937 views

Can add one CLR function to SQL server but not a second in the same assembly: loading wrong DLL

With the following code, I used csc to compile the dll. I then added the assembly to sql server 2014 using CREATE ASSEMBLY ReplaceMultiWord from 'd:\bcp\ReplaceMultiWord.dll' WITH PERMISSION_SET = ...
2
votes
1answer
829 views

CLR and where to store config values

I am looking to replace some hard-coded values in CLR code (SQL 2012), I came across the use of config files here - https://stackoverflow.com/questions/16177769/assembly-configuration-clr-stored-...
6
votes
1answer
606 views

What types of methods can we call from a .NET assembly in SQL Server

After we have registered a DLL in SQL Server (in Programmability -> Assemblies), can we call any type of method from that .NET assembly in SQL Server? Or just static ones?