April 29, 2014 at 4:35 pm
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.
April 29, 2014 at 4:49 pm
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.
April 29, 2014 at 4:54 pm
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
Change is inevitable... Change for the better is not.
April 30, 2014 at 8:03 am
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/
April 30, 2014 at 9:03 am
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.
April 30, 2014 at 9:09 am
eseeweb (4/30/2014)
hi to allI 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/
April 30, 2014 at 9:51 am
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.
April 30, 2014 at 10:00 am
eseeweb (4/30/2014)
HiI 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...
_______________________________________________________________
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/
April 30, 2014 at 10:13 am
thank you for replying. Let me try, will update later.
April 30, 2014 at 11:41 am
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.
April 30, 2014 at 12:08 pm
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.
April 30, 2014 at 12:57 pm
eseeweb (4/30/2014)
hiI 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
Change is inevitable... Change for the better is not.
April 30, 2014 at 1:39 pm
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
May 8, 2014 at 7:48 am
eseeweb (4/30/2014)
hiI 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