July 22, 2003 at 2:42 am
Can anyone help me out with sample code, as to how to create an extended Stored Proc in Visual Basic. All i could find in the online help is with respect to Visual C++
Thanx in advance
Prabhakar
Prabhakar
July 22, 2003 at 3:48 am
Hi Prabhakar,
quote:
Can anyone help me out with sample code, as to how to create an extended Stored Proc in Visual Basic. All i could find in the online help is with respect to Visual C++
unless I am mistaken, you can't use VB to programm an extended stored procedure.
I think you could only use C, C++, maybe Delphi?, because you need a language that gives you control on how to export functions. I don't know if this has changed with .NET, but in VB 6 DLL's are only ActiveX component, meaning they are only able to expose their objects.
However, maybe as a workaround, write your procedure in VB and invoke it using SQL Server's sp_OA... functions. I haven't tried this before, but it should work once you have registered the component.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 23, 2003 at 6:11 am
Prabhakar,
You indeed can not create extended stored procedures with VB. Extended procedures must be created with a language capable of producing true Win32 executable image files. VB requires the VB run time library and therefore does not create a true executable.
I too am not sure if VB.net changes this. I would lean towards saying it probably does not support it either as you need to install the .net framework to execute .net applications which means they are not stand alone executables either.
Pat
Edited by - bustell on 07/23/2003 06:11:54 AM
Pat Buskey
July 23, 2003 at 4:58 pm
Why, if the dependent files (VB Runtime or .net framework) are available on the server couldn't VB be used for extended procedures? If a setup.exe were created as a package that included all the dependent filed and installed on the server it should be able to be accessed. Or not? After all that's what 3 tier applications do. I expect it would not be difficult to test anyway.
July 23, 2003 at 4:59 pm
I have not seen anything on .NET but I believe you are correct. However VC++ 7 (which is in .NET) can as long as you don't use the .NET framework from my understanding.
July 24, 2003 at 12:36 am
H scking,
quote:
Why, if the dependent files (VB Runtime or .net framework) are available on the server couldn't VB be used for extended procedures? If a setup.exe were created as a package that included all the dependent filed and installed on the server it should be able to be accessed. Or not?
I don't think so, because VB6 doesn't let you export functions the way C or C++ does. VB6 Dll's are not true standalone Dll's, as has already mentioned above. Try something similar in VB:
__declspec(dllexport) ULONG __GetXpVersion()
{
return ODS_VERSION;
}
taken from BOL.
I'm pretty sure when trying to call a VB Dll function, you'll receive something like
"Cannot find the function blabla in the library blabla.dll. Reason: 127(The specified procedure could not be found.)."
because even if there is an entry point found like DllMain, the function itself will not be found.
Don't get me wrong, I would like to be mistaken, because it would potentially make things easier on the one side if VB would work, on the other side I think it's ok that only C or C++ could be used.
Why? Because poorly programmed extended stored procedures (no matter what language) are likely to crash SQL Server, as is mentioned here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_con_01_9rxv.asp
Here are some more useful links on this
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_con_01_3fhq.asp
http://search.microsoft.com/search/results.aspx?View=msdn&st=a&s=2&c=4&qu=extended+stored+procedures
And once again, I have no experience with .NET.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 24, 2003 at 2:37 pm
You can do as a5xo3z1 suggested. Create an Active-X DLL in VB then use the sp_OAxxx procedures to execute it's method(s). I've done it several times and it works great.
July 25, 2003 at 1:42 am
Thanx all for keeping the thread live...it took sometime for me to come to conclusion....anyways i'd created a dll and called it using the following functions...
***************************
DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
DECLARE @src varchar(255), @desc varchar(255)
-- Create an object.
EXEC @hr = sp_OACreate '{0839A7DC-8313-441D-BC73-C4A6D14B65D8}', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
-- Call a method.
EXEC @hr = sp_OAMethod @object, 'writetxt',NULL,'This is my parameter'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
-- Destroy the object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
***************************
Thanx again for ur suggestions
Prabhakar
Prabhakar
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply