Using sqlcmd in a DOS batch

Question

Using sqlcmd in a DOS batch

James Wagman-335032

SSC Enthusiast

Points: 127
More actions
June 15, 2009 at 10:00 am

#222613

I am trying to run sqlcmd in a batch job every day, and keeping the output (a csv file) from each day in a separate file(same directory!) for each day, maybe with the date embedded in the file name. I don't know enough DOS batch syntax or sqlcmd syntax to know how/if this is possible. TY!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 735698 More actions · Answer 1

I'm not quite sure what you mean. Do you mean that you want to run something in SQL Server and then have the results stored in a separate file or folder/file combination each day?

I'm not sure how you would do this with SQLCMD, though I bet it's possible. It might be easier to script something in VBScript to handle this if you know a little ADO.

James Wagman-335032 SSC Enthusiast Points: 127 More actions · Answer 2

Sorry if not clear. I am running a daily batch job (I don't want to use SqlServer GUI) which calls sqlcmd to execute a sproc. Each day it has an output file, and I'd like each day to put it in a new file, not overwrite or append. Hope that helps. Maybe vbscript or powershell are the best bets? Don't know. TY.

Steve Jones - SSC Editor SSC Guru Points: 735698 More actions · Answer 3

The issue with SQLCMD is passing in the output file name.

You could, for example, calculate the filename in T-SQL, then pass that to SQLCMD as part of an XP-cmdshelll string. Or you could try to futz with the filename in DOS, which isn't easy.

Might be simpler to write to the same file every day and then have a process that renames it. The FileSystemObject in VBScript, which you can easily use to change a file, could be called from the batch file with a "cscript [myscriptfile]" on the next line after your SQlcmd call.

ps. One Orange Chip Points: 29422 More actions · Answer 4

You can take cues from Divya Agrawal's article:

http://www.sqlservercentral.com/articles/SQL+Job/66421/

Pradeep Singh

James Wagman-335032 SSC Enthusiast Points: 127 More actions · Answer 5

James Wagman-335032

SSC Enthusiast

Points: 127

June 15, 2009 at 11:08 am

#1009799

I think using the FSO would be best. Thanks very much!

Randy-574768 Ten Centuries Points: 1385 More actions · Answer 6

IMHO The simplest would be the osql. It has parameters for an output file. If you have the output of your procedure to be what it is you want logged, then you can have it sent to the file you specify in the command line.

Here's an example that makes the filename like LOGmmddyyyy.txt:

osql -E -Q "use AdventureWorks;select count(*) NumberOfEmployees from humanresources.employee" -o c:\maint\Log%date:~4,2%%date:~7,2%%date:~10%.txt

Randy