sql-dmo object is not destroyed properly

Question

sql-dmo object is not destroyed properly

Igor Marchenko

Hall of Fame

Points: 3281
More actions
November 12, 2007 at 5:25 pm

#103426

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 1

I can't prove it, but apparently you have... I've heard that the sp_OA* procs have a "connection leak"...

Real key here is what do you think you need an sp_AO* string of events for? What is it that you're actually trying to do? I ask because there's usually a good ol' fashioned T-SQL equivelent... maybe with a little xp_CmdShell thrown in...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Igor Marchenko Hall of Fame Points: 3281 More actions · Answer 2

I am trying to script out my server/database objects: jobs, operators, etc. I was just providing a sample script that aslo reproduces the issue.

Thanks,

Igor

Igor Marchenko Hall of Fame Points: 3281 More actions · Answer 3

I think that's what describes my problem:

http://support.microsoft.com/kb/282229/EN-US/

Igor Marchenko Hall of Fame Points: 3281 More actions · Answer 4

Although, Microsoft says problem should be fixed in SQL 2000 SP1. I am running SP4. I am puzzled.

Gordon-265412 SSCrazy Points: 2898 More actions · Answer 5

Make sure you disconnect from the server...

-- Disconnect from the server.

EXEC @hr = sp_OAMethod @object, 'DisConnect'

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc

RETURN

END

Your friendly High-Tech Janitor... 🙂