June 23, 2020 at 3:12 pm
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?
June 23, 2020 at 5:01 pm
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.
June 23, 2020 at 8:29 pm
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.
June 23, 2020 at 8:31 pm
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);
June 23, 2020 at 8:59 pm
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.
July 1, 2020 at 5:02 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply