Extended Stored Procedures and VB

Hot Topic

Debugging Custom Report Data Processing extension
Web Synchronisation Throuth SSL
Accessing Mobile SQL Databases in VB2005 - Windows Apps
exporting
SSMS + *.html files.
Any clever ideas for associating a Report Manager session with a web application session?
Problem with a date function
How do I drop sql server mobile database when ...?
how to know status of previous control flow
Strong SA password

SQL Server

Error:max row size can not be more than 8060 characters in Sql-server2000
table script creation error: "Property AnsiNulls is not available for UserOptions"
Materialized View Doesn't Materialize. (and queries on MVs ignore index hints)
Select statement with dynamic columns based on data content
Data Type timestamp
Document Job Steps
A/Synchronous execution of SSIS ETL packages
Unable to attach to devenv.exe to debug
I want make table 4 like this one in SQL server 2005
Shared Datasource Problem

Extended Stored Procedures and VB

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



Answer this question

Extended Stored Procedures and VB

  • Rolandkline

    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.

  • Mercenary4

    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.

  • Blue Eyes

    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!!!

  • Mr. Trevisan Andrea

    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.


  • sigmasoft

    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.

  • Talinu

    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!
  • Extended Stored Procedures and VB
Answer this question