sp OACreate problem

Hi,
I am using the sp OACreate extended stored procedure to instantiate a COM object.

Since my script is within a sql job which is scheduled to run at every 5 minutes, the spOACreate is called very often.

The problem is that after about a week of running perfectly, the spOACreate procedure crashes and returns this error message : "ODSOLE: Dynamic library initialization failed."

It looks like restarting the SQL Server service fixes the problem, but only for another week, after which the same error occurs. I need to mention that on that machine NO user permission are being changed and the problem DOES NOT look like lack of permissions on spOACreate.

While the spOACreate was failing, I could successfully instantiate the COM object using my test.exe application, which makes me think that the problem is not related to the COM that I try to instantiate.

The COM I am trying to instantiate is using the FileSystemObject to read file names from a specified folder on the local machine, but I have also tried to instantiate a different COM using the spOACreate and it failed as well.

The code looks like this:


Declare @iMsg int
Declare @hr int

EXEC @hr = spOACreate 'MyProject.MyClass', @iMsg OUT
EXEC @hr = spOAMethod @iMsg, 'Execute', NULL
EXEC @hr = spOADestroy @iMsg



Does anyone know how to overcome this situation ? Is this a bug of SQL Server or of the ODSOLE70.dll (which is the implementation of the spOACreate extended stored procedure) ?

Thanks,
Cris

Which service pack are you using.
I even had the same issue but got it resolved after installing SP4