PRB: SQL Server COM Object Persistence Model

ID: Q194661

The information in this article applies to:

SYMPTOMS

Microsoft SQL Server version 6.5 is a multithreaded application that supports thread pooling through Open Data Services (ODS) and instantiation of any COM object, including SQLMail. A situation can occur where the main single-threaded apartment (STA) is terminated, while other COM apartments exist. When running the SQL Server OLE Automation stored procedures (sp_OA), after the main STA is terminated the following errors may be returned:

0x80040154 ODSOLE Extended Procedure
Class not registered (null) 0

0x80010012 ODSOLE Extended Procedure
The callee (server [not server application])
is not available and disappeared; all connections
are invalid. The call did not execute. (null) 0

0x80010012 ODSOLE Extended Procedure
The callee (server [not server application])
is not available


WORKAROUND

Because SQL Server is a multithread application that also supports thread pooling, developers must take care when using COM components within SQL Server, such as OLE Automation stored procedures, SQLMail and extended stored procedure that use COM objects. The first thread that calls CoInitialize(Ex) will exist the lifetime of the SQL Server process.


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:

Q190987 : INF: Extended Stored Procedures: What Everyone Should Know

Q174817 : INF: Microsoft SQL Server DB-Library Has Limited Extensibility


REFERENCES

Additional reading about the COM apartment thread models is available in the following sources:

"Inside Distributed COM" (ISBN 1-57231-849-x), Chapter Four: Threading Models and Apartments

"Essential COM" (ISBN 0-201-63446-5), Chapter Five: Apartments

"Inside COM" (ISBN 1-57231-349-8), Chapter Twelve: Multiple Threads

Information is also available on the Microsoft COM Web site at http://www.microsoft.com/com/.

Additional query words: prodsql ActiveX DCOM 


Keywords          : 
Version           : WINNT:6.5
Platform          : winnt 
Issue type        : kbprb

Last Reviewed: April 14, 1999