sqlcmd sql agent job

  • sqlfriend

    SSC Guru

    Points: 52433

    I have a sqlcmd script, it runs fine if I open the command line to run it.

    sqlcmd -S (local)  -d mydb -E -s, -W -i D:\_ssisfiles\scripts\ProductMap.sql | findstr /V /C:"-" /B  > D:\_SSISFiles\Scripts\xtest.csv

    But if I makes it to a sql agent job, the job failed and it got the following error:

    Sqlcmd: Error: Error occurred while opening or operating on file | (Reason: The filename, directory name, or volume label syntax is incorrect).  Process Exit Code 1.  The step failed.

     

    Anything I am missing?

    The sql agent service account has full control of the file directory.

     

    Thanks

  • ZZartin

    SSC-Dedicated

    Points: 30414

    Well login to the server with the service account and see if it can access D:\_SSISFiles\Scripts

    🙂

     

     

  • Sue_H

    SSC Guru

    Points: 90673

    Could be the underscore in the directory name - Try enclosing the paths in double quotes.

    Sue

  • nigel.

    SSChampion

    Points: 11625

    It looks like it's obecting to the pipe symbol ( | ) in that command line.

    You may have to do it in more than 1 step ie. redirect the output from SQLCMD to a file then do the findstr on that file (perhaps?).

  • Ricky Lively

    Say Hey Kid

    Points: 661

    Along with the other suggestions, sometimes putting the logic in a .bat or .cmd and calling that file in the job step will help.

Viewing 5 posts - 1 through 5 (of 5 total)

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