Microsoft KB Archive/284443

= BUG: Destroying OLE Automation Object May Leave Orphaned SQL Processes =

Article ID: 284443

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q284443



BUG #: 101159 (SQLBUG_70), 351681 (SHILOH)



SYMPTOMS
Destroying an OLE Automation SQLServer object using the sp_OADestroy procedure may leave connections orphaned in SQL Server. This problem is seen under the following conditions:
 * The SQLServer.Jobserver.Jobs.Job object is used to manage SQL Server Agent jobs.
 * The SQLServer object is not explicitly disconnected by calling the Disconnect method

These processes were all inactive (sleeping) with a waittype of 0. They will remain listed in sysprocesses indefinitely, until they are terminated by the TSQL KILL command or SQL Server is restarted.



WORKAROUND
Use the object Disconnect method to explicitly disconnect the object before destroying it. This is good programming practice.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0 and 2000.



MORE INFORMATION
Here is an example of the problem (error checking is omitted for clarity): SET NOCOUNT ON DECLARE @oSQLServer int, -- SQL Server Object. @hr int, @Val nvarchar(250), -- String to concatenate current method or property. @jobStatus int -- Outcome of job.

-- Create local SQL Server object. EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @oSQLServer OUT

-- Set property to use NT Authentication. EXEC @hr = sp_OASetProperty @oSQLServer, 'LoginSecure', 'True'

-- Connect to local server. EXEC @hr = sp_OAMethod @oSQLServer, 'Connect'

-- Check job status of an existing job named 'TestJob_Bug'.

EXEC @hr = sp_OAGetProperty @oSQLServer, 'JobServer.Jobs(&quot;TestJob_Bug&quot;).CurrentRunStatus', @jobStatus out

-- If object is not disconnected, the processes will be orphaned. -- Uncomment the next line to disconnect the object. -- EXEC @hr = sp_OAMethod @oSQLServer, 'Disconnect'

-- Destroy object created. exec sp_OADestroy @oSQLServer

select * from sysprocesses where program_name like 'SQLDMO%' Each time that the above SQL commands are executed, processes listed in MASTER..SYSPROCESSES will increase by 1. Uncomment the line that calls the Disconnect method to explicitly disconnect the SQLServer object and its associated SQL process.

Keywords: kbbug kbpending KB284443

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.