SQL Server Performance

sp_OACreate Issue

Discussion in 'General Developer Questions' started by duda_SQL, May 8, 2008.

  1. duda_SQL New Member

    Hola a tod@s.

    Espero que alguien me pueda ayudar. Llevo una semana detrás de este tema y no doy con ello, sencillamente desquiciante.
    Tengo la siguiente instrucción que funciona Ok:
    EXEC @Error_Calculos = sp_OACreate 'DLL.Clase', @variable OUT
    Pero al ponerle el parámetro de contexto de ejecución [para cargar fuera de SQLServer]:
    EXEC @Error_Calculos = sp_OACreate 'DLL.Clase', @variable OUT,4
    Me tira el siguiente error:
    OLE Automation Error Information Source: ODSOLE Extended Procedure
    Sin mensaje ni nada por el estilo. Esto es sobre un W2003 con SP1 y bajo SQLServer 2000 con SP4, revisión 2039. No sé por que ocurre, el caso es que necesito urgentemente cargar el objeto [en este caso una dll] fuera de proceso de SQLServer. La dll está bien registrada ya que se accede a ella cuando no se pone el parametro 4, ¿me equivoco?
    La necesidad viene dada porque con el parametro 5 [o por defecto vacio], el sistema se vuelve inestable y el sql se cuelga a los dos dias de estar en produccion, Este caso ha sido atendido por la gente de Msoft y su solucion fue esta, en lugar de llamar a a la dll a través de un xp, hacerlo con metodos sp_OA. Reconvertí la dll en servidor de automatización, seguí los articulos recomendados y en un servidor me funcionó, pero en otro [de iguales caracterísiticas, mismas versiones, misma bd, etc] no.
    No sé por que ocurre, me estoy volviendo loco con este tema. La cuenta de servicios de sql y demás usuarios tienen permisos de ejecución sobre los sp_OA, lo intento como admin y nada...
    Por favor, rogaría que alguien me ayude. Muchisimas gracias por adelantado.
    Un saludo para tod@s.
  2. MohammedU New Member

    Can some one translate this [:)]
  3. Adriaan New Member

    Check out sp_OAGetErrorInfo in BOL.
  4. satya Moderator

    Luis is at best to translated.
  5. duda_SQL New Member

    Hi. Before all, sorry about my english, it's really poor.

    I hope somebody can help me. I spent a week in this trouble but ain't get it.

    The next sentence works Ok:

    EXEC @Error_Calculos = sp_OACreate 'DLL.Clase', @variable OUT

    But this:

    EXEC @Error_Calculos = sp_OACreate 'DLL.Clase', @variable OUT,4

    Returns the next error without message:

    OLE Automation Error Information Source: ODSOLE Extended Procedure

    I'm working with W2k3 Server SP1 and SQLServer 2000 SP4, 2039 rev. I don't know what happens, the fact is i need to load urgently the object [in this case a dll] out of SQLServer's process.The object [dll] is well registered because without context param 4 works well. Am I wrong?

    With the parameter 5 [or default empty], the system becomes unstable and the sql is hung to two days. This case has been attended by the people of Msoft and their solution was this, instead of calling to the dll across a xp procedures, to do it with methods sp_OA. The dll was converted into a automation server, followed the recommended articles and in a server it works, but in other one [the sameoperating system, same SQLServer version, same database, etc] not.

    I do not know what more to try.

    The SQLService account is under Administrator's W2k3 Server group, and have permissions to exec this kind of stored procedures [sp_OA], i tried to exec like sa user, but nothing ... it's getting me sick and making me mad.

    Please, help me.

    Thanks in advance.
  6. Adriaan New Member

    Your English is really excellent,no need to worry.
    Did you try calling sp_OAGetErrorInfo to get detailed error info? Details in BOL.
  7. duda_SQL New Member

    Hi. In the call os sP_OACreate i wrote this:

    EXEC @hr = sp_OAGetErrorInfo @CalcEvapoTrans, @source OUT, @description OUT

    IF @hr = 0

    BEGIN

    SELECT @output = ' Source: ' + @source

    PRINT @output

    SELECT @output = ' Description: ' + @description

    PRINT @output

    END

    ELSE

    BEGIN

    PRINT ' sp_OAGetErrorInfo failed.'

    RETURN

    END

    This just only returns:

    OLE Automation Error Information Source: ODSOLE Extended Procedure

    No message, no error number... i sure you.

    I took a look BOL but no more info about this kind of error.

    Thanks again.
  8. Adriaan New Member

    Using the error handler from BOL, something like this should give you more details:
    DECLARE @object int, @hr int
    DECLARE @property varchar(255), @return varchar(255)
    DECLARE @src varchar(255), @desc varchar(255)

    -- Create an object.
    EXEC @hr = sp_OACreate 'DLL.Clase', @object OUT, 4
    IF @hr <> 0
    BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
    END
    Since the DLL.Clase class is not registered on my computer, I get this result:
    hr Source Description
    ---------- ------------------------------ ------------------------------
    0x800401F3 ODSOLE Extended Procedure Invalid class string
  9. duda_SQL New Member

    Hi Adriaan.

    Thanks for the code. I will try this. I'll post the expected error message here when i get it.

    Thanks again.

    Regards.
  10. Luis Martin Moderator

    Murphy low.
    2 month without a single Spanish post. 3 days out of the forum and bingo!!.
    Bienvenido al Forum Duda. (Welcome to the forum Duda!).
    And thank to all member for your help.[:)]
  11. duda_SQL New Member

    Hi.

    Thanks anyway Adriaan. The returned error:

    hr Source Description
    ---------- ------------------------------ ------------------------------
    0x800401F3 ODSOLE Extended Procedure NULL



    Any suggestions?



    Regards.
  12. Luis Martin Moderator

    I've moved to English Forum.

Share This Page