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.
TheBeginning: BEGIN TRY Exec xp_logininfo 'Domain\UserOrGroup', 'all'; END TRY BEGIN CATCH Goto TheBeginning; END CATCH