Re-run dynamic openquery
Posted on 2010-11-12
In SQL Server 2008, I am grabbing data from another database. The interface for that other database is through OPENQUERY.
Occasionally, some of the calls fail. If they do, I would like to be able to re-run that query, so that I can grab the complete set of data and store the results in another table.
I am using dynamic SQL to construct the query, which I store in @OpenQueryString, and then I execute it via "exec (@OpenQueryString)"
Can someone tell me how I would accomplish this? I suppose I could try a TRY-CATCH, but that doesn't seem like it is the right solution. What I think I would want is a WHILE LOOP that would loop if there was an error condition (at least try a few times before bugging out).