SQL Server – Stored Procedure Called from Job Does Nothing

jobssql serverstored-procedures

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?

Best Answer

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.

Related Question