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
7
votes
3answers
547 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
79 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
213 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
83 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 ...
3
votes
1answer
309 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
276 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....
1
vote
2answers
109 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 ...
29
votes
4answers
859 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
279 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
103 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
325 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
95 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
85 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
207 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 ...
5
votes
2answers
375 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
26 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
652 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
88 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
929 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'...
6
votes
2answers
764 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
504 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
203 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
103 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
357 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
2k 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
606 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
290 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
383 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
1k 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 ...
1
vote
0answers
2k 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
199 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 ...
1
vote
1answer
409 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
93 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
553 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
6k 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
520 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
452 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-...
5
votes
1answer
332 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?
7
votes
2answers
658 views

Does SQL Server support custom DOMAINs?

PostgreSQL supports the DOMAIN specification, from SQL 2011 working draft spec, A domain is a named user-defined object that can be specified as an alternative to a data type in certain places ...
6
votes
1answer
393 views

SQL Server CLR procedures and SQLCLR_QUANTUM_PUNISHMENT

I have a CLR stored procedure running on SQL Server 2014. This stored procedure gathers some data (generally numbering in the tens-of-thousands of rows range), runs some computations on the data, ...
0
votes
1answer
314 views

User Defined Aggregate Functions without DLL [closed]

Is it possible to create a aggregate function without dll? Every aggregate function have to reference a dll? Thank you.
1
vote
1answer
2k views

Service Broker activation procedure in C# with external dependencies

I am not entirely sure if I have come to the right place, because this question involves both SQL Server and .Net. Apologies if this is not an appropriate place for my question! I am currently ...
2
votes
1answer
162 views

Audit use of CLR Table-valued function

I have written some CLR table-valued functions that invoke a web service and I would like to audit their use. Does anyone know of any way this can be achieved in the CLR code itself? I would be nice ...
0
votes
2answers
747 views

How to resolve Geospatial Data over linked server causing error?

Update of the issue. The exact error is Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131022. You may fix the problem and try again later. The error is happening ...
2
votes
2answers
69 views

Ways to invoke methods on CLR datatypes

I have difficulty understanding when to use .method() and when to use ::method() in SQL Server. For example For HierarchyID data type I have seen HierarchyID::GetRoot(), but also @sample_node....
5
votes
1answer
4k views

SQL, SQLCLR objects & effective memory utilization

We've recently converted our ERP system from IBM Universe to SQL Server. Application performance is generally tolerable but occasionally degrades to terrible. We're running the database on Win ...
4
votes
1answer
2k views

CLR SQL vs T-SQL for queries

I am aware that CLR stored-procedures is more efficient in procedural code and T-SQL is better for queries - however I am having a hard time deciding what to use for my specific needs, and I would ...
4
votes
2answers
7k views

.NET SQLCLR Assembly not working in SQL Server 2016 (Error msg 10314)

I am migrating a database application from Windows 2008 R2/SQL Server 2008 R2 to Windows 2012 R2/SQL Server 2016 that uses a third party .NET CLR assembly to parse strings. The error I get is: An ...
9
votes
2answers
282 views

Is the hierarchyid CLR open-source?

The built-in hierarchyid is a CLR that stores paths in an efficient binary form, and provides other useful functionality. Unfortunately, there is limit to how deep the represented paths can be, and ...
8
votes
1answer
19k views

What is the SQLCLR wait type in Activity Monitor within SQL Server Management Studio (SSMS)?

When I check our SQL Prod server, every time I open Activity Monitor I always see SQLCLR on the first list on the resource wait section in the Activity Monitor. It always has a high number. What is ...