asked on

Re-run dynamic openquery

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).

Airmaster

ASKER

I tired the Try-Catch, but got the error 3930

"The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."

bclevlnd

try-catch will only stop on errors/warnings that have a severity <= 10 or > 20 and do not break the DB connection. If you are returning the value into a variable, a while loop that is checking that variable for a specific value may work.

Like:
declare @MyVar varchar(20)
set @MyVar=''
while (@MyVar = '')
begin

Airmaster

ASKER

What I am returning not a value, but ultimately something that will be inserted into a table. In other words, I execute the query, and it may return several rows and columns of data that I then insert into a table.

Maybe something like this would work if it didn't return any rows of data. I don't see anything that I could use to determine if the query failed.

Here is an example query, which I stores in @strSQL:

SELECT * FROM OPENQUERY("SERVER",'SET SamplingMode = calculated, CalculationMode=Average, intervalmilliseconds = 1d,
StartTime = 1-2-2007, EndTime = 09/15/10 Select tagname, timestamp, value, quality
from ihrawdata
where tagname = Tag.Name')

This query is part of a stored procedure (procDataAverage), which generates the query via dynamics sql, I execute it via exec (@strSQL).

I then save the data into another table by the following

INSERT INTO tblResults (tagname, TagDate, value, quality)
EXEC procDataAverage ....variables to be passed into procedure.....

So, if the OpenQuery fails, then nothing is stored in tblResults. Note that I execute this multiple times, so tblResults fills up with a lot of data.

Airmaster

ASKER

Here is the error I get:

Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SET SamplingMode = calculated, CalculationMode=Average, intervalmilliseconds = 1d,
StartTime = 1-2-2007, EndTime = 09/15/10 Select tagname, timestamp, value, quality
from ihrawdata
where tagname = Tag.Name" against OLE DB provider "IhOLEDB.iHistorian" for linked server "SERVER".

What I would like to do is to call the routine until it is successful.

ASKER CERTIFIED SOLUTION

bclevlnd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Airmaster

ASKER

Thanks for the help. I did something very similar with your assistance.