Using extended stored procedures or SP_OA stored procedures to load CLR in SQL Server is not supported (322884)

SUMMARY

Microsoft does not support the use of Microsoft Common Language Runtime (included with .NET Framework) for any COM Callable Wrapper or Managed Extensions for C++ in either Microsoft SQL Server 7.0 or Microsoft SQL Server 2000. This limitation for support pertains directly to the use of extended stored procedures and the use of OLE Automation for any loading of the libraries that you must load to run in the SQL Server memory space.

Later versions of SQL Server will host Common Language Runtime (CLR) and will support procedures, functions, triggers, types, and aggregates that are written in CLR langauges. In these later versions, you cannot load CLR by using extended stored proceduress or sp_OA stored procedures.

MORE INFORMATION

NOTE: Unless specifically noted otherwise, all limitations apply to both SQL Server 7.0 and SQL Server 2000.

The .NET Framework assembly System.Runtime.InteropServices provides a robust environment for invoking assemblies from unmanaged code, but there are several technical discordances between the internal implementations of CLR and SQL Server:

Threading

To increase performance, the CLR implements Thread Local Storage. For additional information about issues that are related to the use of thread local storage in extended stored procedures, click the following article numbers to view the articles in the Microsoft Knowledge Base:

163449 Use of Thread Local Storage in an extended stored procedure

190987 How to use extended stored procedures

Additionally, CLR only uses thread-based scheduling and does not support Fiber-mode scheduling; however, SQL Server can use Fiber-mode scheduling. This property is configured as "lightweight pooling" from the sp_configure stored procedure or from the server properties in SQL Server Enterprise Manager (on the Memory tab, click to select the Use Windows NT fibers check box.

Memory

The use of extended stored procedures and OLE Automation both run in the virtual memory address space of the memory of SQL Server. The default SQL Server memory is only a fraction of the memory that SQL Server can potentially use and CLR competes with any existing implementations for these memory resources. For additional information about SQL Server memory management, click the following article number to view the article in the Microsoft Knowledge Base:

316749 There may not be enough virtual memory with large number of databases

COM Interoperability

This section specifically addresses the use of OLE Automation in SQL Server and it applies to both in-process and out-of-process COM objects. Assembly meta data for function interfaces implements a strongly-typed mechanism for any invocations.

As part of this design, the COM Callable wrapper for an assembly must use an external mechanism of mapping a ClassID to a member of a managed class. Because of this explicit mapping, there is no ability from an unmanaged perspective to establish a root list of available interfaces.

The extended stored procedure sp_oaCreate uses the IUnknown::QueryInterface interface to determine the object's support for a particular interface. The interoperability between CLR and unmanaged code relies on the IDispatch interface for implementing interfaces. Because there is no equivalent to a QueryInterface method to a CLR-based assembly, you cannot create an instance of the object.