Run CMD in sql job

1

I have a very simple batch file to query out the SQL table and want to run it through SQL job. However it says "The system cannot find the path specified". I ran the batch file itself then it works great and I changed the drive to my local hard drive which is C:/

Batch file
sqlcmd -S COBDATA -d Prod -E -Q "select ADD_FULL from dbo.address where ACTIVE = 1" -s "," -o "C:\Student\SqlToText_%date:~-10,2%%date:~-7,2%_%date:~-4,4%.txt"

In SQL Job,
Type: Operating system(CmdExec)
Run as: SQL Server Agent Service Account
Command: cmd.exe /c "C:\Student\address.bat"

It seems like sql server agent doesn't recognize C drive. when I add cmd without any drive letter then it works. any idea?

2

is this a sql server installed locally or a dedicated server in your network?

3

it is a dedicated server in our network and I installed just management studio on my computer.

4

is your SQL Server Agent service using a service account as the account for SQL Server Agent

5

I do not know. How do I tell it?

6

Basically SQL Server does not know about your C drive unless you share it across the network \mikehjun\c$(bad idea) and give permissions to the SQL Server Agent Service (more bad idea)
When you are using SQL Jobs it thinks C drive of the SQL server itself.

Using SQL Server Configuration, what do you see here under the Log On As

image
image.png1106×164 7.22 KB

7

I solved the problem. The issues is that sql server agent doesn't recognize the drive letter however when I modified batch file to put the text file into the shared folder, then it works.
Thanks all.

1 Like
8

Log on to the Database Server pc with an Administrator account.
Start Microsoft SQL Server Management Studio.
In the left pane, extend SQL Server Agent > Jobs.
Right-click the job you choose to start, and then click Start Job at Step.
On the Start Jobs window, evaluation any messages.