none
sqlcmd The filename, directory name, or volume label syntax is incorrect RRS feed

  • Question

  • 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?

    Thanks


    SQLFriend

    Monday, August 12, 2019 3:51 PM

Answers

  • The permission seems no problem.

    I rewrite my sqlcmd in a different way, then it works.

    sqlcmd -S (local)  -d mydb -E -s, -W -i D:\_ssisfiles\scripts\productMap.sql -o D:\_SSISFiles\Scripts\xtest.csv  -h -1



    SQLFriend

    Tuesday, August 13, 2019 3:19 PM

All replies

  • Seems the SQL Server-Agent service account don't have read permissions on the folder/file.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, August 12, 2019 4:14 PM
  • Thanks,

    The sql service account has full control of that directory. Read, write, execute.

    So it does not appear it is that problem.


    SQLFriend

    Monday, August 12, 2019 4:22 PM
  • I believe that when Agent starts the job it does not really have access to the command shell, but it just starts the command. If you pay attention to the error messag it is the pipe character it barfs at.

    Stick the command into a BAT file, that works. I have tested.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, August 12, 2019 9:24 PM
  • Hi SPFriend,

    Check whether below link useful. https://www.sqlshack.com/faq-and-examples-about-the-sql-server-agent/

    Best regards,
    Cathy Ji



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, August 13, 2019 7:47 AM
  • The permission seems no problem.

    I rewrite my sqlcmd in a different way, then it works.

    sqlcmd -S (local)  -d mydb -E -s, -W -i D:\_ssisfiles\scripts\productMap.sql -o D:\_SSISFiles\Scripts\xtest.csv  -h -1



    SQLFriend

    Tuesday, August 13, 2019 3:19 PM
  • I rewrite my sqlcmd in a different way, then it works.

    sqlcmd -S (local)  -d mydb -E -s, -W -i D:\_ssisfiles\scripts\productMap.sql -o D:\_SSISFiles\Scripts\xtest.csv  -h -1



    SQLFriend

    Hi SQLFriend,

    I am so glad to hear that you have resolved your issue. Please mark your solution as answer. This can be beneficial to other community members reading the thread.

    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by msloy Wednesday, August 14, 2019 5:46 PM
    • Unmarked as answer by msloy Wednesday, August 14, 2019 5:46 PM
    Wednesday, August 14, 2019 1:18 AM