Update skipping records when run in SQL Server Agent job???

Question

Update skipping records when run in SQL Server Agent job???

Mia G

Old Hand

Points: 374
More actions
July 21, 2009 at 2:17 pm

#73244

Is there anything in scheduling a job to run a stored procedure that could cause it to not work properly?
I've inherited a stored procedure from another developer that needs some changes. It currently runs weekly in a SQL Server agent job. Each week the job shows as completing successfully. However, before making my updates to the procedures I noticed that the results are not what I expected.
It is supposed to update all accounts with a flag that says whether they are active. After it runs all records should have this field marked as either Y or N. When I ran it in our testing environment it did this successfully. However, I'm getting some blank records in the production job that ran over the weekend. Here's something of what the stored procedure does...
[font="Courier New"]select acctNum as acno into #inactiveAccts
from acctMainTbl
where acctNum not in (select acctNum from deceasedAcctTbl)
and acctNum not in (select acctNum from oneYrActList)
update activeField
set activeField = (case when acctNum is not null then 'Y' else 'N' end)
from acctMainTbl left outer join #inactiveAccts on acctNum=acno[/font]
There's a lot of extra logic where the 'oneYrActList' is to determine who these accounts are. But, my assumption is that regardless of whether the code the developer used to determine whether the accounts are inactive is correct... every account should end up with either a Y or an N after the procedure runs.
I'm sure I must be overlooking something. Either that or maybe another job is conflicting??? I'm not sure how. The only other job running around that time is a job that is reading data from the 'acctMainTbl' and the queries are using the NOLOCK server hint.
BTW - Yes, I've factored in data entry of new accounts for the blank records. However, some of the blank accounts are ones that go back a few years.
Any help is appreciated.
thanks

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

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

arun.sas SSChampion Points: 11831 More actions · Answer 1

Mia G (7/21/2009)
select acctNum as acno into #inactiveAccts
from acctMainTbl
where acctNum not in (select acctNum from deceasedAcctTbl)
and acctNum not in (select acctNum from oneYrActList)
update activeField
set activeField = (case when acctNum is not null then 'Y' else 'N' end)
from acctMainTbl left outer join #inactiveAccts on acctNum=acno

Hi,

Any specific reason you update the records in the temp table and again update the main table by the reference of the temp table.

You try like

update acctMainTbl

set activeField = (case when (acctNum is not null) or (acctNum '') then 'Y' else 'N' end)

where acctNum not in (select acctNum from deceasedAcctTbl)

and acctNum not in (select acctNum from oneYrActList)

And how you determine value of the deceasedAcctTbl and oneYrActList is may the criteria for the mismatch of the status.

Mia G Old Hand Points: 374 More actions · Answer 2

No, I'm not sure why the original developer chose to do it that way. Perhaps for simplicity (personal sanity) since the logic they used to determine the oneYrActList is quite involved.

However, even in the current structure when it comes to the update statement either an account is in the list or it's not. So, it should be marked with something once it is run.

I might try putting it together as suggested to see if it makes a difference. However, since it seems to work when I'm testing (just not in the scheduled production job) I don't know if I will be able to validate the change as needed. I'll think on that some more.

Thanks.

sgmunson SSC Guru Points: 110640 More actions · Answer 3

When you eliminate the impossible, whatever remains, however improbable, must be true. I suspect that your left outer join is becoming an INNER JOIN by virtue of a where clause that specifies a field in the table you left joined to. Although you didn't provide the actual query, you did state that there was other logic that was significant, and it seems likely that something in the UPDATE part has a where clause item looking at a field in the joined table, which SQL Server will turn into an INNER JOIN. One quick way to identify this is to query for all records that have blank values before this job runs, and then do the same after the job runs, and see if there's any difference. It's possible you may find your answer that way.

Steve

(aka smunson)

:-):-):-)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Mia G Old Hand Points: 374 More actions · Answer 4

I actually just went ahead and rewrote the query anyway for some performance improvements. Then I was planning to take some steps similar to what you suggested the next time it runs. So, between doing that and the changes I've made, I'm hopeful the situation will be resolved.

Thanks for the input!