trigger to call a program to write a text file onto a folder in the server

  • Hi to all

    I have created a trigger to call a program that is written by our program. The program is basically read the record in the table and write to a text file, then delete the record from the table.

    The trigger is a after insert trigger. After we added the trigger, we insert a record to the table. The result is that the record still and did not get deleted. Also, the text file didn't get created either. It seems that it take a long time for the record to be written to the table.

    But if we just run the program (a exe file), it can write a text file in the folder and delete the record. the trigger is basically:

    USE [Zinter]

    GO

    /****** Object: Trigger [dbo].[ZinterProcess] Script Date: 04/29/2014 18:34:56 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    create trigger [dbo].[ZinterProcess] on [dbo].[Zinter]

    after insert

    as

    begin

    execute master.dbo.xp_cmdshell 'start C:\ZinterProcessor\ZinterProcessor.exe'

    end

    GO

    Any idea or help will be greatly appreciated.

    thx, Ted.

  • Why would you want to delete a row that has just been inserted in a table?

    If you really need something like that (instead of directly executing the program) you could use an instead trigger to prevent inserting the row in the first place.

    Remember that the trigger is part of the transaction and the rows won't be available until it finishes and the transaction is commited.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • xp_CmdShell uses a differnent session than the session the trigger is involved with. I also don't see where you're identifying a record to the .exe (of course, I don't know anything about the .exe, either).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • If I understand you correctly you have an application which inserts a row into a table. Then you have a trigger on that table to copy the data that was just inserted into a text file on the file system. Then you delete the row from sql? Why do you even need to use sql at all for this? You could eliminate a lot of steps by changing the application that inserts into sql to just write the text file and skip sql entirely. 😉

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • hi to all

    I had to agree that the reason for this trigger doesn't make sense. It is because we have 2 system.

    a) the first system can only generate data to a database

    b) the second system can only read in text file or csv file

    Both systems are 3rd party software that our company decide to use. So I end up were asked to write a trigger to call a program that a programmer wrote which did the following:

    a) get the inserted record

    b) write it to a text file

    c) delete the record in the database

    If i can do anything so that the insert record can be output to a text file, i will be very happy, because i don't think we need to delete the record. Once the record deleted from the database, we have no single trace. I would rather just export the record which only has 6 fields to a text file with delimiter using ","

    The program that wrote work fine if it just execute on a cmd line. It will read all records and delete the record. But when i use it in trigger it just look like it did nothing. It take a while for the record to show up when i use : select * from xyz and there is no text file created.

    I think either the user id (credential) don't allow it to create a text file and save it to a folder and/or like the post earlier said the insert is not committed yet and it cannot be delete?

    I am in search of a trigger script that can trigger after insert and export the inserted record to a text file. It seems that it can be done but i am very new to trigger. Any sample will be greatly appreciated.

    thx, Ted.

  • eseeweb (4/30/2014)


    hi to all

    I had to agree that the reason for this trigger doesn't make sense. It is because we have 2 system.

    a) the first system can only generate data to a database

    b) the second system can only read in text file or csv file

    Both systems are 3rd party software that our company decide to use. So I end up were asked to write a trigger to call a program that a programmer wrote which did the following:

    a) get the inserted record

    b) write it to a text file

    c) delete the record in the database

    If i can do anything so that the insert record can be output to a text file, i will be very happy, because i don't think we need to delete the record. Once the record deleted from the database, we have no single trace. I would rather just export the record which only has 6 fields to a text file with delimiter using ","

    The program that wrote work fine if it just execute on a cmd line. It will read all records and delete the record. But when i use it in trigger it just look like it did nothing. It take a while for the record to show up when i use : select * from xyz and there is no text file created.

    I think either the user id (credential) don't allow it to create a text file and save it to a folder and/or like the post earlier said the insert is not committed yet and it cannot be delete?

    I am in search of a trigger script that can trigger after insert and export the inserted record to a text file. It seems that it can be done but i am very new to trigger. Any sample will be greatly appreciated.

    thx, Ted.

    You are already using command shell. Why not just use that to do your work, then you don't need this extra application at all. Or if CLR is an option at your shop that would be another option.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Hi

    I am very new to trigger. Not sure how i can do that. If possible, please help out. Is there any example or ideas on how to do this...

    thx, Ted.

  • eseeweb (4/30/2014)


    Hi

    I am very new to trigger. Not sure how i can do that. If possible, please help out. Is there any example or ideas on how to do this...

    thx, Ted.

    A quick binoogle search on "sql server command shell to write file" returns this...

    http://social.msdn.microsoft.com/forums/sqlserver/en-US/3daf661d-cc44-4aee-b87c-7d5529d11fc1/how-to-use-xpcmdshell-to-write-to-text-file

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • thank you for replying. Let me try, will update later.

  • Don't have the trigger do this. Have the trigger insert the data you want to write out to a new table. Use a date, clear the table, something, but store this. Then have a process pull data out of the table (all or certain rows) and write the text file. You can certainly have a process kicked off by the trigger (sp_start_job) or one that polls and looks for change. You could even insert the data into a Service Broker Queue and have an activation proc that writes it out. That's more complex, but it works.

    Decouple this so that problems with the output process (rights, full disk, etc) don't wreck the process that will fire the trigger.

  • hi

    I just have an update that we don't need to delete the record from the table as long as every time a record inserted can be write to a text file for the other side to consume. So, i did a quick test like this and have access denied issue.

    exec master.xp_cmdshell 'echo hello > c:\file.txt' which try to write to the c drive on the sql server.

    but if i quality it as

    exec master.xp_cmdshell 'echo hello > \\myPC\c$\file.txt'

    The issue is when i log on to sql server, i can add/write/mod files from c drive. Any reason? How to i use "execute as" statement?

    thx, Ted.

  • eseeweb (4/30/2014)


    hi

    I just have an update that we don't need to delete the record from the table as long as every time a record inserted can be write to a text file for the other side to consume. So, i did a quick test like this and have access denied issue.

    exec master.xp_cmdshell 'echo hello > c:\file.txt' which try to write to the c drive on the sql server.

    but if i quality it as

    exec master.xp_cmdshell 'echo hello > \\myPC\c$\file.txt'

    The issue is when i log on to sql server, i can add/write/mod files from c drive. Any reason? How to i use "execute as" statement?

    thx, Ted.

    SQL Server needs to have the privs to write to whatever UNC you're using. I STRONGLY recommend that you NEVER have SQL Server writing to UNCs that are desktop servers. I can see it writing to company shares on a dedicated share box but not to desktop PCs.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (4/30/2014)


    SQL Server needs to have the privs to write to whatever UNC you're using. I STRONGLY recommend that you NEVER have SQL Server writing to UNCs that are desktop servers. I can see it writing to company shares on a dedicated share box but not to desktop PCs.

    +1

  • eseeweb (4/30/2014)


    hi

    I just have an update that we don't need to delete the record from the table as long as every time a record inserted can be write to a text file for the other side to consume. So, i did a quick test like this and have access denied issue.

    exec master.xp_cmdshell 'echo hello > c:\file.txt' which try to write to the c drive on the sql server.

    but if i quality it as

    exec master.xp_cmdshell 'echo hello > \\myPC\c$\file.txt'

    The issue is when i log on to sql server, i can add/write/mod files from c drive. Any reason? How to i use "execute as" statement?

    thx, Ted.

    Does your command shell proxy account have adequate privileges set on the destination folder and does it have network privileges (the built in local service account will have local admin rights but no network privileges - other accounts including the network service account will need permissions set)?

    Have you considered creating an SSIS package to poll the table write to the table and tidy up for this rather than a trigger? It would be easier to handle error conditions.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply