Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

4
votes
1answer
67 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
62 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
50 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
57 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
93 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
128 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
25 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 ...
6
votes
1answer
274 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
55 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
461 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'...
5
votes
2answers
491 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. (...
0
votes
0answers
46 views

SQL Server Blocking issues

We are facing an issue in our top-up system which is SQL Server blocking resources. The scenario is every API user (third party system) has a single account in our database, when many requests came ...
1
vote
1answer
360 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
172 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
84 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
304 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
1k 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
496 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
241 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
282 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
748 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
1k 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
153 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
318 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
82 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 ...
4
votes
1answer
436 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/";...
5
votes
1answer
4k 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
359 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
321 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
237 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
511 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 ...
4
votes
1answer
294 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 or rows range), runs some computations on the data, ...
0
votes
1answer
224 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
146 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
622 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
67 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
3k 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
5k 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
255 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 ...
7
votes
1answer
15k 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 ...
4
votes
2answers
605 views

Do SQLCLR objects support Distributed Transactions on regular / external connections?

The most common usage of regular / external database connections in SQLCLR objects, it would seem, is to do SELECT-only queries (i.e. no changes: DML, DDL, DCL, D*%$#!L, etc). It is also fairly common ...
3
votes
1answer
999 views

CLR Procedure with XML parameter returns Data at the root level is invalid. Line 1, position 1

I have following CLR stored procedure, which is throwing error on this command xmlDoc.LoadXml(inputXml); code public static int spGetTaxOfficeXML(SqlXml _inputXml) { // this ...
4
votes
1answer
2k views

SQL 2012 CLR error: “The context transaction which was active before entering …”?

I am experience strange error with SQL 2012 and CLR: The context transaction which was active before entering user defined routine, trigger or aggregate "My routine" has been ended inside of it,...
4
votes
3answers
1k views

Can I run a CLR Stored Procedure on a different server than the database instance?

I want to add a trigger whenever a row is inserted into a certain table. The trigger will fire off a CLR stored procedure which will do things other than database work, such as write a JSON string to ...
2
votes
1answer
313 views

SQLCLR assembly throws error when multiple queries run simultaneously

I have been using SimMetrics to implement the QGramsDistance search in SQL Server. The SimMetrics is used as a SQL CLR assembly. The search results are promising and everything was well, until of late,...
7
votes
1answer
1k views

How to better use CLR Function from performance point of view (repeat inside each DB or have general function)?

I asked a question about validation of XML using XSD schema inside SQL Server 2012 (see link). I understand (as I suspected) that I need to use CLR Function. The function will get XSD schema text and ...
5
votes
1answer
596 views

Validate XML using XSD inside TSQL function in SQL Server 2012?

I need to validate XML using XSD inside TSQL. To make this task semi automated I need to do this inside TSQL function. To make validation I have to set value to XML and if it is not valid I will ...
2
votes
2answers
572 views

Significance of has_unchecked_assembly_data due to a persisted computed column after changing compatibility level?

This database has a persisted computed column, which is computed by calling a .NET function. When changing compatibility from MSSQL 2005 to 2012, I was presented with error 10334, which looks more ...