March 11, 2011 at 10:22 am
I'm a SQL novice-at-best and am basically learning about tools as the need arises. I still don't really understand joins. But I was given this project and have it working for the most part but it's not at all elegant and prone to fail.
Here is the setup: I have 127 sites with local sql servers. There is a process that runs nightly and returns results from the process to a table. What I'm trying to do is query the remote servers for that result and write it to a local table.
Right now I have it set up as a cursor using dynamic sql to insert the network address of the site by having a local reference table that the cursor moves through. The problem with this is that if any of the sites are offline for whatever reason it kills the cursor and it will not continue to the next row in the reference table. I've been reading up about cursors and it seems like the general attitude towards them borders on a hate crime. My question is, how do I structure a query to pull data from a row and use it to insert the result into another table.
Here is the code:
declare @cntr varchar(100) -- counter
declare @net varchar(200) -- network name of linked server
declare @task varchar(200) -- site specific task key
declare @query varchar(400) -- site query
declare db_success cursor for
select cntr
from reference
open db_success
fetch next from db_success into @cntr
while @@fetch_status = 0
begin
set @net = (select network from reference where cntr = @cntr)
set @task = (select task_key from reference where cntr = @cntr)
set @query = 'insert into dbo.results (site_num, successful, date)
select (select site_id from ['+ @net +'].remote.dbo.STORE ),
(select successful_flag from ['+ @net +'].remote.dbo.TASKHISTORY where run_date >
(select convert(char,getdate(),101))
AND task_key = '+ @task +'),
(select convert(char,getdate(),101))'
exec (@query)
fetch next from db_success into @cntr
end
close db_success
deallocate db_success
This works correctly when all sites are online. If any more detail or explanation is needed I'd be happy to provide.
March 11, 2011 at 10:36 am
Put
BEGIN TRY
END TRY
BEGIN CATCH
END CATCH
around your exec
e.g.
BEGIN TRY
EXEC (@query)
END TRY
BEGIN CATCH
PRINT @net + ' Failed'
END CATCH
Far away is close at hand in the images of elsewhere.
Anon.
March 11, 2011 at 11:23 am
That worked and actually solved another problem I was having with some of the notification side of it.
Thank you.
March 13, 2011 at 3:10 pm
TK-42-1 (3/11/2011)
That worked and actually solved another problem I was having with some of the notification side of it.Thank you.
You're welcome and thank you for the reply 🙂
And remember there are no novices but only those that do not know the answer 😀
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply