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 |
|
rayne
Starting Member
4 Posts |
 Posted - 2010-01-04 : 12:10:50
|
| Hi there,I have an sql script which I typically run manually against a database within a new query window in SQL Server Management Studio (SQL Server 2005). I run it with query results set to "Results to File", to text format (.txt). This works without a problem, and sure enough the text file is created with my results.Now I would like to run this script (with it's output to a text file) on a daily basis. I have done some research and have read that this may be a type of statement which could be used:sqlcmd -S TESTSERVER -d TestDB -i C:\testscript.sql -o C:\ReportStatusLog.txtThis sql cmd in command prompt appears to be working, and the output to text works fine. What I wanted to know is how to add the date to the text file name, so that I am able to keep each daily text file, rather than have it overwritten each day. And also, how to create a batch file for this, so that I can run this daily with the Windows Scheduler.Any help would be greatly appreciated! |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-01-05 : 03:48:41
|
| Your .bat-file should look something like this:sqlcmd -S TESTSERVER -d TestDB -i C:\testscript.sql -o C:\ReportStatusLog_%date%.txt- Lumbagohttp://xkcd.com/327/ |
 |
|
|
rayne
Starting Member
4 Posts |
Posted - 2010-01-05 : 04:30:43
|
| Thanks Lumbago, however I am now getting this error (appears to be syntax maybe?):Sqlcmd: ':\ReportStatusLog_Tue 01/05/2010.txt': Unexpected argument. Enter '-?'for help.If I run the command without the "_%date%.txt" at the end of the file name, it works ok, but then I do not have the date.. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-01-05 : 05:13:31
|
| Hm, seems like your date format is quite different from mine. Try this:sqlcmd -S TESTSERVER -d TestDB -i C:\testscript.sql -o C:\ReportStatusLog_%date:~10,4%%date:~4,2%%date:~7,2%.txtShould look a little better I hope. If that doesn't work either you must fiddle with the %date:~10,4% etc which is basically the same as a substring in sql.- Lumbagohttp://xkcd.com/327/ |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-01-05 : 05:14:43
|
| The error you are getting is due to the space in the date:Tue 01/05/2010- Lumbagohttp://xkcd.com/327/ |
|
|
|
rayne
Starting Member
4 Posts |
Posted - 2010-01-05 : 11:19:49
|
| Thanks this worked!sqlcmd -S TESTSERVER -d TestDB -i C:\testscript.sql -o C:\ReportStatusLog_%date:~10,4%%date:~4,2%%date:~7,2%.txt |
|
|
|
|
|
|
|
|
Help: Running daily scheduled script with output