oa_spcreate returns Class Not Registered error on a .net Assembly

Question

For the reason beyond my control I am forced to use oa_spcreate functionality from within SQL Server 2008 R2 64bit. The old VB6 DLLs (again out of my control) are not supported by 64bit version (32bit consideration is on the way).

I have tried to create a .NET wrapper for some of the functions on the old DLLs. Followed bits and pieces a knocked it together.

Code sample:

namespace Some32bitWrapper
{
    [ClassInterface(ClassInterfaceType.None)]
    public class Dissues32iClass : iDissues32iClass
    {
        public Dissues32iClass()
        {
        }

        //Instantiate iclass and execute InsertLocDetailLinesOnServer
        public Object InsertLocDetailLinesOnServer(String ConnectString, String DetailsString, int User)
        {
            try
            {
                DIssues32.Iclass _iclass = new Iclass();
                Object _return = _iclass.InsertLocDetailLinesOnServer(ConnectString, DetailsString, User);

                return _return;
            }
            catch (Exception er)
            {
                return er.Message;
            }
        }
    }

    public interface iDissues32iClass
    {
        Object InsertLocDetailLinesOnServer(String ConnectString, String DetailsString, int User); 
    }
}

Complied, registered with tlb regasm and tlb, gacutil-ed it. All seems honky-dory. As soon as I try run it from within SQL stored procedure using sp_oacreate get "Class not registered" error.

Stored Proc code:

ALTER PROCEDURE [dbo].[DIME_InsertLocDetailLinesOnServer]
@tConnectionString varchar(255),
@SysUser int,
@tDetailsString text
AS
declare @hr int
declare @Object int
declare @Return int
declare @Output varchar(255)

--HKEY_CLASSES_ROOT\TypeLib\{492BF323-0691-4F62-9171-3F75F4DF2753}
exec @hr= sp_OACreate 'Some32bitWrapper.Dissues32iClass',@object out,5

IF @hr <> 0
BEGIN
    EXEC sp_Getoaerrorinfo @object, @hr,@output out
    select @output,@hr
    RETURN -2
END
else
 return 'registered'
 begin
    exec @hr = sp_OAMethod @object,'InsertLocDetailLinesOnServer',NULL, @ConnectString = @tConnectionString,
               @DetailsString = @tDetailsString,@User = @Sysuser

    IF @hr <> 0
    BEGIN
        EXEC sp_Getoaerrorinfo @object, @hr,@output out
            select @output
        RETURN -3
    END

    exec @hr= sp_OADestroy @object out

    print @return
 end

Not a lot of information on it, except "don't use don't use types". At this time do not have a choice. Anybody can help?

P.S.: I ran all of the required reconfigure options on SQL Server and permission grants.

score 0 · Accepted Answer · edited May 23 '17 at 12:19

0

You can create 32-bit objects from SQL server using sp_OACreate. I know this because I did it for quite a while.

Essentially you have to extend the registration of the object to allow the component to be created in a surrogate process. This should not affect existing clients of the component.

See here for the exact method I used:

COM surrogate for third party component

edited May 23 '17 at 12:19

Community

1
1

answered Jan 07 '14 at 17:01

Ben

34,935
6
74
113

Ben, thank you for the answer, I've read about it before and tried it out. I get the registry entries in, but to no effect. There is an AppId key with Guid and dllsurrogate and it created an AppId key under the CLSID\{GUID} to reference the app id. – KonB Jan 07 '14 at 17:50
@user2687092 This is to create the old VB6 object yes? Registering .Net objects is a different kettle of fish. – Ben Jan 07 '14 at 18:09
No, I have VB6 objects already created, I am trying to put a .NET wrapper around them, since everywhere I read i see that 32bit DLLs are not supported by oa_sp function calls in SQL2008 R2 64 bit. Well and I've seen class not registered messages on VB6 dlls, in places where they always ran on 32bit SQL Server. – KonB Jan 07 '14 at 18:37
@user2687092, you don't need a .Net wrapper. If you register a surrogate for the 32bit component you will be able to create it directly from SQL 64 bit. I have done it. In fact a .Net DLL wrapper won't help since it will be loaded into the 64-bit process itself, so won't be able to create the object either. – Ben Jan 07 '14 at 22:51
That is my understanding, although a .NET DLL wrapper can be compiled in x86 platform. I probably need different instruction for the surrogate, or have someone else look at it, I can't make it work. – KonB Jan 08 '14 at 15:01

oa_spcreate returns Class Not Registered error on a .net Assembly

1 Answers1