Calling external code

Question

Calling external code

Jon-183221

SSCommitted

Points: 1908
More actions
July 8, 2005 at 3:45 pm

#66677

Hi,
I have recently taken over a development project which is partially complete. I am trying to put together some options for solving a particular problem and was looking for some advice on how I could procede.
I have a VB.net\Sql Server 2000 application. in some cases I have UDF's that I would like to be able to run certain VB functioality. I dont know if this is possible full stop, I think this is something I might be able to do with 2005 (am I right?) but that isnt an option right now. The sort of functionality I'm looking for is to pass in a couple of parameters and get a return value.
I can think of a couple of possible ways to do it but I'm not sure if these are possible in any way?
Extended Stored Procedures - am i right in thinking these need to be written in C or C++? If they dont have to be in C could this be an option?
VBScript - I know I can create a DTS package to run vbscript, which would possibly allow me to crete an object reference but can I get a return value from this? Is there any other way to call this outside of DTS in a UDF as DTS seems quite clumsy?
Re-write the VB functionality as SQL UDF's and SP's. This I know I can do but I'm trying to be a bit clever (unlike me) and impress my new boss.
Any input on this would be greatly appreciated, even if its to tell me what I think I already know.
Jon

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

UDFs in SQL Server 2005 can be CLR. Whoa to us DBAs.

Outside of xprocs and rewriting the functionality, you can always call a script using xp_cmdshell, however, the transfer of parameters back and forth may take some doing. The other main option is to use the sp_OA* stored procedures to instantiate COM objects, etc. The sp_OA* are actually extended stored procedures themselves but they may save you some time since you're not converting your own code to an xproc. Both of these typically require sysadmin rights to execute, though and that's the main catch.

K. Brian Kelley
@kbriankelley

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Jon,

What kind of VB functionality are you looking to do?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jon-183221 SSCommitted Points: 1908 More actions · Answer 3

The functioanlity is mostly data processing and if im honest I dont know why it wasnt written in SQL to start with, so for example pass in a start and end date and then work out how many working days an employee has between those two dates. This means checking shifts, non working days and stuff like that, it would be reasonable to put this into SQL. I've just come on board this project and I need to try and bring it back on schedule, I'm not willing to compromise quality but at the same time if there is an easier way I'm wiling to listen.

Thank you for the suggestion of using the SP_OA stored procedures, even if I dont use them this time its great to know for the future. I spent all afternoon playing with them.

Thanks

Jon

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

It sounds like you may need to build a "Calendar Table". Lot's of folks have wriiten about their construction and use. I'd also recommend taking a peek at the following URL...

http://www.aspfaq.com/show.asp?id=2519

So far as the SP_OA stored procedures go, there's a nasty little rumor (that I've not been able to confirm) that they a fraught with memory leaks. I don't use them but if I did, I'd do some pretty hefty testing to find out if that rumor was true or not. Maybe someone else that reads this will know for sure and post an answer to that rumor one way or another.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

I don't think I've seen anything indicating for sure there are memory leaks in the sp_OA* extended stored procedures. However, since they serve the purpose of instantiating COM objects in SQL Server, you face all the potential issues with respect to doing that. Mismanagement of such can result in memory problems, just as they would in a normal application.

K. Brian Kelley
@kbriankelley

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

I found it and I was correct...

They first found memory leaks in the SP_OA procs in SQL Server 6.5 and supposedly fixed it... see the following URL:

http://support.microsoft.com/kb/151601/EN-US

I guess there weren't any complaints about the memory leaks in 7.0... couldn't find anything but I'd still be cautious and do a test.

Then, the SP_OA memory leaks reappear in SQL Server 2000... see the following URL:

http://support.microsoft.com/kb/q282229/

The bottom line is, I don't trust the little buggers and even after the fix, I'd still do a test before I used them...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

True, but the leaks in SQL 2000 were in the RTM. They fixed it in SP1 and we're up to SP4 now. While SP4 had its own issues with AWE memory, they've probably taken care of the sp_OA* memory leaks.

K. Brian Kelley
@kbriankelley

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

Either way, there have been problems with SP_OA and memory leaks of one form or another. My recommendation is to test it for memory leaks before you put it into production because it's been an on and off problem a couple of times.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)