1

I have a stored procedure that deletes all records from an existing table, and then inserts new records. After the insert I have several update operations that change the value of one field.

If I run the SQL Server Agent job, the update does not happen. If I select and execute the update code manually, the update occurs.

If I execute the entire stored procedure manually, the update occurs.

No errors are thrown; everything 'works' but the update does not occur when I kick off the SQL Server job.

  • It would add value to question if you can please add the code which you are using for delete/update in question. Replace table names by dummy name before posting. – Shanky Jan 18 '15 at 16:23
  • 2
    Make sure that the login running the job has the needed rights. When you run the job manually it runs with your credentials, likely not the same as defined for the job. – RLF Jan 18 '15 at 16:50
  • UPDATE ap SET ap.HQCOControllerEmail = ISNULL(cl.ControllerEmails,'') FROM zzz_APApprovalReview ap INNER JOIN zzv_HQCOControllerList cl ON ap.APCo=cl.HQCo – unholymackerel Jan 18 '15 at 17:02
  • the view zzv_HQCOControllerList has public = select permissions – unholymackerel Jan 18 '15 at 17:06
  • added public - insert, select, delete, etc to the target table and the update still does not occur when run from the job – unholymackerel Jan 18 '15 at 17:07
1

I resolved the issue - but do not understand it.

The system has a View corresponding to each Table. One of the Views was used in my FOR XML PATH view.

I substituted the no-permission Table for the View, and it works.

The View has Public SELECT permissions. The Table has no named permissions.

I think the SQL Server Agent Job should be able to SELECT from the View because of the public permission, but it cannot.

0

If i am getting you right and what i also witnessed when the job did not ran an update code because of the limit of the text size with hold by sql server agent job:

Therefore you can try something like limiting the amount of data returned via sql agent job as The default TEXTSIZE for SQL Agent is 512.

You can specify the text size as per output you get after a success manual run.

Refer to below link for more info:

http://www.mssqltips.com/sqlservertip/1398/limit-amount-of-data-returned-with-the-sql-server-textsize-command/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.