SQL Server Performance

sp OACreate problem

Discussion in 'General Developer Questions' started by cpopescu2005, Dec 6, 2005.

  1. cpopescu2005 New Member

    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) ?

  2. Luis Martin Moderator




    Luis Martin

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell

    All postings are provided “AS IS” with no warranties for accuracy.

  3. ranjitjain New Member

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

    http://www.sqlservercentral.com/columnists/dasanka/callingcomfromtsql.asp describes how to access third party dll from T-SQL


Share This Page