Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

trigger to call a program to write a text file onto a folder in the server Expand / Collapse
Author
Message
Posted Tuesday, April 29, 2014 4:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 5:49 AM
Points: 7, Visits: 34
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.
Post #1566169
Posted Tuesday, April 29, 2014 4:49 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:57 PM
Points: 3,908, Visits: 8,860
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1566173
Posted Tuesday, April 29, 2014 4:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:00 AM
Points: 35,547, Visits: 32,137
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1566175
Posted Wednesday, April 30, 2014 8:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1566411
Posted Wednesday, April 30, 2014 9:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 5:49 AM
Points: 7, Visits: 34
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.

Post #1566442
Posted Wednesday, April 30, 2014 9:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1566451
Posted Wednesday, April 30, 2014 9:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 5:49 AM
Points: 7, Visits: 34
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.
Post #1566472
Posted Wednesday, April 30, 2014 10:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1566476
Posted Wednesday, April 30, 2014 10:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 5:49 AM
Points: 7, Visits: 34
thank you for replying. Let me try, will update later.



Post #1566487
Posted Wednesday, April 30, 2014 11:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:55 PM
Points: 31,278, Visits: 15,736
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1566526
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse