Using the Command Prompt vis SQL Server Agent

Question

Using the Command Prompt vis SQL Server Agent

Shawn O

SSC Rookie

Points: 30
More actions
June 3, 2007 at 10:53 am

#117334

I need to verify a csv file exists before I call a stored procedure to complete a bulk insert. The first step in the SP will be to verify the file exists (that particular "Job" step did not fail). I have only one step in the job that I have set the type as "Operating System (CmdExec)" and the Command is "Dir C:\NAFCDM\Data\Shipments\CooperShipments.csv". When I execute the job manually to verify the proper operation I receive the error listed below. I know the path and file name are correct as I originally typed in the UNC, when it failed I copied it from the Address bar. I attempted to move the file to the root location without success. I attempted to use another file. I have attempted to rename the file. The DBAs will not allow me to use the SP_cmdshell stored procedure (that was my first thought). Can someone Please point me in a direction to fix this problem?
"The process could not be created for step 1 of job 0x71D51027F920A140A2913234DB7FF509
(reason: The system cannot find the file specified). The step failed.
The job failed
. The Job was invoked by User sa. The last step to run was step 1 (Verify the File's Existance)."

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

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

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 1

check out that the service account of your sql agent has the needed authority to read the folder where your file(s) reside.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Shawn O SSC Rookie Points: 30 More actions · Answer 2

Thanks, I appreciate the assistance. In an attempt to test your theory, I added 'Everyone' with Full Access to the root folder and propagated these permissions to all containing folders. This did not allow the job to run. I changed the statment to "REN C:\NAFCDM\Shipments\Shipment.csv Shipment1.csv". I continue to get the same error. Any other thoughts?

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 3

Is sa jobowner ? if not, make sa jobowner.

(in your first post, job was launched by sa)

Did you specify an "execute as " at jobstep level ? (let's hope not)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Shawn O SSC Rookie Points: 30 More actions · Answer 4

Thanks, I had the same thought. The owner is sa and the step has the 'execute as' empty.

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 5

I'm pritty much out of options now

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Shawn O SSC Rookie Points: 30 More actions · Answer 6

Thanks, I appreciate the help anyway...I will look for answers elsewhere unless someone wlse has an idea.

stickneym SSC Veteran Points: 224 More actions · Answer 7

Have you tried

EXEC master..xp_fileexist 'c:\file.txt'

stickneym SSC Veteran Points: 224 More actions · Answer 8

sorry, should have given you more than that...

declare @rc int

EXEC master..xp_fileexist 'c:\file.txt', @rc OUTPUT

--PRINT 'EXECUTE FILE EXIST - RCODE: ' + CAST(@RC AS VARCHAR(12)) -- FOR TESTING IN QA

IF @rc = 0

BEGIN

-- END JOB WITH STATUS 16 - there should always be a file available

RAISERROR ('FILE WAS NOT AVAILABLE - JOB ENDED.', 16, 1) -- do not removee !!

RETURN

END

Shawn O SSC Rookie Points: 30 More actions · Answer 9

OK, Thanks! It appears I can use that. What table does the results end up in? And I still neet to move and then rename the file. How can I do that?

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 10

How about using a DTS package? It should be able to handle existence checking, bulk insert, and file moving with no problems.

Shawn O SSC Rookie Points: 30 More actions · Answer 11

I had the same thought. I am trying to avoid that as even though this is a new database, they will be converting to 2005 in August. I attempted to point them toward 2005 in the first place so we will not have to redo work. It was their choice. So I am attempting to not use DTS packages as much as possible.