Trouble accessing file from job.

[MarkButler]

I have a SQL job which the first step executes a 'extract.bat' script. Within this script is a copy from another physical server as follows:

copy \\serverb\temp\xxx.txt e:\xxx.txt

It seems that it never finds the file to copy. I placed an "if exist echo nofile" in front of the copy and it does not find the file. Yet if I execute it from the command.com prompt it works fine.

Is this a limitation of the command execute that it can't access anything but physical drives?

If so is there an easy workaround?

 

[nigelrivett]

It can access anything available to the profile under which it is executing - probably the sql server logon account.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[dmcmunn]

If your account is a member of the sysadmin role, you can try this...

EXEC master..xp_cmdshell 'fully_qualified_server_local_path_to\extract.bat'

 

[MarkButler]

I can get to the batch script which is on a local drive without a problem. I create a log within the batch script used for debugging. The log is being created and states that the file on the other server is not found.

I have tried doing it both ways, using the xp_cmdshell and the job command script with the same results.

 

[dmcmunn]

When logging, add a command to concatenate the value of the "USERNAME" environment variable.

Add something like this to your logging...
set USERNAME >> logfile.out

This will give you an idea of what network credentials are being used to determine access permissions to the "temp" share point on \\serverb.

You might also attempt to create a separate share point, if you don't want to alter the "temp", to allow whatever your SQL Server login account's credentials are.

Best,
dm