Start a program from SQL trigger

1

Hi,
I have an exe that sends data to a printer. When I run it as a standalone, it works fine. When I call the same program from an SQL trigger, it doesn't work. I know it runs at the background but no jobs are sent to the printer.
The exe doesn't have a user interaction interface. It simply sends the job to the printer and terminates/closes itself.
Any ideas how I can have the program work?

2

Welcome.

Could you share a bit more details?
Show us your trigger and also where is the location of this exe?

3

To be honest - I would never call out to an external program from a trigger. Any blip at all and that transaction gets rolled back - and worse, if that exe gets hung it could cause SQL Server to crash.

I can think of 2 possibilities:

  1. Implement a service broker - then in the trigger start a conversation on that service broker (basically add messages to the queue) and let the service broker manage sending data to the printer).
  2. Implement an agent job that runs every xx minutes - query for the data and send it to the printer.

I also think your trigger is not written appropriately. In SQL Server - triggers are executed once per insert and not once per row, so if someone inserts 1000 rows in a single insert statement your trigger would only fire the one time. Depending on how you wrote the trigger you will most likely be missing data.

2 Likes
4

CREATE TRIGGER InsertEmployee
ON emp_details
AFTER INSERT
AS
BEGIN
DECLARE @id int
DECLARE @cmd sysname
SELECT @id = empid FROM inserted

  SET @cmd = 'echo ' + @id + ' > c:\testing\output.txt';  
  EXEC master..xp_cmdshell @cmd;

END

both the executable and the output file are in the same folder C:\testing

5

I see. Thanks for the insight.
The use case for this is that i have a biometric reader. Every time a user is authenticated, which happens to be at a canteen, i want to print a meal ticket with the user's name. So its more or less real time and not scheduled.

6

As @jeffw8713 recommends. Maybe you could have a column isPrinted boolean data type that is true by default at creation time (so you dont print everything) then another process that trips the row to isPrinted to false, prints thern sets it back to true.

7

Your trigger is not correct - if someone inserts 2 rows in a single statement, the @id variable will be set to one of them, with no idea which one and the other would be skipped.

Ideally, you would move this functionality to the application instead of trying to work it into a trigger. If a service broker is too much - then a separate table where you insert the necessary data for the printing process.

Then a separate process (a windows service - for example), can then query that table for xx number of rows, generate the output, print the items for all selected id's - and delete the row (or mark as processed).

Your trigger then becomes a simple insert into the processing table:

Insert Into dbo.MealTicketPrintQueue (EmployeeID)
Select EmpID From inserted;

Add a CreatedDate column with a default of getdate() - a bit column for Printed - a column for PrintedDate. The service then selects any non-printed - updates the table to printed for each empid printed and updates the printeddate to the date/time printed.