SQLCLR User-Defined Type (UDT) SCH-M blocking I’m hoping someone can provide some guidance on a problem I’m having with schema locking on C# SQLCLR UDT’s.  I’ve been on the Internet for an extended period of time researching my problem without success on a solution.

Also, this "might" be a linked server issue as we see linked server queries accessing the UDT seem to block queries (sch-m running on the remote server).

Back story:

Back in 2006, we began porting some of our DB2 processes down to SQL Server 2005 (we’ve upgraded to SQL 2008 since then, but still need to use our UDT for a lot of current processes). Under DB2, we had made extensive use of the DB2 TIMESTAMP data type -- CCYY-MM-DD.HH.MM.SS.mmmmmm -- which has precision down to the microsecond. Because SQL Server, at that time, did not have DATETIME2, we were forced to create our own C# SQLCLR UDT to emulate the DB2 TIMESTAMP. The UDT was created as VARBINARY with properties that allowed us to reference things like:

            Column.Timestamp        (CCYY-MM-DD.HH.MM.SS.mmmmm)
            Column.Date                     (CCYY-MM-DD)
            Column.Microsecond     (mmmmmm)
            Etc.


When multiple processes access the UDT with just the column name (without referencing a specific property), we don’t seems to have problems.  

However, when we have multiple processes accessing the UDT and reference one of the properties (.Timestamp, etc.), we encounter Sch-M locks and other processes also accessing that UDT are forced to wait.

I’ll admit that I have limited knowledge on all things CLR and UDT.

I simply do not understand why we’re getting Schema Modification locks while merely selecting the column and specifying one of the underlying properties.

I’d appreciate any suggestions you might have.

Thanks,
When you use SQL CLR objects across databases, SQL Server will automatically convert them if certain conditions are met (https://technet.microsoft.com/en-us/library/ms178069(v=sql.105).aspx).  

In my case, this automatic conversion was taking out the SCH-M locks. I fixed the problem by manually converting the contents of my SQL CLR type to binary, and passing it to the second db as varbinary(max).  I then deserialized back to my CLR type in the second db.