MS SQL Server - SQL2000 sp_OACreate 'SQLDMO.SQLServer' memory leak?

Author

SQL2000 sp_OACreate 'SQLDMO.SQLServer' memory leak?

Paul Cahill

2006-11-15, 5:13 am

SQL Server 2000 SP4 Build 2187

I recently put live a SqlPing job on all our servers. Recently our IT guys
had some network, bdc issues that caused SQL server connection problems.
I decided to put jobs in place that would let me know if servers could not
talk to each other.

The jobs rely on a proc 'sysServerUp' that uses SQLDMO to do a login. They
have been running OK for a week or so until yesterday when one of our
servers reported:

0x80045900 Microsoft SQL-DMO [SQL-DMO]Out of memory. SQLDMO80.hlp 700

Could there be a memory leak caused by the proc below?

Thanks
Paul

create procedure dbo.sysServerUp
@Server sysname , -- remote server
@Timeout int = 10, -- login timeout
@Up char(1) = NULL OUTPUT -- returns server status

as

set nocount on

declare @Hr int
declare @Sql int
declare @Cnt int
declare @Status int

select @cnt = 0, @status = 0

exec @Hr = sp_OACreate 'SQLDMO.SQLServer', @Sql OUTPUT
if @Hr<>0 exec sp_OAGetErrorInfo @Sql

exec @hr = sp_OASetProperty @Sql, 'LoginSecure' , 'True'
exec @hr = sp_OASetProperty @Sql, 'LoginTimeout', @Timeout
exec @hr = sp_OAMethod @Sql, 'Connect' , null, @Server
if @Hr<>0 exec sp_OAGetErrorInfo @Sql -- This can be used to get the error
output

exec @Hr = sp_OAGetProperty @Sql, 'Status', @Status OUTPUT
exec @Hr = sp_OAMethod @Sql, 'DisConnect', null
exec @Hr = sp_OADestroy @Sql

select @Up = case when @Status = 1 then 'Y' else 'N' end

RETURN 1

Dan Guzman

2006-11-15, 7:14 pm

> Could there be a memory leak caused by the proc below?

I don't see a leak caused by your code.

Is there some reason you don't execute an ActiveX script directly for this
purpose? Personally, I avoid the sp_OA* procs.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Paul Cahill" <anon@anon.com> wrote in message
news:eRuqGzJCHHA.3524@TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 SP4 Build 2187
>
> I recently put live a SqlPing job on all our servers. Recently our IT guys
> had some network, bdc issues that caused SQL server connection problems.
> I decided to put jobs in place that would let me know if servers could not
> talk to each other.
>
> The jobs rely on a proc 'sysServerUp' that uses SQLDMO to do a login. They
> have been running OK for a week or so until yesterday when one of our
> servers reported:
>
> 0x80045900 Microsoft SQL-DMO [SQL-DMO]Out of memory. SQLDMO80.hlp 700
>
> Could there be a memory leak caused by the proc below?
>
> Thanks
> Paul
>
> create procedure dbo.sysServerUp
> @Server sysname , -- remote server
> @Timeout int = 10, -- login timeout
> @Up char(1) = NULL OUTPUT -- returns server status
>
> as
>
> set nocount on
>
> declare @Hr int
> declare @Sql int
> declare @Cnt int
> declare @Status int
>
> select @cnt = 0, @status = 0
>
> exec @Hr = sp_OACreate 'SQLDMO.SQLServer', @Sql OUTPUT
> if @Hr<>0 exec sp_OAGetErrorInfo @Sql
>
> exec @hr = sp_OASetProperty @Sql, 'LoginSecure' , 'True'
> exec @hr = sp_OASetProperty @Sql, 'LoginTimeout', @Timeout
> exec @hr = sp_OAMethod @Sql, 'Connect' , null, @Server
> if @Hr<>0 exec sp_OAGetErrorInfo @Sql -- This can be used to get the error
> output
>
> exec @Hr = sp_OAGetProperty @Sql, 'Status', @Status OUTPUT
> exec @Hr = sp_OAMethod @Sql, 'DisConnect', null
> exec @Hr = sp_OADestroy @Sql
>
> select @Up = case when @Status = 1 then 'Y' else 'N' end
>
> RETURN 1
>
>
>
>
>

John Bell

2006-11-15, 7:14 pm

Hi Paul

You are on the same version for all the servers? Have you monitored the
memory usage to see if it is being eaten up?

John

"Paul Cahill" wrote:

> SQL Server 2000 SP4 Build 2187
>
> I recently put live a SqlPing job on all our servers. Recently our IT guys
> had some network, bdc issues that caused SQL server connection problems.
> I decided to put jobs in place that would let me know if servers could not
> talk to each other.
>
> The jobs rely on a proc 'sysServerUp' that uses SQLDMO to do a login. They
> have been running OK for a week or so until yesterday when one of our
> servers reported:
>
> 0x80045900 Microsoft SQL-DMO [SQL-DMO]Out of memory. SQLDMO80.hlp 700
>
> Could there be a memory leak caused by the proc below?
>
> Thanks
> Paul
>
> create procedure dbo.sysServerUp
> @Server sysname , -- remote server
> @Timeout int = 10, -- login timeout
> @Up char(1) = NULL OUTPUT -- returns server status
>
> as
>
> set nocount on
>
> declare @Hr int
> declare @Sql int
> declare @Cnt int
> declare @Status int
>
> select @cnt = 0, @status = 0
>
> exec @Hr = sp_OACreate 'SQLDMO.SQLServer', @Sql OUTPUT
> if @Hr<>0 exec sp_OAGetErrorInfo @Sql
>
> exec @hr = sp_OASetProperty @Sql, 'LoginSecure' , 'True'
> exec @hr = sp_OASetProperty @Sql, 'LoginTimeout', @Timeout
> exec @hr = sp_OAMethod @Sql, 'Connect' , null, @Server
> if @Hr<>0 exec sp_OAGetErrorInfo @Sql -- This can be used to get the error
> output
>
> exec @Hr = sp_OAGetProperty @Sql, 'Status', @Status OUTPUT
> exec @Hr = sp_OAMethod @Sql, 'DisConnect', null
> exec @Hr = sp_OADestroy @Sql
>
> select @Up = case when @Status = 1 then 'Y' else 'N' end
>
> RETURN 1
>
>
>
>
>
>

