sql-dmo objects is not destroyed properly

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

sql-dmo objects is not destroyed properly

Author

Topic

imarchenko
Yak Posting Veteran

57 Posts

Posted - 2007-11-12 : 19:32:03

Hello!

I have noticed that on one of SQL Servers (SQL 2000 SP4) after I run following script SQLDMO connections are not destroyed:

declare @objDMO int
declare @objDatabase int
declare @resultCode int
declare @dbname varchar(200)
declare @tablename varchar(200)
declare @cmd varchar(300)
declare @temp varchar(8000)

Set @dbname = 'PUBS'
Set @tablename = 'Authors'

EXEC @resultcode = sp_OACreate 'SQLDMO.SQLServer', @objDMO OUT
if @resultcode = 0
print 'Created Object'

Exec @resultcode = sp_OASetProperty @objDMO, 'loginsecure', 'true'

EXEC @resultcode = sp_OAMethod @objDMO, 'Connect', NULL, '(local)'
if @resultcode = 0
print 'connected'

Set @cmd = 'databases("' + @dbname + '").tables("' + @tablename + '").script'
Exec @resultcode = sp_OAMethod @objDMO, @cmd , @temp OUTPUT, 4
print @temp

EXEC @resultcode = sp_OADestroy @objDMO
if @resultcode = 0
Print 'destroyed object'

I do see 'destroyed object' message. But if I run following query I can see connections piling up:

select * from master..sysprocesses where spid>50 and program_name like 'SQLDMO%'

As a result, at some point SQL server is accumulating hundreds of SQLDMo connections. This is only happening on one server. I ran tests on others and everything was fine.

Any help is greately appreciated,
Igor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources