August 12, 2002 at 5:01 pm
hi everyone,
i am wondering if it might be possible to call a COM object (and pass in some parameters) from a sql stored procedure.
basically, i'm using data from a few tables in a database and using them to create new records in a third-party product's database. this third-party software provides a COM API to help make new records...handling the formatting and little procedures it requires before creating a member. is it possible use the COM object from within SQL or do I have to write a VB/Delphi/ASP/etc app to use the COM object?
THank you so much in advance@!!!!
Brian
August 12, 2002 at 5:13 pm
You can. If its a simple COM object anyway, dont think it would be very functional for one with tons of collections, just because the syntax is so complex. Take a look at sp_oacreate in BOL. For simple components I usually try to get it down to just one method call with parameters. For more complex stuff I'll either run it using VBScript from a job, or wrap a small exe around it.
Andy
August 12, 2002 at 5:28 pm
Be very, very careful!! SQL's support for COM is not very good at all, and in my experience is extremely unreliable. Oh the COM works, and you get your contributions or external updates, but you also have a nasty little memory leak left over from 7 (assuming your on 2000). One day, without warning, your production server will freeze, and you'll need a reboot. This got so bad on my clients box that I actually installed a secondary server that handled all of the COM-based interfacing. Then I used remote proc calls to get this to do the nasty stuff. This means the production server doesn't get rebooted hardly ever, and the little PC in the corner can be rebooted whenever is necessary. I have successfully used Visual Foxpro, VB and VC++ COM objects with SQL. Do remember to release your object handle when your done or you'll eat memory even faster...
Regards
Simon,UK
August 12, 2002 at 6:23 pm
Andy, i'm sorry but i'm a newbie. what is BOL? I'd really like to take a look at that script. thanks so much!
brian
quote:
You can. If its a simple COM object anyway, dont think it would be very functional for one with tons of collections, just because the syntax is so complex. Take a look at sp_oacreate in BOL. For simple components I usually try to get it down to just one method call with parameters. For more complex stuff I'll either run it using VBScript from a job, or wrap a small exe around it.Andy
http://www.sqlservercentral.com/columnists/awarren/
August 12, 2002 at 6:33 pm
No problem at all. It's Books Online, the standard SQL help file.
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply