BUG: Destroying OLE Automation Object May Leave Orphaned SQL Processes (284443)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 7.0
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("TestJob_Bug").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.
Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbbug kbpending KB284443
©2003 Microsoft Corporation. All rights reserved.