2

I have a script that looks like: 

sqlcmd -S ServerName -d database -E -i "C:\Report1.sql"  -o E:\Folder\SubFolder\FileDate_%date:~-4%_%date:~4,2%.csv -s"," -w 700

The purpose is to automagically run a .sql file and export the results to a .csv file with the Date in the file name.

This script works fine if I run it from the Command Prompt, but when I throw it into a SQL Server Agent Job, it fails with the error message:

Sqlcmd: Error: Error occurred while opening or operating on file E:\Folder\SubFolder\FileDate_%date:~-4%_%date:~4,2%.csv (Reason: The filename, directory name, or volume label syntax is incorrect).  Process Exit Code 1.  The step failed.

Both are being run by Administrator and I'm using SSMS 2014. Also, the Job is Type: Operating system(CmdExec) and Run as: SQL Server Agent Service Account.

Any suggestions or alternate methods are welcome. My assumption is sqlcmd has a problem with the date-getting commands in the file name

Paden S
  • 21
  • 2
  • 1
    Echo out the command that SQL Agent is attempting to execute to a file. This will show you whether there are errors in building the command line. – STLDev Aug 18 '17 at 20:39
  • Hard to tell without seeing the environment. One thought: If `E:` is a network share, and the SQL Server Agent Service Account is not a domain user, and you are in a domain, you can't access domain resources as a local user. – Steven K. Mariner Aug 18 '17 at 20:40
  • sql server instance needs access to "E:\Folder\SubFolder\". – Kashif Qureshi Aug 18 '17 at 21:15
  • what do you mean by "Both are being run by Administrator and I'm using SSMS 2014"? Jobs are always run with the permissions of the SQL Server Agent service account, unless you specify a proxy. – Xingzhou Liu Aug 19 '17 at 01:20
  • @XingzhouLiu I mean the SQL Server Agent Service account is Administrator and I tested the script in Command Prompt as Administrator – Paden S Aug 19 '17 at 08:25
  • If `E:` is a network drive, then you may need to specify the file as `\\COMPUTERNAME\SHARENAME\Folder\SubFolder\FileDate_%date:~-4%_%date:~4,2%.csv` Use `NET USE` to find out the COMPUTERNAME and SHARENAME. – lit Aug 19 '17 at 21:42
  • @lit Appreciate response. I should have clarified that E:// is local. NOT a network share drive. – Paden S Aug 22 '17 at 20:39
  • Try putting `cmd.exe /C` at the beginning of the command to ensure that the variables are processed by the interpreter. If that does not work, you may need to use T-SQL. https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-cmdexec-job-step – lit Aug 23 '17 at 12:52

1 Answers1

0

Likely this is because date and time produce different output according to the language settings associated to the calling process, so to be sure about what is happening you should set first a variable and then using it instead of trying to compose the filename straight for the -o parameter and print its value in a file.

i.e. :

set foutname= "E:\Folder\SubFolder\FileDate_%date:~-4%_%date:~4,2%.csv"
@echo %foutname% > "E:\Folder\SubFolder\mychecklogfile.txt"

sqlcmd -S ServerName -d database -E -i "C:\Report1.sql"  -o "%foutname%" -s"," -w 700
A. Lion
  • 673
  • 5
  • 12