0

I've got a script (batch) and a table in a database (Microsoft sql server). I need them to work together.

Script collects data from the database and uses it to call an external program which prints labels with the data collected from the table and the external program into pdf.

On every insert to the table, I want the script to run using one of the fields from the insert as a parameter.

Example:

INSERT INTO table1
  VALUES (value1, value2, value3);

So then I want the script to run like: Script.bat value1

I've been told triggers are not the best option because if there's any error during the trigger the insert won't be made.

Hope you can help me!

Barking Flea
  • 3
  • 3
  • 3
    And why are you worried about errors? You can deal with them approprietly. We will need more details. This seems like a perfect example for triggers (depending on what your script does). – Kamil Gosciminski Sep 28 '18 at 09:52
  • What do you mean by : So then I want the script to run like: Script.bat value1 Do you want to run a .bat file from sql or what exactly do you want to do – Praneet Nadkar Sep 28 '18 at 09:53
  • If possible then yes @PraneetNadkar – Barking Flea Sep 28 '18 at 10:03
  • @BarkingFlea, I think it would be best for a trigger to capture newly inserted values into another table. Schedule a separate batch process to generate the PDFs using that as a source. – Dan Guzman Sep 28 '18 at 10:14
  • @DanGuzman, PDFs are not created directly, they use data from an external program. Edited post. – Barking Flea Sep 28 '18 at 10:31
  • @BarkingFlea, that doesn't change the approach I suggested. The bottom line is that you don't want SQL Server itself to launch the PDF generation. – Dan Guzman Sep 28 '18 at 10:40
  • @DanGuzman, you're right, but I'm missing a bit. Having the new table with the inserted values. How can I send the values as a parameter to the script? – Barking Flea Sep 28 '18 at 10:48
  • 1
    You don't necessarily need to "send" the parameters to the script. You could have the script retrieve the values from the table containing the newly inserted values. Alternatively, create a wrapper script that does that and passes those rows as values to your existing script. – Dan Guzman Sep 28 '18 at 10:52
  • This sounds like a very bad idea. You should never allow the SQL Server process to be the parent of another process that consumes data from the database or any publicly accessible source. Batch files are notoriously sensitive to attacks on their input data that can lead to execution of arbitrary code. Take great care to insure that any data passing through your database tables to your scripts, do not contain any of cmd.exe's special characters. Better to send a signal/message of some sort to another process, preferably running on another machine or VM, that then queries and validates inputs. – jwdonahue Sep 28 '18 at 22:01

1 Answers1

0

In fact it is not a good idea to execute external scripts within a trigger but you can test and evaluate results in a development server

Here is the sample SQL trigger code

create trigger tgSample on FolderTable after Insert
as
begin

declare @name varchar(100)

DECLARE newdata_cursor CURSOR FAST_FORWARD FOR 
select foldername from inserted

OPEN newdata_cursor

FETCH NEXT FROM newdata_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @cmd VARCHAR(1000)
    SET @cmd = 'c:\desktop\example.bat' + ' ' + @name
    EXEC master..xp_cmdshell @cmd

    FETCH NEXT FROM newdata_cursor INTO @name
END

CLOSE newdata_cursor
DEALLOCATE newdata_cursor

end

Please note that I pass parameter to the batch script within the code.

One important note related with triggers, triggers work in a set based manner So be prepared with execution of a trigger for more than one row. So assuming you can insert, for example 10 rows into the table, I had created a SQL cursor and executed the batch script one by one. If it is possible, according to your batch file, you can combine all effected data and pass it once to the batch command without a cursor

I hope it helps

Eralper
  • 6,461
  • 2
  • 21
  • 27