Paul Cahill

2006-11-15, 7:14 pm

Same build on all servers. I have disabled the jobs for now as I do not want
to have to reboot our main server.

Dan.
Could you get me started on how to do an ActiveX script to do this?
I set this up as a quick and dirty. I created a job on each server that
tests for a connection to all the other important servers by calling the
procedure.
I get emailed via xp_smtp_sendmail if there is a failure.
The advantage of doing a login rather than a ping or port telnet is that it
catches any authentication issues. We have a BDC at a remote site that keeps
playing up and IT do not know why.

I need to use SQL Server tools to do this however I suppose it could be a
push learn to knock up a VB or C# app.

Paul

"John Bell" < jbellnewsposts@hotma
il.com> wrote in message
news:7F19C0E4-55CA-4F51-9DE8- 1CEC71EEDAE2@microso
ft.com...[color=darkred]
> Hi Paul
>
> You are on the same version for all the servers? Have you monitored the
> memory usage to see if it is being eaten up?
>
> John
>
> "Paul Cahill" wrote:
>

Dan Guzman

2006-11-15, 7:14 pm

> Could you get me started on how to do an ActiveX script to do this?

Here's the ActiveX script version of your proc. You can create a separate
job step that runs on failure of this script to send your xp_smtp_sendmail
notification. It's wouldn't be hard to do the notification in this script
as well but you might as well use this as an opportunity to lean C# ;-)

Set oSqlServer = CreateObject("SQLDMO.SQLServer")
oSqlServer.LoginSecure = True
oSqlServer.LoginTimeout = 10
oSqlServer.Connect "MyServer"
status = oSqlServer.Status
oSqlServer.DisConnect
Set oSqlServer = Nothing

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Paul Cahill" <anon@anon.com> wrote in message
news:uksNCwLCHHA.2316@TK2MSFTNGP04.phx.gbl...
> Same build on all servers. I have disabled the jobs for now as I do not
> want to have to reboot our main server.
>
> Dan.
> Could you get me started on how to do an ActiveX script to do this?
> I set this up as a quick and dirty. I created a job on each server that
> tests for a connection to all the other important servers by calling the
> procedure.
> I get emailed via xp_smtp_sendmail if there is a failure.
> The advantage of doing a login rather than a ping or port telnet is that
> it catches any authentication issues. We have a BDC at a remote site that
> keeps playing up and IT do not know why.
>
> I need to use SQL Server tools to do this however I suppose it could be a
> push learn to knock up a VB or C# app.
>
> Paul
>
>
> "John Bell" < jbellnewsposts@hotma
il.com> wrote in message
> news:7F19C0E4-55CA-4F51-9DE8- 1CEC71EEDAE2@microso
ft.com...
>
>

John Bell

2006-11-15, 7:14 pm

Hi

I noticed you're procedure is very similar to
http://www.sqldbatips.com/displaycode.asp?ID=38

To change to VBScript this should help http://tinyurl.com/ylkp6v

John

"Paul Cahill" wrote:

> Same build on all servers. I have disabled the jobs for now as I do not want
> to have to reboot our main server.
>
> Dan.
> Could you get me started on how to do an ActiveX script to do this?
> I set this up as a quick and dirty. I created a job on each server that
> tests for a connection to all the other important servers by calling the
> procedure.
> I get emailed via xp_smtp_sendmail if there is a failure.
> The advantage of doing a login rather than a ping or port telnet is that it
> catches any authentication issues. We have a BDC at a remote site that keeps
> playing up and IT do not know why.
>
> I need to use SQL Server tools to do this however I suppose it could be a
> push learn to knock up a VB or C# app.
>
> Paul
>
>
> "John Bell" < jbellnewsposts@hotma
il.com> wrote in message
> news:7F19C0E4-55CA-4F51-9DE8- 1CEC71EEDAE2@microso
ft.com...
>
>
>

Paul Cahill

2006-11-15, 7:14 pm

Hi Dan and John.

Thank you very miuch for the infomation. It is much appreciated.

Paul

"John Bell" < jbellnewsposts@hotma
il.com> wrote in message
news:B7C17276-8B54-4CCC-94BE- 5FB5B46A43EA@microso
ft.com...[color=darkred]
> Hi
>
> I noticed you're procedure is very similar to
> http://www.sqldbatips.com/displaycode.asp?ID=38
>
> To change to VBScript this should help http://tinyurl.com/ylkp6v
>
> John
>
> "Paul Cahill" wrote:
>

Sponsored Links