Error running scheduled job in Enterprise Manager

Question

Post reply

Error running scheduled job in Enterprise Manager

Curtis Gibson

Old Hand

Points: 329
More actions
May 10, 2005 at 2:45 pm

#65820

I've created a job that is exporting data from a view to a text file. When I run the job manually, it runs fine. However when I schedule it, it fails to run and no error is given. I've looked at the job history and the windows event viewer but both just say that it failed with no explaination as to why. Anyone have any ideas on how to trouble shoot this.
TIA - Curtis

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

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

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 1

Check the SQL Agent Error Log.

Make sure the database is defined properly in the job step.

Secondly never use direct SQL Commands (which refers to objects) in the jobs step. Create a SP and place the query in it and call the stored procedure. (This is just a better way to manage the objects)

Kindest Regards,

Amit Lohia

Divina Escaño Grasshopper Points: 14 More actions · Answer 2

Where did you create the job? If you have created it directly in the SQL Server Agent then try creating the job at the Database Maintenance for it to run as scheduled. Any changes made in the plan should also be done at the Database Maintenance to take effect, not at the SQL Server Agent.

Andrew Sturt SSC Enthusiast Points: 107 More actions · Answer 3

I'd be somewhat suprised if this is your problem, but seeing as I have just had exactly the same issue, I figured I'd go ahead and answer your question anyhow.

If you are using xp_cmdshell, you might try appending it with the optional ", no_output." That, however, was not my problem.

I had xp_cmdshell inside another sproc. My wrapper sproc wrote a history of xp_cmdshell calls to a table in our database. One column (field, variable, attribute - take your pick) was too short for the name of the application calling xp_cmdshell, which turned out to be the job (its name was "SQLAgent - TSQL JobStep (Job 0x06BD091B66F8604FBAB6BCDCCEE76407 : Step 1)"). Once I altered the my text file was created as I had intended but the job showed as "failed."

I hope that helps,

Andj

swede SSCommitted Points: 1773 More actions · Answer 4

sounds like its a permission problem...

a few things to look at:

1) when you export to the text file are you using UNC names or drive letters?

2) does the job owner have permissions to the source and destination

3) when manually run it, I assume you are running from the client, when you schedule a job, its running from the server

Curtis Gibson Old Hand Points: 329 More actions · Answer 5

Permissions are fine.

The destination for the export is another server and I am using the unc name i.e. \\servername\folder\filename I changed the destination for the export to the local D partition this morning for testing and it runs fine. Is there a way to get it to work with the unc naming convention as we can't used mapped drives.

Curtis

swede SSCommitted Points: 1773 More actions · Answer 6

uncname should work fine...

verify that \\servername\folder\filename is a valid path accessable by owner of the job and sql server agent account.

you may want try mapping to \\servername\folder\filename just to verify its a valid path (ie. the folder may not have been setup as shared, or there may be the same folder name on multiple drives on the same server)