locked
Extended Stored Procedures and VB RRS feed

  • Question

  • I have two functions in a .DLL created in VB6 I want to use.
    I create two Extended Stored Procedures using:

    sp_addextendedproc 'MyFunctionA', 'MyFunctions.dll'
    sp_addextendedproc 'MyFunctionB', 'MyFunctions.dll'

    When I run:
    EXECUTE @ReturnValue = MyFunctionA @Paramate1

    I get:
    "Cannot find the function MyFunctionA in the library C:\Program Files\MyDLLs\MyFunctions.dll. Reason: 127(The specified procedure could not be found.)"

    What am I missing?

    Monday, November 7, 2005 7:44 PM

Answers

  • You cannot use DLLs created by VB as extended stored procedures. There is no way to do it actually using VB. You need a low-level language like C, C++ or Delphi to do it. The extended stored procedures require specific entry points in the dll to work and this cannot be done from VB. Can you implement the VB code using UDF/SPs? Alternatively, you can use the VB program outside of SQL Server by running it from a SQLAgent job.
    Tuesday, November 8, 2005 12:53 AM
  • The wizard will only create stubs that you have to implement. So you will have to call the VB program or use the DLL directly from C++. But this seems like lot of effort and features like extended stored procedures for example needs to be used with care. You can create a VB OLE automation object and then use it via the OLE automation SPs in SQL Server. Look for sp_OACreate. This may be the solution you are looking for. In any case, running external code from TSQL can have performance issues and it depends on the usage.
    Tuesday, November 8, 2005 11:42 PM

All replies

  • Hi.

    Do you get the same with CREATE ASSEMBLY and CREATE PROC?
    CREATE ASSEMBLY ...
    CREATE PROC ... AS EXTERNAL NAME ...

    I guess I would not use MyFunctionA, not 'MyFunctionA', but I don't know if that matters.
    Monday, November 7, 2005 11:11 PM
  • You cannot use DLLs created by VB as extended stored procedures. There is no way to do it actually using VB. You need a low-level language like C, C++ or Delphi to do it. The extended stored procedures require specific entry points in the dll to work and this cannot be done from VB. Can you implement the VB code using UDF/SPs? Alternatively, you can use the VB program outside of SQL Server by running it from a SQLAgent job.
    Tuesday, November 8, 2005 12:53 AM
  • Hi,

    I do not know Assembler (MASM, I Guess?), so I would not know.  About an hour after you posted, someone answered my query.  The answer is:  You cannot do it in VB. You must use a lower level language like C, C++, or Delphi. If you want more info, you can view the answer at: 

    http://forums.microsoft.com/msdn/showpost.aspx?postid=126929&siteid=1

    Thanks for your help though.

    Tuesday, November 8, 2005 1:10 PM
  • Hi,

    Unfortunately I cannot implement the VB Code using UDFs or SPs.  The VB Code I wrote is simply a wrapper around another DLL to simplify the interface.

    Unfortunatley, I'm sort of stuck using the VB Function directly inside of the SP.  I did some more digging and found C++ has an Extended Stored Procedure Wizard.

    I know it sounds a bit clugy, but can I use the Wizard to create a C++ Program, which is a wrapper around the VB Program, which is a wrapper around the DLL?

    Since I do not know C++, this might be the easier approach instead of rewriting VB in C++.

    Books On-Line for SQL Server (Extended Stored Procedures >> Creating) gives some instructions, but it is confusing.  The Example xp_hello may be helpful along with the other examples.

    Thanks for your help!

    Tuesday, November 8, 2005 6:01 PM
  • The wizard will only create stubs that you have to implement. So you will have to call the VB program or use the DLL directly from C++. But this seems like lot of effort and features like extended stored procedures for example needs to be used with care. You can create a VB OLE automation object and then use it via the OLE automation SPs in SQL Server. Look for sp_OACreate. This may be the solution you are looking for. In any case, running external code from TSQL can have performance issues and it depends on the usage.
    Tuesday, November 8, 2005 11:42 PM
  • I have tried this solution and it seems to be very easy to implement:    sp_OACreate, sp_OAMethod, and sp_OADestroy.  It also seems to be much faster than you indicated.
    Thank you very much for your help!!!

    Thursday, November 10, 2005 8:36 PM