Calling program from SQL Stored Procedure

Hello,
I don't know if this can be done, I'm not an SQL Server expert; But can you
start a program ( VB app ) with a stored procedure.
For instance if a record is written to a sql table then a trigger could be
set to start a vb app from sql'
I'm trying to come up with a way to recognize an insert to a sql table and
this insert would start a vb app which would perform tasks and write
additional fields to the table...
Any help would be welcomed... Thanks
--
T HYou can, but it is very very ill advised.
If the application fails, the transaction gets rolled back.
If your application takes 30 seconds to execute, SQL Server has to hold the
locks, severely impacting transactional throughput.
Your application, as it is outside SQL Server could end up waiting on locks
on rows in the DB that actually launched it.
If you have 100 users working against the table, you could potentially have
100 instances of the application launched by the trigger.
Rather write the record to another table and have an external application
poll the table and perform the functions.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Monster T" <MonsterT@.discussions.microsoft.com> wrote in message
news:6AA60AD3-1EF4-466D-A620-6D49075BD661@.microsoft.com...
> Hello,
> I don't know if this can be done, I'm not an SQL Server expert; But can
> you
> start a program ( VB app ) with a stored procedure.
> For instance if a record is written to a sql table then a trigger could be
> set to start a vb app from sql'
> I'm trying to come up with a way to recognize an insert to a sql table and
> this insert would start a vb app which would perform tasks and write
> additional fields to the table...
> Any help would be welcomed... Thanks
> --
> T H|||You can use xp_cmdshell which can be used to execute programs, esp. if it is
non-interactive and has no ui. See SQL Server Books Online for more details
on xp_cmdshell.
However, based on the narrative, you might want to get some advice on your
overall application design before implementing such convoluted steps.
Triggering a VB app which in turn process data & insert data into tables
sounds like a bad idea.
Anith|||Even if you did call the app, it would be running on the server box and apps
that build recordsets can be memory hogs and security risks. Instead, write
the additional fields using T-SQL.
"Monster T" <MonsterT@.discussions.microsoft.com> wrote in message
news:6AA60AD3-1EF4-466D-A620-6D49075BD661@.microsoft.com...
> Hello,
> I don't know if this can be done, I'm not an SQL Server expert; But can
you
> start a program ( VB app ) with a stored procedure.
> For instance if a record is written to a sql table then a trigger could be
> set to start a vb app from sql'
> I'm trying to come up with a way to recognize an insert to a sql table and
> this insert would start a vb app which would perform tasks and write
> additional fields to the table...
> Any help would be welcomed... Thanks
> --
> T H|||Has anyone seen any information on calling a .NET application from within a
stored procedure? For instance, is there a way a stored procedure could cal
l
a Web Service?
What we have in our situation is a process/custom application that needs to
be launched as soon as the database completes an import of data. This
application is launched on a different server from the database server. In
the past we used the OLE stored procedures to call a COM (that was accessabl
e
via DCOM) but we would like to use .NET in this instance instead of the old
COM ways. It seems the only options I'm aware of are:
xp_cmdshell
OLE stored procedures
If that's the case then we can still write the .NET application but we would
have to make it available to the old COM interface. Anyone know of any othe
r
methods?
Regards,
David Gardner
"Mike Epprecht (SQL MVP)" wrote:

> You can, but it is very very ill advised.
> If the application fails, the transaction gets rolled back.
> If your application takes 30 seconds to execute, SQL Server has to hold th
e
> locks, severely impacting transactional throughput.
> Your application, as it is outside SQL Server could end up waiting on lock
s
> on rows in the DB that actually launched it.
> If you have 100 users working against the table, you could potentially hav
e
> 100 instances of the application launched by the trigger.
> Rather write the record to another table and have an external application
> poll the table and perform the functions.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Monster T" <MonsterT@.discussions.microsoft.com> wrote in message
> news:6AA60AD3-1EF4-466D-A620-6D49075BD661@.microsoft.com...
>
>|||Hi David -
Another option for you *may* be Notification Services. Using the SQL
Server Event Provider, you can configure it to monitor a table and
notify you when a certain change has occurred. In your case, that
notification would be a call to another process on another machine.
You'd need to develop a custom delivery protocol for that purpose.
Just something to check out...
HTH...
Joe
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Tue, 7 Jun 2005 17:00:28 -0700, "David Gardner"
<David.Gardner-Logan@.REMOVECAPSrrd.com> wrote:
>Has anyone seen any information on calling a .NET application from within a
>stored procedure? For instance, is there a way a stored procedure could ca
ll
>a Web Service?
>What we have in our situation is a process/custom application that needs to
>be launched as soon as the database completes an import of data. This
>application is launched on a different server from the database server. In
>the past we used the OLE stored procedures to call a COM (that was accessab
le
>via DCOM) but we would like to use .NET in this instance instead of the old
>COM ways. It seems the only options I'm aware of are:
>xp_cmdshell
>OLE stored procedures
>If that's the case then we can still write the .NET application but we woul
d
>have to make it available to the old COM interface. Anyone know of any oth
er
>methods?
>Regards,
>David Gardner
>"Mike Epprecht (SQL MVP)" wrote:
>sql