Agent Job won't run - HELP!

Question

Post reply

Agent Job won't run - HELP!

russpang

SSC Enthusiast

Points: 112
More actions
January 1, 2004 at 1:50 pm

#59928

I'm running SQL 2k on Win2k Server. I have a client connecting remotely via Enterprise Manager where he is running a DTS package which imports his local FoxPro tables into the server's SQL tables for use with the online store.
He is logged onto Enterprise Manager with dbo permissions for his database.
He can execute the package fine. BUT, when scheduling the package so it will update the inventory daily, the SQL Server Agent Job fails instantly giving no reason for failure.
I tried to run the job directly from the server as sa but it fails also - but probably because I'm not on his machine.
I thought perhaps it was a permission issue but why then would it allow the direct execute but not the scheduled job?
Any ideas? Do I need to provide more info? I really need help with this.
Thanks,
Russ

Viewing 7 posts - 1 through 6 (of 6 total)

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

wz700 SSCertifiable Points: 7133 More actions · Answer 1

It sounds like a permission. Run it directly from server fail proves that.

Server might not have right back to local poxpro table.

Amit Jethva SSCarpal Tunnel Points: 4707 More actions · Answer 2

SQL Agent runs on server machine.

So it looks for thos files to be available locally. which it does not and hence job fails.

Solution.

A. make the FoxPro files available on server

or

B. Share the Dirctory containing FoxPro files on client. Map it on server and Use ODBC to connect to that Foxpro Files.

-- Amit

"There is no 'patch' for stupidity."

Download the Updated SQL Server 2005 Books Online.

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

Can you open the DTS-package with EM on the server itself ?

If not, MDAC is your problem.

Only create an maintain DTS-packages at the server itself, not using a client to connect to it, because DTS stores CLIENT-objects like connection-info, ..

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

ThomasH SSCertifiable Points: 7437 More actions · Answer 4

Is there any error message in the "details" of the Job history?

Is there a chance the Foxpro data has been accessed within a minute prior to the Job run?

Once you understand the BITs, all the pieces come together

russpang SSC Enthusiast Points: 112 More actions · Answer 5

Here is an error I found on the job:

"Non-SysAdmins have been denied permission to run CmdExec job steps. The step failed."

So, it appears the user can't execute an Agent Job. And I can't access the user's FoxPro files from the server. Dang this is weird, there must be a way around this.

When viewing the Job's properties via EM on the client's machine, the "owner" box is greyed out with his username in it (can't change it)

When logged on the server I can change the permission to admin, but then when trying to execute it, I, of course, get a "server not found" error.

Any thoughts?

Russ

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

Make sa jobowner.

Your sql-agent-service-account needs to be able to access all needed files, db, ...

You could also open the commandline setting a proxy-account (check BOL), but I don't know if this is what you want when having one problem.

If you log into the server as sa, the dts-package should work fine, if not, something is wrong. Mostly the execution error will point you the right way.