SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
eseeweb
eseeweb
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 60
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.
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42538 Visits: 19839
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218355 Visits: 41995
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63634 Visits: 17966
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.

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)
eseeweb
eseeweb
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 60
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63634 Visits: 17966
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.

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)
eseeweb
eseeweb
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 60
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63634 Visits: 17966
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.

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)
eseeweb
eseeweb
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 60
thank you for replying. Let me try, will update later.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148490 Visits: 19444
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search