SQL Server Job

Question

Post reply

SQL Server Job

nathanfields

Old Hand

Points: 345
More actions
June 23, 2020 at 3:12 pm

#3764809

I have a sql server Job that runs a stored procedure. When I run the stored procedure manually it merges the data perfectly. When the Job runs the stored procedure it nulls out two columns instead of merging.
I have looked at permissions and even gave
NT AUTHORITY\NETWORK SERVICE permissions to all levels and I have never had to do that before and even that did not work. I changed back to sa to run the job and that did not work either.
Any ideas?

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

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

Ken McKelvey SSCoach Points: 18865 More actions · Answer 1

Does the stored procedure insert/update/delete tables from more than one database?

If if does then you will need to look at the login permissions in each database.

This reply was modified 4 years, 6 months ago by Ken McKelvey.
This reply was modified 4 years, 6 months ago by Ken McKelvey.

nathanfields Old Hand Points: 345 More actions · Answer 2

This is in one database and when run manually the stored procedure works correctly and populates three fields in the merge statement. When you run it through the job it populates only one field and nulls out two others coming from a view.

nathanfields Old Hand Points: 345 More actions · Answer 3

This is the code in the stored procedure

Merge into FINANCE_CURRENT_GROSS_PROFIT as tab1

using

(

SELECT

JCCo, [Contract], ContractAmt, ProjectedGP, AcctGP

FROM dbo.FINANCE_CURRENT_GP

) as tab2

on tab1.JCCo=tab2.JCCo and tab1.Contract=tab2.Contract

when matched then update set tab1.ContractAmt=tab2.ContractAmt,tab1.ProjectedGP=tab2.ProjectedGP,tab1.AcctGP=tab2.AcctGP

when not matched then

insert values(tab2.JCCo,tab2.Contract,tab2.ContractAmt,tab2.ProjectedGP,tab2.AcctGP);

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

nathanfields wrote:

This is in one database and when run manually the stored procedure works correctly and populates three fields in the merge statement. When you run it through the job it populates only one field and nulls out two others coming from a view.

It is difficult to guess without seeing the code of the VIEW, but could it be a difference in SET options for the sessions? Management Studio uses different SET options than SQL Server Agent does by default.

nathanfields Old Hand Points: 345 More actions · Answer 5

Finally was able to spend the time needed to figure this out. There was a custom table that was added to the view the merge statement was pulling from. That custom table did not have any permissions assigned and that is why the merge statement was nullifying to fields in the table. Job now runs the stored procedure the same as running it manually.