How do I write an Extended Stored Procedure in VB6?

[StewartJ]

I would welcome some advice on how to structure a VB dll so that I can create my own extended stored procedures. Do I need any special components, structures, etc? I am a competent VB programmer but a novice at SQL Server.

 

[tlbroadbent]

Start with "Extended Stored Procedure Programming" in SQL Books Online. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[StewartJ]

Thanks. However, before posting, I did read through Creating and Maintaining Databases / Stored Procedures / Extended Stored Procedures and through Building SQL Server Applications / Open Data Services Programming / Programming Open Data Services. Unfortunately, the latter concentrates on C++.

I have created (in VB6 SP5) a global multiuse dll with one class and two public functions, copied it to C:\MSSQL7\Binn (but not registered it with RegSvr32) and registered it through Enterprise Manager so that its functions are listed under Extended Stored Procedures in the Master database. However, when I attempt to execute a method using Query Analyzer in the Master database with Exec MyFunction "Parameter", I get an error message :

Cannot find the function MyFunction in the library MyDll.dll. Reason: 127(error not found).

The function sits in a class, MyClass, within the dll. I have tried registering the class alone and MyClass.MyFunction as extended stored procedures. They appear but give the same error. I am being careful about case sensitivity. I suspect that I am not 'exporting' the function correctly but don't know how to do this in VB.

I have just come across sp_OACreate and its associated procedures in

http://www.inquiry.com/techtips/thesqlpro/10min/10min0400.asp

but have not tried this yet. Is it as efficient or just the only way to go?

 

[tlbroadbent]

Looks like I rushed like a fool, again. I thought a developer in our shop had created an XP using VB. Apprently, it can't be done. At least everything that I've read tonight indicates that XPs cannot be created in VB. So that leaves you with the sp_OA OLE automation procedures which allow you to call the VB DLL.

I have used the sp_OA procedures a couple of times but am not expert enough in their use to give useful advice. Perhaps, someone more knowledgeable in this area will jump in. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[StewartJ]

Thanks, anyway. I was afraid of that!

 

[StewartJ]

I now have tried the sp_OA procedures and they work OK. As far as I can tell also, you can't write xp's in VB. If anyone can offer further suggestions, they will be welcome but at least the original problem now is bypassed if not solved!

Thanks for your help, Terry.