Hi Guys. I am looking for samp

[MAK9974]

Hi Guys.

I am looking for sample code for creating Extended stored procedure on SQL Server 2000 using Visual Basic 6.0.

All I can get is Using VC++ and ODS APIs.

There is not enough info available on ODS lib either.


Please help.
Thanks
-MAK

 

[StewartJ]

I was trying to do this recently and the concensus is that you can't - VB doesn't povide the correct interface. You can create VB dlls and call them through ODBC using the spOA_ stored procedures. These are well documented in Books Online with samples.

 

[MAK9974]

Can I get a sample on VB code for this.
a small sample to create dll will be enough.
I appreciate it.

-MAK

 

[StewartJ]

Select ActiveX DLL when opening your new project to make the DLL in VB.

Register the DLL using RegSvr32, eg, from the Run menu :

RegSvr32 "MyPath\MyDLL"

In SQL Server you need something on the lines of :

DECLARE @cInputValue VarChar(255)
DECLARE @cOutputValue VarChar(255)
DECLARE @objDLL int

SET @cInputValue = "Something"

EXECUTE sp_OACreate "MyDLL", @objDLL OUTPUT

- objDLL holds a handle to your DLL

EXECUTE sp_OAMethod @objDLL, "MyMethod", NULL, @cInputValue, @cOutputValue OUTPUT

- Don't forget to release it when you have finished

EXECUTE sp_OADestroy @objDLL

 

[MAK9974]

I am getting errors.

"Error Occurred Calling Object: ODSOLE Extended Procedure
sp_OADestroy usage: ObjPointerToBeDestroyed int IN."

This is what I did

Step1
'created a DLL for the following function

Public FirstNumber As Variant
Public SecondNumber As Variant

Function AdditionFunction(FirstNumber, SecondNumber)
AdditionFunction = FirstNumber + SecondNumber
Print AdditionFunction
End Function

Step2

Registered the Dll
regsvr32 d:\mydll\additionfunction.dll

Step3

'Execute this script
DECLARE @cInputValue1 int
DECLARE @cInputValue2 int
DECLARE @cOutputValue int
DECLARE @objDLL int
declare @Hresult int
DECLARE @ErrorSource varchar (255)
DECLARE @ErrorDesc varchar (255)

SET @cInputValue1 = 5
SET @cInputValue2 = 6

EXECUTE @Hresult =sp_OACreate "additionFunction", @objDLL OUTPUT

-- objDLL holds a handle to your DLL

EXECUTE @Hresult =sp_OAMethod @objDLL, "additionfunction",NULL,@cInputValue1 ,@cInputValue2, @cOutputValue OUTPUT

print @cOutputValue
-- Don't forget to release it when you have finished

EXECUTE @Hresult =sp_OADestroy @objDLL

print @objDLL

IF @Hresult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objDLL , @ErrorSource OUT, @ErrorDesc OUT
PRINT 'Error Occurred Calling Object: ' + @ErrorSource + ' ' + @ErrorDesc
RETURN
END