October 1, 2001 at 11:52 am
I need to email a list of jobs that failed, here is the code that I am using:
select sj.name from sysjobs as sj inner join sysjobservers as sjs on sj.job_id = sjs.job_id where sjs.last_run_outcome = '0'
I am writing this list to a file but it keeps appending to the file instead of overwriting, is it possible to overwrite???
Steve Johnson
Steve Johnson
October 1, 2001 at 12:21 pm
Sure. Or just delete (kill) the file if it exists before you export.
Andy
October 1, 2001 at 12:40 pm
You wouldn't happen to know the syntax to (kill) a txt file would you???
Steve Johnson
Edited by - Steve Johnson on 10/01/2001 12:40:49 PM
Steve Johnson
October 2, 2001 at 9:46 am
there are a bunch of ways. is this a scheduled job? Add a step before that uses a CommandExec and a "del filename". you could also add a step to the DTS package that runs a t-sql command (xp_cmdshell) or a activeX script (using filesystemobject)
Steve Jones
October 9, 2001 at 10:15 am
exec
master.dbo.xp_cmdshell 'del C:\filename.txt'
That should do the trick, test it out in the query analyzer.
October 9, 2001 at 11:17 am
Remember to double quote your path & filename, it will break if embedded spaces.
Andy
October 17, 2001 at 11:08 pm
Hi I was looking for the same solution and have tried the exec master.dbo.xp_cmdshell "del c:\dts_diag\sp_diagnostic_PO_13.xls" but when I run the PKG again it says it can't find the .xls file. I then have to go in to the transform data task and recreate the .xls file(or table as DTS calls it) before it will run again. Is there any other solution? All I'm doing is running a stored procedure nightly and would like to send the results in a .xls in the mail to the business users without appending...nightly
October 18, 2001 at 9:31 am
If you are trying to resend the same XLS file, you probably want to "drop " the table and then recreate it using 2 T-SQL tasks.
I do this quite a bit and have an article in the works on it. Basically, when I build the Transform Data Task, I use the "create" button on the destination tab. Cut and paste this code into a T-SQL task that points to the XLS connection. Then add another T-SQL task that does a "Drop table" instead of the "create table". You can cut and paste the first line from the create statement.
Then I have:
Drop table
->Success->Create Table
->Failure->handle this
Create Table
-> Success ->run transform
-> Failure -> handle this
Sometimes I have had problems doing an xp_sendmail from within the DTS package, so I use a second step in the job to run xp_sendmail.
Steve Jones
October 23, 2001 at 5:18 pm
Steve, worked great! Thanks for the info.
Greg
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply