How to list CLR objects inside a SQL Server database

Troubleshooting an error message An error occurred in the Microsoft .NET Framework while trying to load assembly id xxx and needed to identify CLRs related to a particular database. This query lists the CLR objects inside a SQL Server database. Use the information in this query to start diagnosing the error messages. use myDB go SELECT o.object_id AS [object_ID] , a.assembly_id AS [assemblyID] ,a.name AS [assemblyName] ,schema_name(o.schema_id) + '.' + o.[name] AS [CLRObjectName] ,o.type_desc AS [CLRType] ,o.create_date AS [DateCreated] ,o.modify_date AS [SateModified] ,a.permission_set_desc AS [CLRPermission] FROM sys.objects o INNER JOIN sys.module_assembly_usages ma ON o.object_id = ma.object_id INNER JOIN sys.assemblies a... Read more →

Troubleshooting SQL CLR memory pressure

Question: I can see error messages in the SQL Server Error Log such as “AppDomain xxxxxxxxxxxxx is marked for unload due to memory pressure”. How can I troubleshoot the memory pressure? Answer: This message can be challenging to identify the source. I don’t define this message as an error message. It’s more of an informational message that reports on server memory pressure. Because there was memory pressure , the server unloaded app domain to free server memory. The app domain is unloaded causing the associated memory to be freed. It is common for this information message to appear when CLR... Read more →

Missing CLR data types on client - Microsoft.SqlServer.Types - geography and geometry

Question: We have been having issues with an application. We have a potential solution that involves using the spatial data type in SQL 2008 R2 Will we have access to the geography and geometry CLR datatypes as is or do we need to load a Feature Pack ? Answer: The CLR data types geometry and geography are presented through the Microsoft.SqlServer.Types CLR assembly. To return a list of each CLR assembly use the sys.assemblies view. You should have a resultset which includes “Microsoft.SqlServer.Types : select * from sys.assemblies To view each user-defined data type presented use assembly_types view. The resultset... Read more →

How to list all CLR objects and associated procedures

A developer reported an error in the Microsoft .Net Framework when executing a CLR object. Use the query and CLR views to understand and troubleshoot CLR integration managed database objects that are executing in the SQL Server instance. An error occurred in the Microsoft .NET Framework while trying to load assembly id xxxxx System.IO.FileLoadException: Could not load file or assembly 'myapp.database.sqlserver, Version=2.1.40317.0, Culture=neutral, PublicKeyToken=xxxxxxxx' or one of its dependencies. An error relating to security occurred. How to troubleshoot .Net Framework errors and CLR objects These type of errors can be difficult to troubleshoot. One of the reasons they can be... Read more →