Solved

Re-run dynamic openquery

Posted on 2010-11-12
6
837 Views
Last Modified: 2012-05-10
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).

0
Comment
Question by:Airmaster
  • 4
  • 2
6 Comments
 

Author Comment

by:Airmaster
ID: 34124430
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."
0
 
LVL 1

Expert Comment

by:bclevlnd
ID: 34124510
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

0
 

Author Comment

by:Airmaster
ID: 34140017
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.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Airmaster
ID: 34140416
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.
0
 
LVL 1

Accepted Solution

by:
bclevlnd earned 125 total points
ID: 34140591
You could add output inserted.tagname (or any of the fields you are inserting into tblResults) into a @TempTable and then select count(*) from @TempTable to set an @Variable.  You could then have your while statement check for @Variable = 0.  This would work only if you know you should always receive a value back from you openquery.
0
 

Author Closing Comment

by:Airmaster
ID: 34292917
Thanks for the help.  I did something very similar with your assistance.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
IN with @variable 5 25
Help with simplifying SQL 6 49
today minus 2 years SQL server 2008 2 31
Anyway to make these 2 SQL statements into one? 13 25
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now