SQL Job doesn't seem to run the following procedure even though the Job History shows it ran: Is there something else I can try to ensure this gets ran at 8:15 a.m. every morning?
DECLARE @nbrOfRows int = 0, @nbrOfMissingID int = 0; SET @nbrOfRows = ( select COUNT(*) from banking.prod BP where BP.SSN <> RIGHT(BP.BankAcct, 9) and BA_ID = 0 AND (BP.BankAcct like '3330%' OR BP.BankAcct like '2207%') ) IF (@nbrOfRows > 1 ) Update Banking.prod SET SSN = RIGHT(BP.bankacct, 9) From Banking.Prod BP where BP.SSN <> RIGHT(BP.BankAcct, 9) and BA_ID = 0 AND (BP.BankAcct like '3330%' OR BP.BankAcct like '2207%') SET @nbrOfMissingID = (select Count(*) from Banking.Prod BP JOIN Banking.contact BC ON BP.SSN = BC.SSN where BP.BA_ID = 0) IF(@nbrOfMissingID > 1) UPDATE Banking.Prod SET BA_ID = BC.ContactID, SSN_Status = 'G' FROM banking.contact BC JOIN Banking.prod BP ON BP.SSN = BC.SSN where BP.BA_ID = 0 -- Delete From Review Table Trans that have BA_ID DELETE BR from banking.review BR Join banking.prod BP On BR.Payroll_ID = BP.SSN where BP.BA_ID <> 0 COMMIT TRAN
Here is an image of what the job settings look like:
As you can see here, using the same connection/login I provide - I have permission to run this and it will run when I fire this up in the query window but for some reason the job won't run the query update. Again, I did the count and there were more than 1 records that fit the requirement.
Is there anything more I can do to ensure this gets ran? Because even though I ran it already - as records progress this may happen again.
Some of the actual results coming up was 1 and thus the coding changed needed to be:
IF(@nbrOfMissingID > 0)