PRB: SQL Server COM Object Persistence Model (194661)

MORE INFORMATION

Microsoft SQL Server 6.5 supports creation of objects by adhering to the specification of the Component Object Model (COM). Microsoft SQL Server 6.5 uses a series of extended stored procedures to support the creation and invocation of components from the Transact-SQL (TSQL) language. The OLE Automation (sp_OA) extended stored procedures are designed to start a single-threaded apartment to support the COM object within SQL Server.

Apartments are the context in which a COM object is created and used. Each apartment model has a set of specifications that defines the use and access of the component object. To understand this problem, it is crucial that you understand that the SQL Server implementation is an STA.

Beginning in Microsoft Windows NT 3.51 and Windows 95 (pre-DCOM), support for STA was introduced. This means that a multithreaded process, like SQL Server, could have multiple STAs within that process. Single-threaded apartments are designed to have one thread create and that same thread use the COM object. Because of built-in synchronization and thread safe mechanisms provided by the COM libraries, doing cross-thread COM access requires that you follow a rigid protocol, called marshaling.

When an STA first calls CoInitialize() or CoInitializeEx(), it creates a message queue to handle object synchronization. The SQL Server OLE Automation stored procedures are designed to create this thread and perform the CoInitialize when they are first invoked.

COM objects that do not have a threading model defined in the registry are considered legacy components. The SQL 6.5 SQL-DMO component is an example of a legacy component. With legacy components, the object expects that the thread which created the object is the thread accessing it. COM handles this in process by always creating and controlling the objects on the main STA. The main STA designation is given to the first STA instantiated in a process. After the main STA is created, all other COM calls from both the MTA and any other STAs, are marshaled to the main STA to execute the COM object and any of its methods.

SQL Server's main thread is used to control the service and ODS components. It is responsible for incoming connections as well as thread pooling and scaling functions. This means the main STA will be created on a secondary worker thread, never on the main application thread. If the main STA thread is terminated all the in-process objects created on that main STA thread are destroyed. Remember, all objects on other worker threads are marshaled to the main thread.

The SQL Mail component uses MAPI calls to send mail. MAPI internally calls CoInitialize(Ex) and if it is the first thread to initialize COM, it becomes the main STA. Future calls of sp_OA methods are marshaled to the main STA and not the apartment created by the sp_OA routines. Thus, if you call xp_stopmail the main STA is unloaded and current objects created with sp_OA calls are rendered out of scope until a main STA is re-established. This can easily be reversed if you run an sp_OA routine and then use the SQLMail component.



For additional information and clarification about the threading model used by SQL Server, please see the following articles in the Microsoft Knowledge Base: