Run batch file from sql server into into share drive ???

[inarobis]

Hello Guys,

I have a problem with a job moving and zipping file to share drive


What does my Batch?


1) Copy backup device (initially in D:/) into
//SERVER/SHARE/Archived_Backups/


2) Create new folder in //SERVER/SHARE/Archived_Backups/


3) Move the DEVICE into this new folder


4) Zip the DEVICE and named it today.zip


if I run it manually the batch file works perfectly, But if I launch it
with sql server (what I would like to perform) this step does not run
perfectly.


What does this job?


1) run the batch
Step 1
DECLARE @cmd sysname, @var sysname
SET @var = 'start D:\Backups\MyBacth.bat'
EXEC master..xp_cmdshell @var


2) Overwrite the DEVICE


3) Verify DEVICE integrity


I have already set privileges for sqlservice in to this share


What do i need to do??? Any Suggestions?


Thanks in advance


Ina

 

[TWillard]

1) Can you define the error message or what happens when it runs in sql server jobs?

2) You stated that you changed the permissions for sqlservice for this share. Are you aware that there are two services, MSSQLSERVER and SQLSERVERAGENT? Go to control pannel > administrative tools > services. Find these services and post who is the "Log On As" user for each of these services.

3) Go to your sql server job properties. Who is the owner of this job?

 

[inarobis]

First of all Thank you very much.

When I launch from EM/Management/SQl Agent / Jobs
my job I have no error message from this step (run batch file) but when I run it from Query Analyser I have that:
Output = Null

I have allowed full permission for this share to sqlservice and both service MSSQLSERVER and SQLSERVERAGENT are logged on as sqlservice

the owner of the job is sa.

If I do that on the F: drive (local drive) it works perfectly. As I do not have enough space, I do need to perform all jobs in an external drive.

What to do?

 

[Catadmin]

Does the SA account work under a local administrator's account or a domain user's account?

If the former, that might be your problem. Try getting a plain vanilla domain user account (NOT a domain Admin account) and having SA log in with this. Then you should be able to access your share.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[TWillard]

You could also change the job ownership from 'sa' to 'sqlservice'. (I will assume that this is a domain acount and that you have or WILL create a sql login associated with this domain account using windows nt authentication.) This user is running both MSSQLSERVER and SQLSERVERAGENT and has permissions to the share.

 

[inarobis]

Thanks I will check that

Concerning the sqlservice user I try to change the job's ownership but no way it doesn't work I will be doing more test as soon.

Thanks a bunch

ina