Call VB 6.0 dll or function from Stored Procedure in MS SQL Server 7.0

[ottman2]

I'm migrating my databases from MS Access 2000 to MS SQL Server 7.0, but I also want to move my functions that currently exist in MS Access Modules, because the process is too slow from Access and with a Stored Procedure I can schedule it at night as well. I was approaching the idea of creating my functions in Visual Basic 6.0, then creating a dll, then call the function from a Extended Stored Procedure. Does everyone know the process or the code required to do this?

 

[rajeevnandanmishra]

Hi there,
You need to create dll using the Open Data Services concept. And then add the functions of that dll through sp_addextendedproc.

check the BOL for sp_addextendedproc and related topics.

 

[ottman2]

That was helpful, I created my Extended Stored Procedure.
Question:
Where should I keep my dll? Or is it ok, to use Enterprise manager to point my Extended Stored Procedure to the location on a LAN drive?

I'm trying to use my new Extended Stored Procedure function in a view, but it will not find the function. It comes up with ODBC...function not found.

I tried testing my dll by calling it from another VB program. I also was able to call my function from MS Access.

xp_proper_case is the name of my stored procedure and the name of the function.

Here is what my code in my view looks like.
SELECT SSNO, DIV, DEPT_LOCATION, STATUS_CODE, LAST_NAME,
FIRST_NAME, xp_proper_case(FIRST_NAME)
AS P_first
FROM tbl_associates

 

[rajeevnandanmishra]

Hi there,
Sorry for a late reply (because of holidays)!
Just make sure to do the following:
You should put your dlls in the MSSQL\BINN directory.
Must add the procedure in master database.
Grant execute rights to proper user(s)/group(s).

And, remember that these are just procedures. I don't think you can use these as functions in SQL 7.0 or lower.

 

[Guest_imported]

Hi Raj,

I am a new memeber. I have created a sample extended procedure based on your thoughts/steps.

My VB (DLL File) contain following coding.

Public Function UpperCase(xInpStr As String) As String
UpperCase = UCase$(xInpStr)
End Function

I saved this DLL (DemoDLL.dll) in mssql\binn directory.

Afterthat, I was created a Extended procedure.

USE master
EXEC sp_addextendedproc xp_UpperCase, 'DemoDLL.dll'

Successfully created it.

I was tryed to use this x_procedure,

Select xp_UpperCase(Tsth_Desc) From TestHd

error raised. Error message is,

Server: Msg 195, Level 15, State 10, Line 1
'xp_UpperCase' is not a recognized function name.

Thanks and Regards,
Rafiq.

 

[rajeevnandanmishra]

Hi Rafiq,
I think i were unable to explain the concept of adding extended stored procedure.
I am afraid, but the dll created by you can not be used to execute procedures in SQL server.

Please Go through this thread, it will help you understand about adding / creating such dlls.

http://msdn.microsoft.com/library/en-us/odssql/ods_6_con_01_22sz.asp

 

[ottman2]

UpperCase function is exactly the type of functions I want to use in MS SQL 7.0. From the comments from rajeevnandanmishra I will not be able to use these functions created in VB. Can I create something to be used in MS SQL 7.0?

 

[Guest_imported]

can anyone give me proper steps for creating EXTENDED STORED PROCEDURE IN SQL SERVER USING VB DLL.I tried with creating extended stored procedure VB DLL but, i couldn't succeeded. Always i get the error message "function not found in the dll" .
any help will be appreciated.

 

[tlbroadbent]

senthil kumar,

Please create a new thread for this question.

Thanks, Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous