Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.dm_clr_properties

 

Returns a row for each property related to SQL Server common language runtime (CLR) integration, including the version and state of the hosted CLR. The hosted CLR is initialized by running the CREATE ASSEMBLY, ALTER ASSEMBLY, or DROP ASSEMBLY statements, or by executing any CLR routine, type, or trigger. The sys.dm_clr_properties view does not specify whether execution of user CLR code has been enabled on the server. Execution of user CLR code is enabled by using the sp_configure stored procedure with the clr enabled option set to 1.

The sys.dm_clr_properties view contains the name and value columns. Each row in this view provides details about a property of the hosted CLR. Use this view to gather information about the hosted CLR, such as the CLR install directory, the CLR version, and the current state of the hosted CLR. This view can help you determine if the CLR integration code is not working because of problems with the CLR installation on the server computer.

Column name Data type Description

name

nvarchar(128)

The name of the property.

value

nvarchar(128)

Value of the property.

The directory property indicates the directory that the .NET Framework was installed to on the server. There could be multiple installations of .NET Framework on the server computer and the value of this property identifies which installation SQL Server is using.

The version property indicates the version of the .NET Framework and hosted CLR on the server.

The sys.dm_clr_properties dynamic managed view can return six different values for the state property, which reflects the state of the SQL Server hosted CLR. They are:

  • Mscoree is not loaded.
     
  • Mscoree is loaded.
     
  • Locked CLR version with mscoree.
     
  • CLR is initialized.
     
  • CLR initialization permanently failed.
     
  • CLR is stopped.
     

The Mscoree is not loaded and Mscoree is loaded states show the progression of the hosted CLR initialization on server startup, and are not likely to be seen.

The Locked CLR version with mscoree state may be seen where the hosted CLR is not being used and, thus, it has not yet been initialized. The hosted CLR is initialized the first time a DDL statement (such as CREATE ASSEMBLY (Transact-SQL)) or a managed database object is executed.

The CLR is initialized state indicates that the hosted CLR was successfully initialized. Note that this does not indicate whether execution of user CLR code was enabled. If the execution of user CLR code is first enabled and then disabled using the Transact-SQL sp_configure stored procedure, the state value will still be CLR is initialized.

The CLR initialization permanently failed state indicates that hosted CLR initialization failed. Memory pressure is a likely cause, or it could also be the result of a failure in the hosting handshake between SQL Server and the CLR. Error message 6512 or 6513 will be thrown in such a case.

The CLR is stopped state is only seen when SQL Server is in the process of shutting down.

This is a view in the mssqlsystemresource database.




Syntax
CREATE VIEW sys.dm_clr_properties AS
	SELECT *
	FROM OpenRowset(TABLE DM_CLR_PROPERTIES)

 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash