Never an Infinite Loop Around When You Need One

Infinite Loop: image from
Infinite Loop (Image source)
You may be thinking, “You’re a DBA. I thought DBAs hated infinite loops.” Well, you’re right, most of the time. As with most things, there are times when infinite loops can be useful, as long as there is some attainable condition that breaks us out of it. I found myself in need of one such loop one day at work recently. I had a SQL job that was failing in production because it could not verify that the login had server access. We’ve all seen that error, right? This one was a little different.

To make a long story short, we thought we had found that the cause was an issue with the account’s properties in Active Directory (AD). We had implemented the fix we had come up with (I’m planning a different post on that topic), and I simply needed for the change we had made to propagate through AD. Executing xp_logininfo for the account was still failing. I didn’t want to keep checking it, so I wrote an infinite loop to keep checking the account. The trouble is, normal behavior is for the error to cause it to fail and stop the execution. I wanted it to do the opposite. I needed it to loop as long as it continued to fail and then break out of the loop once it finally succeeded.

I put the execution of xp_logininfo in a TRY … CATCH block to trap the error and prevent it from breaking out of the loop as it failed. I also added a short WAITFOR call to make it pause in between tries. This was a marathon, not a sprint, after all. The final piece was to add a GOTO statement in the CATCH block that redirected the script back tot he first line. Essentially, I had infinitely nested TRY … CATCH blocks until one of the TRY blocks was successful.

The Loop

    Exec xp_logininfo 'Domain\UserOrGroup', 'all';
    Goto TheBeginning;

3 thoughts on “Never an Infinite Loop Around When You Need One

  1. Hi, Robert – interesting structure. Any ideas on a good way to delay without hogging resources, to make it more polite to the Security logs & DC.

    I’m trying to recall if it was you who demoed public role dangers – and if there were similar loops… :)

    1. Hi Karl. It wasn’t me that demoed that. What resources are you worried about hogging? If you don’t want to cause too many entries in a security log, you can make the delay longer or simply wait and check it manually at some later point rather than looping.

Comments are closed.