Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fireballbrady
Starting Member
6 Posts |
 Posted - 2011-11-11 : 12:43:53
|
| Hey Guys,I currently have job set to run daily and to take the output from the query into a txt file on the server. The problem is, I need to be able to run this daily and have it drop a new file into that directory.Right now all I have been able to accomplish is to have the query txt file over right the previous days txt file or append to it. I don't want to cross contaminate the data. I need the files to stay seperate for each day!.Any ideas would be greatly appreciated.can't stop the signalfireballbrady |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-11 : 12:45:30
|
| How are you creating the file.Just change the name - or rename the previous days file.You would probably want to add the date to the filename.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
fireballbrady
Starting Member
6 Posts |
Posted - 2011-11-11 : 13:09:22
|
| I am trying to set it up so that the system will just run the query every morning. And store the file in the folder with out having to change or touch the files created every day.I currently have the system set to output the file to the folder each time the agent job runs.In the advanced tab of the job agent i have the T-sql set to output file to the location desired. The only other options are to Append output to existing, log to table, and include step output in history.Is there a way to do this and have it create a new file with out over writing the old?fireballbrady |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-11 : 13:21:04
|
| You have the output file in a step writing to file x:\mydir\mifile.txtAdd another step after thisdeclare @sql varchar(max)select @sql = 'ren x:\mydir\mifile.txt mifile_' + convert(varchar(8),getdate(),112) + '.txt'exec master..xp_cmdshell @sql==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
fireballbrady
Starting Member
6 Posts |
Posted - 2011-11-11 : 19:32:48
|
| thanks,I logged into my server and opened up the job agent and added a step just like you said.And got the error "Procedure expects parameter 'command_string' of type 'varchar'. [SQLSTATE 42000](Error 214). I looked it up and found that it was failing because of the declare @sql varchar(max)I changed @sql varchar(max) to (1000) and ran it and it worked!Thanks again, your wisdom saved me much grief!fireballbrady |
|
|
|
|
|
|
SQL Server Agent file output.