issue with sp_OACreate

18 Aug 2006 6:34 AM

Bindu Pushparaj

Hi All,

When we run the sp_OACreate sp, we are receiving the following error:

OLE Automation Error Information
Source: ODSOLE Extended Procedure
Description: Class not registered

This happens only, when we try to run the sp_OACreate sp in a separate
memory space using excution context option 4.
We are not receiving error, while with run with execution context option 1
and 5.
Our requirement is to be able to run this with option 4, since there were
memory problems with using option1.

The Class is registered. And also the Microsoft Windows NT user account
("system") that SQL Server is running under has "Full Control" permission on
the registry keys for this object.

Here is the sample code:

DECLARE @Object int
DECLARE @Hresult int

--Create the object
EXEC @Hresult = master.dbo.sp_OACreate 'DLL.Class', @Object OUT , 4
select @Hresult

DECLARE @output varchar(255)
DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(255)
PRINT 'OLE Automation Error Information'

EXEC @hr = sp_OAGetErrorInfo @object, @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

Please advice. Am I missing something ?

Thanks and Regards,
Bindu.

18 Aug 2006 9:14 AM

Greg Linwood

Hi Bindu

I'm not exactly sure what's causing your problem, but another approach you
can use is simply to create a out of process COM .exe rather than a .dll,
which naturally uses it's own memory. Years ago, I used to do this on a
fairly regular basis using ATL, VB & COM+ approaches with success . Another
advantage of this approach is that you can configure the program to use a
different security context than SQL Server which was also useful

Regards,
Greg Linwood
SQL Server MVP

Show quote

"Bindu Pushparaj" <bin***@healthasyst.com> wrote in message
news:OSWs$ApwGHA.2432@TK2MSFTNGP06.phx.gbl...
> Hi All,
>
> When we run the sp_OACreate sp, we are receiving the following error:
>
> OLE Automation Error Information
> Source: ODSOLE Extended Procedure
> Description: Class not registered
>
> This happens only, when we try to run the sp_OACreate sp in a separate
> memory space using excution context option 4.
> We are not receiving error, while with run with execution context option 1
> and 5.
> Our requirement is to be able to run this with option 4, since there were
> memory problems with using option1.
>
> The Class is registered. And also the Microsoft Windows NT user account
> ("system") that SQL Server is running under has "Full Control" permission
> on the registry keys for this object.
>
>
> Here is the sample code:
>
> DECLARE @Object int
> DECLARE @Hresult int
>
>
> --Create the object
> EXEC @Hresult = master.dbo.sp_OACreate 'DLL.Class', @Object OUT , 4
> select @Hresult
>
> DECLARE @output varchar(255)
> DECLARE @hr int
> DECLARE @source varchar(255)
> DECLARE @description varchar(255)
> PRINT 'OLE Automation Error Information'
>
> EXEC @hr = sp_OAGetErrorInfo @object, @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
>
>
> Please advice. Am I missing something ?
>
> Thanks and Regards,
> Bindu.
>