Triggering an application from SQL Server?

Hi,

I was just wondering would it be possible to have a trigger in a SQL 2000
database launch a VFP application to print a label? I think setting up the
trigger is pretty straight forward but having it launch a VFP application is
more tricky. I guess the trigger would have to pass a couple parameters
that the application would use for a look up.

Is this possible and what would I have to do on the VFP end to make it work?

Thanks in advance,
Linn

David
Mon Sep 17 14:57:46 PDT 2007

"Linn Kubler" <lkubler@chartwellwisc2.com> wrote in message
news:e2fJeFX%23HHA.748@TK2MSFTNGP04.phx.gbl...
> Hi,
>
> I was just wondering would it be possible to have a trigger in a SQL 2000
> database launch a VFP application to print a label? I think setting up
> the trigger is pretty straight forward but having it launch a VFP
> application is more tricky. I guess the trigger would have to pass a
> couple parameters that the application would use for a look up.
>
> Is this possible and what would I have to do on the VFP end to make it
> work?
>
> Thanks in advance,
> Linn
>
>
>

For a number of reasons it wouldn't be sensible to attempt such a thing from
a trigger. A trigger executes in the context of a transaction - ie. *before*
that transaction has committed, so you could end up printing data for a
phantom transaction that later gets rolled back. You'll cause unnecessary
blocking by holding the transaction longer than you need to. Also, if your
VFP application opens a new connection to the database then you could
possibly induce an undetectable deadlock.

I'd say the "right" place to do this is within whatever process caused the
update in the first place - not within a trigger.

If you must do it from SQL code then do so *outside* a transaction. Use
xp_cmdshell to execute your program in a DOS shell. Another alternative
would be to use COM via the sp_OA procs (see sp_OACreate in Books Online)
but in my experience that approach is too prone to memory leakage.

Finally, have you considered using SQL Server Reporting Services to print
your labels?

Hope this helps.

--
David Portas

Linn
Tue Sep 18 06:26:05 PDT 2007


"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:u0PDKXX%23HHA.6008@TK2MSFTNGP04.phx.gbl...
> "Linn Kubler" <lkubler@chartwellwisc2.com> wrote in message
> news:e2fJeFX%23HHA.748@TK2MSFTNGP04.phx.gbl...
>> Hi,
>>
>> I was just wondering would it be possible to have a trigger in a SQL 2000
>> database launch a VFP application to print a label? I think setting up
>> the trigger is pretty straight forward but having it launch a VFP
>> application is more tricky. I guess the trigger would have to pass a
>> couple parameters that the application would use for a look up.
>>
>> Is this possible and what would I have to do on the VFP end to make it
>> work?
>>
>> Thanks in advance,
>> Linn
>>
>>
>>
>
> For a number of reasons it wouldn't be sensible to attempt such a thing
> from a trigger. A trigger executes in the context of a transaction - ie.
> *before* that transaction has committed, so you could end up printing data
> for a phantom transaction that later gets rolled back. You'll cause
> unnecessary blocking by holding the transaction longer than you need to.
> Also, if your VFP application opens a new connection to the database then
> you could possibly induce an undetectable deadlock.
>
> I'd say the "right" place to do this is within whatever process caused the
> update in the first place - not within a trigger.
>
> If you must do it from SQL code then do so *outside* a transaction. Use
> xp_cmdshell to execute your program in a DOS shell. Another alternative
> would be to use COM via the sp_OA procs (see sp_OACreate in Books Online)
> but in my experience that approach is too prone to memory leakage.
>
> Finally, have you considered using SQL Server Reporting Services to print
> your labels?
>
> Hope this helps.
>
> --
> David Portas
>
Hi David,

I'm not sure they actually use transactions in this database application so
I'm not sure I need to worry about roll backs. I don't have access to the
source code, it's a purchased application. I actually thought this is what
triggers were designed for, a state change in a record causes a trigger to
fire running another process.

I'm not familiar with SQL Reporting Services I'll have to look that up. Is
it a purchased product or built into SQL? What I'm looking for is a
sollution that doesn't require user intervention.

Thanks,
Linn

David
Tue Sep 18 13:43:40 PDT 2007

"Linn Kubler" <lkubler@chartwellwisc2.com> wrote in message
news:OqRB4df%23HHA.1484@TK2MSFTNGP06.phx.gbl...

> Hi David,
>
> I'm not sure they actually use transactions in this database application
> so I'm not sure I need to worry about roll backs. I don't have access to
> the source code, it's a purchased application. I actually thought this is
> what triggers were designed for, a state change in a record causes a
> trigger to fire running another process.
>
> I'm not familiar with SQL Reporting Services I'll have to look that up.
> Is it a purchased product or built into SQL? What I'm looking for is a
> sollution that doesn't require user intervention.
>

If it's a third party app then triggers may be the way to go. Triggers are
fine for DML operations but not for invoking external code because a trigger
runs in an implicit transaction. What I would do is to use a trigger to
insert the updated rows to another table (example below). Then create a SQL
Agent job that periodically polls the print queue table looking for new
data, calls the report program if necessary and then removes the data after
printing.

CREATE TRIGGER trg1 ON dbo.YourTable FOR INSERT AS
IF @@ROWCOUNT >0
INSERT INTO dbo.PrinterQueue (col1, col2, col3)
SELECT col1, col2, col3
FROM inserted;
GO

As with any change there is always a possibility that the application code
could be affected. Test carefully.

Reporting Services is a solution for creating and publishing reports and
it's included with SQL Server.
http://www.microsoft.com/sql/technologies/reporting/default.mspx

--
David Portas