SP execution erroring when running as a job

Question

SP execution erroring when running as a job

Linda W

Grasshopper

Points: 13
More actions
November 3, 2004 at 7:33 am

#86833

One of our developers created the following stored procedure. It runs fine in the query analyzer, however if I schedule it as a job it errors. Of course, the job log doesn't give me enough of a clue to solve.
The job is setup to run under sa, with a Transact SQL Script type, pointed to the correct database, with one step exec deletecrmtoproj go. Any ideas as to why it errors. The history log says: 'The job failed. The Job was invoked by User sa. The last step to run was step 1 (run deletecrmtoproj).'
Thanks - Linda
CREATE PROCEDURE dbo.deletecrmtoproj
***********************************************************************/
AS
BEGIN
INSERT INTO PS_HB_CRMTOPROJ_TM SELECT * FROM PS_HB_CRMTOPROJ_VW with (HOLDLOCK) DELETE PS_HB_CRMTOPROJ_VW with (HOLDLOCK)
END
GO

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

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

Nitin Rana-182398 Say Hey Kid Points: 687 More actions · Answer 1

You are probably having permission issues Either on the table or on the view. When you run it thru QA, your logged on security credentials are supplied to SQL Server to execute the query. However, when the same job is run via SQL Job Agent, SQL Agent's profile ID are the ones that are used to run the query. See who owns the job and try changing the job ownership to someone who is admin on the box.

-Nitin

Linda W Grasshopper Points: 13 More actions · Answer 2

I don't think it's permissions as I've ran it as sa and under the domain admin account that is also setup as an Admin in SQL Server.

Any other ideas?

lholman SSCrazy Points: 2810 More actions · Answer 3

Linda,

when you view the Job History, have you checked the 'Show step details' checkbox on the top right of the Job History dialog? when you select the job step in the history list you should now get a more detailed error message in the 'Errors' box below.

lloyd

Nitin Rana-182398 Say Hey Kid Points: 687 More actions · Answer 4

If you can post error message from the job history, it will help me in making more recommendations.

If you think that it's not a permission issue, try doing a simple step against these table and view in your job: Something like:

Step1:

Select Top 1 * FRom PS_HB_CRMTOPROJ_TM

Step 2:

SELECT Top 1 * FROM PS_HB_CRMTOPROJ_VW

And couple of more steps to add only 1 record to these tables at a time and then check which step failed.

It will be stupid to ask but still making sure that the job is created in the right database??

-Nitin

Linda W Grasshopper Points: 13 More actions · Answer 5

Turns out the view was on a table in another database on another server. I can't even select * from that table without getting a SQLSTATE 42000 error. Of couse, we have linked servers and ODBC System DSNs defined is why it worked in Query Anaylzer. But apparently SQL Agent's rights were what was tripping us up.

We actually just created a DTS package to pump data from the source database and have it running successfully.

Thanks for the help!

Linda