| Author |
Topic |
|
sorourke1
Starting Member
5 Posts |
 Posted - 2007-06-04 : 07:59:28
|
| 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). I added 'Everyone' with Full Access to the root folder and propagated these permissions to all containing folders. This again 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. I have setup the job owner as SA and the step to 'run as' is empty. 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)." |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-04 : 16:46:58
|
| Sql agent service account needs permission on that file. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
sorourke1
Starting Member
5 Posts |
Posted - 2007-06-04 : 17:36:11
|
| Thanks for the thoughts. I do not have access to execute SPs in the master database, only the database I have access to which is a 'new' database. Any other thoughts? |
|
|
|
sorourke1
Starting Member
5 Posts |
Posted - 2007-06-04 : 17:38:08
|
| Thanks for the advice. I have added 'everyone' with full access to the parent folders and propagated the permissions to the subfolders and files. Any other thoughts? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-06-04 : 18:10:46
|
quote:
Originally posted by sorourke1
Thanks for the thoughts. I do not have access to execute SPs in the master database, only the database I have access to which is a 'new' database. Any other thoughts?
So, you didn't bother trying to execute xp_fileexist?CODO ERGO SUM |
|
|
|
sorourke1
Starting Member
5 Posts |
Posted - 2007-06-04 : 22:40:10
|
| Thanks for the reply. I want to but the DBA will not provide permissions as the master database is 'restricted'. Since the SP you are asking me to exec is in master, I can't use it. I have been told I should be able to use the CmdExec from SQL Server Agent so that is what I have been atempting to use. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-04 : 22:59:32
|
| Is the file on sql server's local disk? If not, have to start sql services with domain account. Local system can't access remote file. |
|
|
|
sorourke1
Starting Member
5 Posts |
Posted - 2007-06-05 : 07:49:55
|
| Thanks, I am attempting to do this on my local machine that has SQL 2K and 2005 installed on it. The file is local to my machine. After I get it working and know it works, I will move it to the development server. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-06-05 : 10:08:14
|
quote:
Originally posted by sorourke1
Thanks for the reply. I want to but the DBA will not provide permissions as the master database is 'restricted'. Since the SP you are asking me to exec is in master, I can't use it. I have been told I should be able to use the CmdExec from SQL Server Agent so that is what I have been atempting to use.
So, you didn't actually try to execute xp_fileexist and just assumed you would not be able to?CODO ERGO SUM |
|
|
|
|
Using the Command Prompt via SQL Server Agent