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
TheBeginning: BEGIN TRY Exec xp_logininfo 'DomainUserOrGroup', 'all'; END TRY BEGIN CATCH Goto TheBeginning; END CATCH
Karl Fasick
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… 🙂
SQLSoldier
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.
(SFTW) SQL Server Links 13/12/13 • John Sansom
[…] Never an Infinite Loop Around When You Need One – An interesting piece of T-SQL wizardry from Robert L. Davis(Blog|Twitter). […]