Call a Dll from Stored Procedure

Can you call a .dll from a stored procedure?
I am looking to use this as a trigger to run some other software.
I have a piece of software that I was going to run as a service when a
record is updated. But I have no way to trigger the service that something
has happened.
For example:
If a record is updated, I want a program to run that will run a web service.
If a stored procedure can call a .dll, that method could call the web
service.
Thanks,
Tom"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OtpHii7RGHA.4264@.TK2MSFTNGP11.phx.gbl...
> Can you call a .dll from a stored procedure?
> I am looking to use this as a trigger to run some other software.
> I have a piece of software that I was going to run as a service when a
> record is updated. But I have no way to trigger the service that
> something has happened.
> For example:
> If a record is updated, I want a program to run that will run a web
> service. If a stored procedure can call a .dll, that method could call the
> web service.
Look into extended stored procs. If you run C++ and select new project one
of the project types is extended store proc.
Michael|||"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OtpHii7RGHA.4264@.TK2MSFTNGP11.phx.gbl...
> Can you call a .dll from a stored procedure?
> I am looking to use this as a trigger to run some other software.
> I have a piece of software that I was going to run as a service when a
> record is updated. But I have no way to trigger the service that
> something has happened.
> For example:
> If a record is updated, I want a program to run that will run a web
> service. If a stored procedure can call a .dll, that method could call the
> web service.
> Thanks,
> Tom
>
In SQL 2005 you can put .NET code directly in a proc.
In SQL 2000 you can create your own extended proc or use the sp_OA
automation extended procs. Neither option is great and extended procs are
now deprecated. Use 2005 if you can.
Alternativey, you could just have your external code poll the table
regularly and action any changes based on a date-timestamp of new rows.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On second thoughts and in light of Michael's response, my reply deserves a
bit of clarification. When I say "not great" what I mean is that in my
experience the COM interface frequently leaks memory. That's based on
several brushes with the sp_OA procs in 2000. I don't have personal
experience of developing extended procs in C++ so I shouldn't comment on
those beyond what Books Online says about them (below). I have used some
third party XP (xp_smtp_sendmail) and not noticed any particular problem in
that case.
<quote>
Extended Stored Procedures
This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature. Use CLR Integration instead.
[...]
Extended stored procedures may produce memory leaks or other problems that
reduce the performance and reliability of the server. You should consider
storing extended stored procedures in an instance of SQL Server that is
separate from the instance that contains the referenced data. You should
also consider using distributed queries to access the database. For more
information, see Distributed Queries.
</quote>
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> On second thoughts and in light of Michael's response, my reply deserves a
> bit of clarification. When I say "not great" what I mean is that in my
> experience the COM interface frequently leaks memory. That's based on
> several brushes with the sp_OA procs in 2000.
It might be lousy implemenations of the particular COM methods you
have used.
But nevertheless, you were right on target. Writing extended stored
procedures, or calling your own COM methods through sp_OAxxx comes
with big warning signs. Performance is not fantastic, as there is some
context switching. But what is really ugly is that since the DLLs
are in-process, an execution error like an access violation will
crash the entire SQL Server.
On SQL 2005 writing a trigger in a CLR language might be the best choice.
But that depends on how long that web service takes. Triggers should
be swift and quick, since you are in a transaction. Triggers that runs
for several seconds in a busy system is a recipe for diasster.
On SQL 2000 the best is have the DLL to poll, or possibly start a job
with sp_start_job.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||My problem is we are not using Sql 2005.
I need to get something to work now. Is the Extended Stored Procedures the
same as Com Interface?
Also, the quote mentions CLR Integration. Is that the same thing as your
"NET code directly in a proc"?
Thanks,
Tom
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ONO%23At7RGHA.2176@.TK2MSFTNGP10.phx.gbl...
> On second thoughts and in light of Michael's response, my reply deserves a
> bit of clarification. When I say "not great" what I mean is that in my
> experience the COM interface frequently leaks memory. That's based on
> several brushes with the sp_OA procs in 2000. I don't have personal
> experience of developing extended procs in C++ so I shouldn't comment on
> those beyond what Books Online says about them (below). I have used some
> third party XP (xp_smtp_sendmail) and not noticed any particular problem
> in that case.
> <quote>
> Extended Stored Procedures
> This feature will be removed in a future version of Microsoft SQL Server.
> Avoid using this feature in new development work, and plan to modify
> applications that currently use this feature. Use CLR Integration instead.
> [...]
> Extended stored procedures may produce memory leaks or other problems that
> reduce the performance and reliability of the server. You should consider
> storing extended stored procedures in an instance of SQL Server that is
> separate from the instance that contains the referenced data. You should
> also consider using distributed queries to access the database. For more
> information, see Distributed Queries.
>
> </quote>
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9787723711E9Yazorman@.127.0.0.1...
> But nevertheless, you were right on target. Writing extended stored
> procedures, or calling your own COM methods through sp_OAxxx comes
> with big warning signs. Performance is not fantastic, as there is some
> context switching. But what is really ugly is that since the DLLs
> are in-process, an execution error like an access violation will
> crash the entire SQL Server.
What this is saying (and the MSDN article quoted by David) is that you might
have a bug in one particular technique therefore don't use that technique.
To me this just means greater caution should be used when using said
technique.
Michael|||"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:upCIsG8RGHA.4456@.TK2MSFTNGP14.phx.gbl...
> My problem is we are not using Sql 2005.
> I need to get something to work now. Is the Extended Stored Procedures
> the same as Com Interface?
No, they are 2 different techniques. Extended stored proc is where you write
code, usually in C++, to create a dll. You can add functions from the dll
similar to other stored procs. You could use this as a wrapper for your
existing dll. Have a look at, for eg, the xp_subdirs extended stored proc in
the master database, xp_subdirs is a function in xpstar.dll (you can see
this by double clicking the ex stored proc).
Com interface is something different. You can get sql2k to call functions on
an existing com interface. If your dll isn't com then this isn't a lot of
use to you unless you write a com wrapper.

> Also, the quote mentions CLR Integration. Is that the same thing as your
> "NET code directly in a proc"?
This method is not available to you in sql2k
Michael|||How time sensitive are your needs? As erland mentioned earlier,
triggers aren't really designed to be event-handlers; they should be
used for quick responses to data changes (such as low-level
validation). If you change several 1000 rows of data in a second, your
trigger needs to be able to fire that many times.
What is that you're trying to do? It sounds like some form of
event-handling, which should be done at a higher level than your data
source. If your needs are not time sensitive, than you may consider
polling your database periodically to see if your criteria is met, or
you might consider having your application call the web service when it
changes the database.
Stu|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1142384900.721926.212080@.p10g2000cwp.googlegroups.com...
> How time sensitive are your needs? As erland mentioned earlier,
> triggers aren't really designed to be event-handlers; they should be
> used for quick responses to data changes (such as low-level
> validation). If you change several 1000 rows of data in a second, your
> trigger needs to be able to fire that many times.
> What is that you're trying to do? It sounds like some form of
> event-handling, which should be done at a higher level than your data
> source. If your needs are not time sensitive, than you may consider
> polling your database periodically to see if your criteria is met, or
> you might consider having your application call the web service when it
> changes the database.
>
That may be the best bet.
I still need to look into the requirements as we are just starting to design
it. But as you say, having the App Call a Web Service might be the best
way.
Thanks,
Tom

> Stu
>