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.

learn more… | top users | synonyms (1)

0
votes
2answers
61 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 ...
3
votes
2answers
184 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 ...
2
votes
2answers
35 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....
3
votes
1answer
118 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 ...
2
votes
1answer
802 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 ...
2
votes
1answer
248 views

When creating asymetric key from assembly does assembly need to stay on the file system

I am deploying a SQL Server CLR project to my database and am usure of something. To give the function in the project external access I am creating an asymetric key from the projects assembly, ...
8
votes
2answers
134 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 ...
5
votes
1answer
267 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
184 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 ...
4
votes
1answer
208 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 ...
5
votes
2answers
512 views

Can CLR execution continue after 'THROW' is encountered in T-SQL?

I am calling a T-SQL stored procedure from a CLR stored procedure. and the THROW statement in T-SQL stops the execution of CLR without going into the catch block. Is there something I can change in my ...
3
votes
1answer
139 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 ...
10
votes
3answers
9k views

3rd party dll in SQL Server CLR

I need to use a third party DLL in a trigger c# code in SQL Server CLR But when I try to add reference it just shows some DLLs from SQL Server. How can I add my third party dll to SQL Server?
3
votes
3answers
410 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 ...
6
votes
1answer
264 views

dm_exec_describe_first_result_set_for_object for multiple result sets

Does a procedure exist to return metadata for all result sets in a stored procedure? Something like sys.dm_exec_describe_first_result_set_for_object, but for all result sets? Eventually, I will want ...
2
votes
1answer
67 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,...
5
votes
1answer
241 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 ...
16
votes
3answers
825 views

Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?

I would like to use code that I developed in C# CLR to be used in all of the databases on the system so that I don't have to set each to trustworthy and turn CLR on and keep a bunch of the same code ...
1
vote
1answer
71 views

Is it possible to run an .exe file from a CLR function?

Is it possible to run an .exe from CLR function with Sybase SQL Anywhere 12? If yes then I thought about link this function and .exe with pipes, is it a good idea or do you have better ideas? If not ...
4
votes
1answer
176 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 ...
1
vote
1answer
146 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 ...
4
votes
1answer
911 views

triggers - using the inserted / deleted tables in Dynamic SQL

Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables. Declare @NewID varchar(50) = Replace(...
8
votes
3answers
8k views

Assembly 'system.web, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog

I am trying to deploy a SQL CLR function using the HTTPUtility.UrlDecode method of System.Web but I can't get it to deploy. Error received: .Net SqlClient Data Provider: Msg 6503, Level 16, State ...
0
votes
0answers
20 views

How to deploy XSD from SQLCLR?

I have few SQLCLR functions implementing business logic that return SqlXml data. I would also like to make the Xml Schema Definitions in the dll available in Sql Server. It's not a '...
0
votes
0answers
38 views

Is it better to use C# for complex business logic that uses SQL and then run it from SQL Server or do everything from within SQL Server? [duplicate]

I find a C-based language like C# to be better in terms of programming constructs for handling complex code. So has anyone here used SQL simply to extract data and managed their business logic in C++ ...
2
votes
2answers
2k views

AppDomain 2 (mssqlsystemresource.dbo[runtime].1) is marked for unload due to memory pressure message in SQL Server Log

From last two days I can see following in SQL Server errorlog. AppDomain 2 (mssqlsystemresource.dbo[runtime].1) unloaded. & AppDomain 2 (mssqlsystemresource.dbo[runtime].1) is marked for unload ...
1
vote
0answers
101 views

How to use bcp utility in a CLR procedure as a process

I made an CLR Stored Procedure and I want to call bcp utility as a process in it. My project looks like : [SqlProcedure] public static int CmdShellBridgeBCP(string arguments) { return ...
12
votes
2answers
662 views

Crashing CLR on SQL Server 2014 (windows 2012R2)

I have this small CLR that does a RegEX function on a string in columns. When running on SQL Server 2014 (12.0.2000) on Windows Server 2012R2 the process crashes with Msg 0, Level 11, State 0, ...
1
vote
1answer
376 views

CLR Assemblies and SQL Server Availability Groups

I have a SQL 2012 Availability group. On that I also have a database with a .Net assembly that is using PERMISSION_SET = UNSAFE. CREATE ASSEMBLY drawing FROM C:\Windows\Microsoft.NET\Framework\v4....
5
votes
1answer
347 views

Why does dm_os_memory_clerks have MEMORYCLERK_SQLCLR values on one NUMA node?

We are using a SQLCLR stored procedure for service broker activation, and I want to monitor the memory used by the CLR code. Looking at sys.dm_os_memory_clerks, I see that only NUMA node 1 has any ...
2
votes
1answer
829 views

How to capture PRINT statements from TSQL called by CLR stored procedure

I have a CLR stored procedure that (among other things) calls a TSQL stored procedure. The TSQL stored procedure runs some dynamic SQL, and it prints the SQL before it runs for debugging purposes. ...
2
votes
2answers
585 views

Export a table and zip with password

I would like, from TSQL, to use a view to extract data from a table, output it with BCP, then use non 3rd party tools to ZIP it with a password and AES encryption. Optionnally, I could use a 3rd party ...
2
votes
2answers
93 views

CLR/C# equivalent to XACT_STATE()

I am porting a set of T-SQL stored procedures to C#/CLR. In the T-SQL, we use XACT_STATE() to determine if an error requires rolling back to a savepoint or rolling back the transaction entirely. I ...
0
votes
2answers
474 views

Grant execute on xp_logevent to sa using sa user to do it

Running a SQL Server 2012 CLR stored procedure with this code: const string executeLog = "EXEC master..xp_logevent @errorNumber, @message, informational"; using (SqlCommand cmd = new SqlCommand(...
0
votes
1answer
291 views

Log debug messages in a SQL CLR C# Stored Procedure

I have developed a SQL CLR stored procedure in C#, for SQL Server 2012. I want to log messages to SQL Server registers. On a normal stored procedure I use xp_logevent What can I use to log a message ...
2
votes
2answers
659 views

SQL server job deployment

I have a SQL Server job which has got different steps, including running a SSIS package. Currently it is on my PC (my local instance). I should deploy it to UAT and assume some steps need to be ...
3
votes
2answers
6k views

App domain is marked for unload due to memory pressure

In my sql server log (2005 SP4) I have this message every couple of seconds : "AppDomain XXX [DataBase.dbo[runtime], YYY] is marked for unload due to memory pressure" Our server : Windows Server ...
1
vote
1answer
264 views

SQL Server to XML files alternatives

I am currently looking for an alternative to writing a large, complex XML file based on a known specification. Currently the file is written in t-sql as a stored procedure. The output is in a results ...
2
votes
2answers
3k views

Storing IP addresses - varchar(45) vs varbinary(16)

I am going to create a table with two fields - ID as BIGINT and IPAddress as either varchar(45) or varbinary(16). The idea is to store all unique IP addresses and use a reference ID instead the actual ...
1
vote
1answer
131 views

SQL CLR very slow on Amazon EC2 Instance (SQL Server 2014 CU6)

I am running a SQL Server 2014 Standard Edition (Cumulative Updates 6) instance on an r3.large Amazon EC2 box. I noticed that the CPU usage of SQL Server skyrockets to 100% while executing pretty much ...
0
votes
1answer
325 views

SQL CLR in MySQL? [closed]

Can we call SQL CLR in MySQL Server using User Define Function? I want to create trigger in mysql. It will call user define function and this UDF will update the table in SQL Server.
1
vote
0answers
30 views

CLR UDT SCH-M blocking

I’m hoping someone can provide some guidance on a problem I’m having with schema locking on C# CLR UDT’s. I’ve been on the Internet for an extended period of time researching my problem without ...
9
votes
2answers
2k views

Other than restarting SQL Server, is there any way to force the SQLCLR AppDomain to be reset?

I want to force the AppDomain being used by SQLCLR to be reset. How can I do that besides restarting the SQL Server instance?
1
vote
0answers
2k views

“Failure occured while attempting to connect to the Principle server” from CLR procedure

I have a Windows Service running under a domain account which calls a stored procedure which then calls a CLR stored procedure. Initially I had a problem where database ownership chaining wasn't ...
2
votes
1answer
459 views

In a SSDT project how to avoid redeploying the assembly?

I have a SSDT project that has a few sql clr elements. They rarely get altered but every time I redeploy my db project it decides it must drop the assembly and recreate it. I think its somewhat ...
1
vote
0answers
155 views

Update SQL Server .net version

I have used this article to detect the latest install .net version and here is the result: Its .net 4.0 and I want to know if the SQL Server is using the latest installed version? I want to ...
4
votes
1answer
900 views

GZipStream/DeflateStream compressions alternatives

I have created a simple CLR functions for compressing/decompressing NVARCHAR columns: [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)] public static SqlBinary Compress( string ...
3
votes
1answer
253 views

Mathematical method for spotting irregularity in numbers flow

I am trying to build an engine which is going to predict orders for items inside shop. Three main variables are: Time-dates (x-axe etc 1.1.2013,1.2.2013 etc I do calculation one per day) Available ...
5
votes
2answers
2k views

Security or performance risks using SQL CLR [closed]

Are there any particular security or performance risks in using the CLR in SQL Server ?
3
votes
1answer
7k views

resource wait SQLCLR - wait_type CLR_SEMAPHORE by SPID 0

UPDATE In the comments under Aaron's post is an extensive explanation why I want to find this out, please read it if you want to know. The system is running some ETL, reporting services and a ...