2

I have very simple stored procedure

CREATE PROCEDURE FillMissingBirthDateFromMemberNumber
AS
BEGIN
    UPDATE Members
    SET BirthDate =
        CASE 
            WHEN CONVERT(DATE, SUBSTRING(m.MemberNumber, 0, 7), 12) > '2030-01-01' 
            THEN dbo.fn_LocalTimeToUTC(DATEADD(YEAR, -100, CONVERT(DATE, SUBSTRING(m.MemberNumber, 0, 7), 12)))
            ELSE dbo.fn_LocalTimeToUTC(CONVERT(DATE, SUBSTRING(m.MemberNumber, 0, 7), 12))
        END 
    FROM Members m
    WHERE 
        m.BirthDate IS NULL AND
        m.MemberNumber IS NOT NULL AND
        m.MemberStatus IN (1,2)
END

that when executed in a query window, performs exactly as promised. It fills out missing birth dates based on the first six chars of the member number (with some caveats, as the CASE WHEN indicates).

Now, I have created a SQL job that simply EXECs this stored procedure and reports success when run.
Only: nothing happens.

Also when I paste the code from the SP into the Command section directly, the result is identical: success but no rows affected when checking afterwards.

I have recreated the SP and the job, thinking I did something wrong. I have also changed the job owner to different accounts (from my own all the way to admin) thinking it had something to do with rights - the job history consistently reports success across the board.

What am I doing wrong or what am I not seeing?

8
  • 2
    For starters, I'd modify your SP to issue a SELECT with your WHERE clause before the UPDATE and PRINT CONVERT(VARCHAR(10),@@ROWCOUNT) to verify that rows actually satisfy your WHERE condition. Also, make sure your job step is pointing to the correct database. Aug 22, 2017 at 10:41
  • Hi @ScottHodgin. It says 1, which is exactly the number of rows it should be. Executed as user: [username here]. 1 [SQLSTATE 01000] (Message 0). The step succeeded. Aug 22, 2017 at 10:53
  • I'd build in a CASE and first select @iCntMembers = count(*) FROM Members m WHERE m.BirthDate IS NULL AND m.MemberNumber IS NOT NULL AND m.MemberStatus IN (1,2) to see if you have any results returned.
    – John K. N.
    Aug 22, 2017 at 10:54
  • 2
    Is it T-SQL step? Under what account is it executig? Try to execute the code impersonating that login, maybe it really does nothing. Because you have date/time manipulations that can be language dependent (you didn't post here your udf code), and different logins can have different default language
    – sepupic
    Aug 22, 2017 at 10:55
  • 2
    Does the job target the correct database? Aug 22, 2017 at 11:03

1 Answer 1

2

I started yesterday by profiling the job execution and found nothing of interest.

As it turns out, the devil was in calling [MyOtherDatabase].dbo.fn_LocalTimeToUTC.

For some reason (that is beyond me), there's some credentials checking going on (yes: in a time conversion function...) but if that fails, no error is returned - but also no result. Don't ask. I didn't write it.

Once I decided to skip the conversion everything worked flawlessly.

I'll be wanting those two days of my life back now.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.