Update code works in manual stored procedure but not when run as SQL Server Agent Job

Question

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.

• Shanky: 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.
• RLF: 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.
• unholymackerel: UPDATE ap SET ap.HQCOControllerEmail = ISNULL(cl.ControllerEmails,'') FROM zzz_APApprovalReview ap INNER JOIN zzv_HQCOControllerList cl ON ap.APCo=cl.HQCo
• unholymackerel: the view zzv_HQCOControllerList has public = select permissions
• unholymackerel: added public - insert, select, delete, etc to the target table and the update still does not occur when run from the job

Answer 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.

Answer 2

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/