Append output of sqlcmd to file using Job Agent

Question

After some research I found the solution to append the output of a sqlcmd command to a csv file. Everything is fine when using the Command Shell:

C:\temp>sqlcmd -S <SERVER> -d <DATABASE> -E -Q "SELECT [something] FROM [TableX] WHERE [columnY] IS NOT NULL;" -W -h-1 -s";" >>"<FULL_PATH_TO_CSV-FILE>"

I want to schedule this stuff by using SQL Server Job Agent (with same account as with console) so I add a Job (type CmdExec). Job execution fails with the following error (unexpected argument):

Meldung Ausgeführt als Benutzer: ''[ACCOUNT]''. Sqlcmd: '>>''[FULL_PATH_TO_CSV-FILE]'': Unerwartetes Argument. Geben Sie '-?' ein, um die Hilfe anzuzeigen. Prozessexitcode 1. Fehler bei Schritt.

My workaround works fine: remove >>"<FULL_PATH_TO_CSV-FILE>" and use the output file of the step. But: I want to use the output file for logging purposes - not for the data output ...

The question is: why does the ">>" command does not work within a SQL Server Agent job?

I don't want to simply redirect the output. I know that sqlcmd provides the parameter -o for that. In this case an existing file would be overwritten. That's not what I want.

Unfortunatelly I do not have the reputation to post the screenshots so far.

Thanks you very much in advance!

Best regards, D.C.

valex · Accepted Answer · 2015-09-24 13:46:27Z

1

I think the issue here that SQL server agent creates process "sqlcmd" and sends this whole line to it as a parameters. So "sqlcmd" doesn't know what this means - ">>..." and returns with error.

To use this (">>") Command Shell syntax you should call it in the SQL agent. So try:

cmd.exe /c "sqlcmd -S -d -E -Q "SELECT [something] FROM [TableX] WHERE [columnY] IS NOT NULL;" -W -h-1 -s";" >>"""

answered Sep 24, 2015 at 13:46

valex

24.2k7 gold badges47 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

D.C. Over a year ago

This solved it! Thank you very much! Unfortunately there is a new problem: no special chars (although I added -f 65001) :-(

Collectives™ on Stack Overflow

Append output of sqlcmd to file using Job Agent

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related