Cant create object using sp_OACreate

All, when trying to use the sp_oacreate by "EXEC @.hr = master.dbo.sp_OACreate 'FileSize.clsFileSize', @.object
OUT", I am having the following error being returned:
hr=0x80070008, source=ODSOLE Extended Procedure,
Description=Not enough storage is available to process
this command. This command works in a procedure most of
the time and when it starts occuring, the node is usually
failed over to resolve the problem. We are using Windows
2000 clustering, SQL 2000. TIA.Hello Lee,
Thank you for using MSDN Newsgroup!
From your description, I understand that when you use sp_oacreate to create
an instance of the OLE object, sometimes you will receive the error
message, but when this random issue is failed over, it can be resolved.
Have I fully understood you? If there is anything I misunderstood, please
feel free to let me know.
However, I need to mention that such an intermittent issue is complex to
troubleshoot in newsgroups. It's hard (even impossible) to reproduce it and
address its causes. So if the issue is urgent on your side, it is
recommended that you contact Microsoft Product Support Service (PSS) via:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS
Based on my experience, this issue may be caused by some unexpected
resources leak, especially a memory leak or something related to some
components on your side.
=================First of all, please read the following article to see if it makes sense:
198891 INF: Enabling DLL-Based COM Object Execution Outside SQL Server
http://support.microsoft.com/?id=198891
282229 FIX: SP_OA Procedures Leak Memory and Cause Various Errors
http://support.microsoft.com/?id=282229
=================Secondly, not having enough storage/memory available will also cause the
sp_OACreate failing with that error. In this case, we can try to increase
the memory space to make more memory available to non-SQL Server memory
consumers.
"-g" is an optional SQL Server startup parameter that can be used to
increase the size of the MemToLeave area. The default -g memory size is
128MB in SQL Server 7.0 and 256MB in SQL Server 2000. You can increase the
size of the MemToLeave area by an additional 128MB by adding -g256 (SQL
7.0) or -g384 (SQL 2000) as a server startup parameter. This setting will
take effect the next time the SQL Server service is started. Startup
parameters are added in the "General" tab of the Server Properties dialog
in Enterprise Manager.
=================Furthermore, I suggest you use VBScript to check if you can successfully
create the object. When the error occurs, do not fail it over and use the
VBScript to create the object. If it can be created, the problem may be
located in the server side. Otherwise, it may relate to some component
(such as the 'FileSize.clsFileSize').
I'm unsure of the root cause of this random issue and hope the provided
suggestion and information will be useful for your problem troubleshooting
or resolution production. If you have any concerns on this issue, please
feel free to let me know.
=================Last but not the least, for additional information about the way the Sp_OA
stored procedure is implemented, please reference the following article in
case you can work out the possible workaround:
180780 INF: How Sp_OA Procedures Extension to SQL Server Is Implemented
http://support.microsoft.com/?id=180780
Best regards,
Billy Yao
Microsoft Online Support|||Thank you for your comments. We are still having these
issues. I have created the VB to run the dll's method
outside of SQL during an incident. It proved that we could
in fact still call the dll, just not inside SQL using
sp_oacreate. Also other sp_oacreates did work fine, like
those calling the 'filesystemobject'.
Could it be that this particular dll is being held in SQL
memory and becomes corrupt? Then it does not work until it
is forced out of memory and reloaded, like on startup? We
are still searching for answers here.
-John
>--Original Message--
>Hello Lee,
>Thank you for using MSDN Newsgroup!
>From your description, I understand that when you use
sp_oacreate to create
>an instance of the OLE object, sometimes you will receive
the error
>message, but when this random issue is failed over, it
can be resolved.
>Have I fully understood you? If there is anything I
misunderstood, please
>feel free to let me know.
>However, I need to mention that such an intermittent
issue is complex to
>troubleshoot in newsgroups. It's hard (even impossible)
to reproduce it and
>address its causes. So if the issue is urgent on your
side, it is
>recommended that you contact Microsoft Product Support
Service (PSS) via:
>http://support.microsoft.com/default.aspx?scid=fh;EN-
US;CNTACTMS
>
>Based on my experience, this issue may be caused by some
unexpected
>resources leak, especially a memory leak or something
related to some
>components on your side.
>=================>First of all, please read the following article to see if
it makes sense:
>198891 INF: Enabling DLL-Based COM Object Execution
Outside SQL Server
>http://support.microsoft.com/?id=198891
>282229 FIX: SP_OA Procedures Leak Memory and Cause
Various Errors
>http://support.microsoft.com/?id=282229
>=================>Secondly, not having enough storage/memory available will
also cause the
>sp_OACreate failing with that error. In this case, we can
try to increase
>the memory space to make more memory available to non-SQL
Server memory
>consumers.
>"-g" is an optional SQL Server startup parameter that can
be used to
>increase the size of the MemToLeave area. The default -g
memory size is
>128MB in SQL Server 7.0 and 256MB in SQL Server 2000. You
can increase the
>size of the MemToLeave area by an additional 128MB by
adding -g256 (SQL
>7.0) or -g384 (SQL 2000) as a server startup parameter.
This setting will
>take effect the next time the SQL Server service is
started. Startup
>parameters are added in the "General" tab of the Server
Properties dialog
>in Enterprise Manager.
>=================>Furthermore, I suggest you use VBScript to check if you
can successfully
>create the object. When the error occurs, do not fail it
over and use the
>VBScript to create the object. If it can be created, the
problem may be
>located in the server side. Otherwise, it may relate to
some component
>(such as the 'FileSize.clsFileSize').
>I'm unsure of the root cause of this random issue and
hope the provided
>suggestion and information will be useful for your
problem troubleshooting
>or resolution production. If you have any concerns on
this issue, please
>feel free to let me know.
>=================>Last but not the least, for additional information about
the way the Sp_OA
>stored procedure is implemented, please reference the
following article in
>case you can work out the possible workaround:
>180780 INF: How Sp_OA Procedures Extension to SQL Server
Is Implemented
>http://support.microsoft.com/?id=180780
>
>Best regards,
>Billy Yao
>Microsoft Online Support
>.
>|||John,
Thank you for your update!
I have little idea on this radom issue. Does it always fail to call the
dll/component in SQL using sp_oacreate, and does it always succeed to call
in VB. It seems a memory leak occurs when you perform a sp_oacreate, and it
may also related to that 'FileSize.clsFileSize' I mentioned before.
Since you can use the VB to call the dll, I recommend you use that VB to
run the dll's mothod outside of SQL and then call the VB executable file in
SQL, which is an appropriate workaround you can try.
Let us see if this help solves your problem. If there is anything more I
can do to assist you, please feel free to post it in the group.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.