SQL server: how to union all for others if it errors on few

Hello Experts, please help. I have a SQL Server query doing union all among 12 servers. Issue is if one of the server goes down, my whole query fails.
How can i ignore that failing server and resume with other union all's.

example as below:

(select * from openquery(server1, 'select *****')) union all
(select * from openquery(server2, 'select *****')) union all
(select * from openquery(server3, 'select *****')) union all  
(select * from openquery(server4, 'select *****')) union all
(select * from openquery(server5, 'select *****')) union all
(select * from openquery(server6, 'select *****')) union all
so on till server 12
Harman15Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior Database AdministratorCommented:
Hi,

how about this:
Create temp table

insert #TempTable
select * from openquery(server1, 'select *****')
 
insert #TempTable
select * from openquery(server2, 'select *****')

insert #TempTable
select * from openquery(server3, 'select *****')
 
etc.

This also does several things - reduces the overall datasize of the openquery's and allows the rest to proceed if one fails.

HTH
  David

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Russell FoxDatabase DeveloperCommented:
If you can live with stale data you could run a nightly job to pull the data one at a time into a "datamart", basically a single denormalized table. Surround each with a try/catch.
Harman15Author Commented:
Thanks David/Russell.
David, The thing is this part of the code is from a SSRS report ( it pulls perf data). Going by TempDB approach, my concern is it will create too many temp DB's for each SSRS user (not sure how it will work).

Russell, i tried a similar static thing, created a SQL job and created a table (with truncate it in the first step) and had 12 job steps of inserts from each individual server. And had it skip to next step on failure and had job scheduled to run every 2 mins (as i get the complete results in 1 mins from all servers). But then i realized it would not work as truncate must be running, while some user click SSRS ( he might not see data). Not sure, how i can achieve it as this report is used extensively 24*7*365 and we do not want SSRS report to completely fail but rather still read from others, if one of the link server is down.

I tried bunch of steps. i found this try catch thing. and thought of doing some testing. But i am not able to make it work. I only get till "All servers up" but if i intentionally rebooted X, i should be seeing "X is down" but instead i see "All Servers Up" along with error that link server X cannot be connected.

Loop that i am testing the logic. If it works, then i will replace it with my real code.


declare @retval varchar (100) ,@retval1 varchar (100), @retval2 varchar (100), @retval3 varchar (100), @retval4 varchar (100),
      @srvr1 nvarchar(128),@srvr2 nvarchar(128),@srvr3 nvarchar(128),@srvr4 nvarchar(128)

begin try
 
 EXEC sp_testlinkedserver X
 EXEC sp_testlinkedserver Y
 EXEC sp_testlinkedserver Z
 EXEC sp_testlinkedserver T


end try
begin catch
 set @retval = sign(@@error);
end catch;
if sign(@@error) = 0
BEGIN
PRINT 'ALL SERVERS ARE UP'
END
Else
Begin
   set @srvr1 = 'X';
   begin try
   exec @retval1 = sys.sp_testlinkedserver @srvr1
   end try
   begin catch
   set @retval1 = sign(@@error);
   end catch;
            if sign(@@error) = 1
            BEGIN
             PRINT 'X is down'
            END
            ELSE
                 Begin
                 set @srvr2 = 'Y';
                 begin try
                 exec @retval2 = sys.sp_testlinkedserver @srvr2
                 end try
                       begin catch
                       set @retval2 = sign(@@error);
                       end catch;
                                    if @retval2 = 1
                                    BEGIN
                                      PRINT 'Y is down'
                                    END
                                    ELSE
                                           Begin
                                           set @srvr3 = 'Z';
                                           begin try
                                           exec @retval3 = sys.sp_testlinkedserver @srvr3
                                           end try
                                                   begin catch
                                                   set @retval3 = sign(@@error);
                                                   end catch;
                                                      if sign(@@error) = 1
                                                      BEGIN
                                                         PRINT 'Z is down'
                                                      END
             
                                                             ELSE
                                                                  Begin
                                                                  set @srvr4 = 'T';
                                                                  begin try
                                                                  exec @retval4 = sys.sp_testlinkedserver @srvr4
                                                                  end try
                                                                        begin catch
                                                                        set @retval4 = sign(@@error);
                                                                        end catch;
                                                                        if sign(@@error) = 1
                                                                        BEGIN
                                                                            PRINT 'T'is down'
                                                                        END
                                                                        END
                                                                        END
                                                                        END
                                                                        END
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Russell FoxDatabase DeveloperCommented:
sp_testlinkedserver doesn't actually return a value, so "EXEC @retVal = sys.sp_testlinkedserver" will not return anything. It just throws an error:

DECLARE 
	  @srvr1 nvarchar(128) = 'X'
	, @srvr2 nvarchar(128) = 'Y'
	, @srvr3 nvarchar(128) = 'Z'
	, @srvr4 nvarchar(128) = 'T'
	, @X TINYINT = 1
	, @Y TINYINT = 1
	, @Z TINYINT = 1
	, @T TINYINT = 1

--	---------------------------
BEGIN TRY
 EXEC sp_testlinkedserver @srvr1
END TRY
BEGIN CATCH
	SET @X = 0
	PRINT @srvr1 + ' is down'
END CATCH
--	---------------------------
BEGIN TRY
 EXEC sp_testlinkedserver @srvr2
END TRY
BEGIN CATCH
	SET @Y = 0
	PRINT @srvr2 + ' is down'
END CATCH
--	---------------------------
BEGIN TRY
 EXEC sp_testlinkedserver @srvr3
END TRY
BEGIN CATCH
	SET @Z = 0
	PRINT @srvr3 + ' is down'
END CATCH
--	---------------------------
BEGIN TRY
 EXEC sp_testlinkedserver @srvr4
END TRY
BEGIN CATCH
	SET @T = 0
	PRINT @srvr4 + ' is down'
END CATCH
--	---------------------------
IF @X+@Y+@Z+@Z = 4
	PRINT 'All servers up'

Open in new window

Russell FoxDatabase DeveloperCommented:
You could also switch it to a publisher model: have a job on each server that pushes its data into a table on the main reporting server. Something like this running on Server2 where Server1 runs the report:
DELETE FROM Server1.ReportDatabase.dbo.ReportTable
WHERE ServerName = 'Server2'
GO
INSERT INTO Server1.ReportDatabase.dbo.ReportTable
SELECT 'Server2' AS ServerName,
	GETDATE() AS DatePublished,
	foo,
	bar
FROM SomeTable
GO

Open in new window

That would leave the prior data in the table if Server2 went down, so you'd have to decide if that's okay or if your report would need to limit it to just a recent DatePublished.
David ToddSenior Database AdministratorCommented:
Hi,

As Russell suggested, see if you can live with stale data.

What I mean is interact with the business and see if they need up-to-the-minute results or history. Al most invariably they need one and not the other. For instance, Daily sales totals for the last week/month/year almost NEVER need current day in progress results.

If so, then as you appear to be a multinational, get those results pushed to a central datamart on close of business.

On the other hand, does this report need all 12 server results in it? That is, as a multinational living in New Zealand, do I care about the results from Canada or Alaska? Probably not. And if so, can live with quite stale data. This line of thinking suggests that you need twelve similar reports, one for each server. Then if that server is inaccessible no biggie as it only affects an individual report.

Question: How does the business handle timezone differences? That is, there is a huge difference in querying worldwide business at a particular point in time - some will have closed for the day, some will be trading, and some wont have even opened yet.

HTH
  David
Harman15Author Commented:
Thanks a lot Russel

Sir, i tried your given loop. Thanks for providing it. Really appreciate it. It  works great in my testing environment. But when i replace it with my real code. I still get the error message of general connectivity of the linked server its failing for. for instance, i am replacing Print with  the union all query, which is going to all the server except 3. So when 3 goes down. I want union all to happen for all other 11 servers and loop should break after that.

BEGIN TRY
 EXEC sp_testlinkedserver @srvr3
END TRY
BEGIN CATCH
      SET @Z = 0
(select * from openquery(server1, 'select *****')) union all
(select * from openquery(server2, 'select *****')) union all
-----(select * from openquery(server3, 'select *****')) union all  
(select * from openquery(server4, 'select *****')) union all
(select * from openquery(server5, 'select *****')) union all
(select * from openquery(server6, 'select *****'))
END CATCH

The error that i am seeing is
" Uncommitted transaction is detected at the end of the batch. The transaction is rolled back. OLE DB provider "SQLNCLI10" for linked Server "server3" returned message "A network-related or instance-specfic error has occured while establishing a connection to SQL server. Server not found or not accessible. Check if the instance name is correct and If SQL is configured to allow remote connections. For mroe information see sql BOL. "oLE DB provider "SQLNCLI10" for linked server 'server3"returned message "login timeout expired"

If all servers are up, then the last logic of your code give me the results. which is good.
*****************************
IF @X+@Y+@Z+@Z = 4
My union all query returns the result.
*****************************
I am not sure how i can make it work. Like if one of the linked server is down. Then run the query with union all for all the remaining link servers and break the loop.
Please suggest.
Thanks.

Thanks David for your advice as well Sir. The report in question is used to collect perf data, that is collected every minute. So the report needs to be as close to real time as possible. I will try to explore your suggestion for dumping data to one table. As table will grow, as each extract gives 700-800,000 rows. i am thinking to create a run ID column and pull the report by most recent run ID. only concern is table contentions (even if no lock is used) and performance the other expect ( i might need to create clustered Index).
Russell FoxDatabase DeveloperCommented:
You can't embed a try/catch into a query, but you could construct a dynamic SQL statement and execute it with sp_executesql:
DECLARE @SQLStatement NVARCHAR(MAX) = ''
	, @srvr1 nvarchar(128) = 'X'
	, @srvr2 nvarchar(128) = 'Y'
	, @srvr3 nvarchar(128) = 'Z'
	, @srvr4 nvarchar(128) = 'T'
	, @X INT = 1
	, @Y INT = 1
	, @Z INT = 1
	, @T INT = 1

--	---------------------------
BEGIN TRY
	EXEC sp_testlinkedserver @srvr1
	SET @SQLStatement += ' UNION ALL SELECT * FROM ' + @srvr1 + '.YourDB.dbo.YourTable'
END TRY
BEGIN CATCH
	PRINT @srvr1 + ' is down'
END CATCH
--	---------------------------
BEGIN TRY
	EXEC sp_testlinkedserver @srvr2
	SET @SQLStatement += ' UNION ALL SELECT * FROM ' + @srvr2 + '.YourDB.dbo.YourTable'
END TRY
BEGIN CATCH
	PRINT @srvr2 + ' is down'
END CATCH
--	---------------------------
BEGIN TRY
	EXEC sp_testlinkedserver @srvr3
	SET @SQLStatement += ' UNION ALL SELECT * FROM ' + @srvr3 + '.YourDB.dbo.YourTable'
END TRY
BEGIN CATCH
	PRINT @srvr3 + ' is down'
END CATCH
--	---------------------------
BEGIN TRY
	EXEC sp_testlinkedserver @srvr4
	SET @SQLStatement += ' UNION ALL SELECT * FROM ' + @srvr4 + '.YourDB.dbo.YourTable'
END TRY
BEGIN CATCH
	PRINT @srvr4 + ' is down'
END CATCH
--	---------------------------
IF @X+@Y+@Z+@T = 4
	PRINT 'All servers up'

--	Remove the first "UNION ALL"
SELECT @SQLStatement = RIGHT(@SQLStatement, LEN(@SQLStatement)-11)

EXEC master.sys.sp_executesql @SQLStatement

Open in new window

David ToddSenior Database AdministratorCommented:
Hi,

Have you tried my suggestion above? It will possibly perform better on larger result sets than your union all.

Regards
  David
Harman15Author Commented:
Thanks a lot Russel/David.

Russel, Sir your query was great. I will keep the logic for some other solution.

For this problem, i went as suggested by David. I have a job, which runs 12 steps. Each pulls the data to a table ( have each step to go to next on failures). Once all is done, it marks completion with runid. Then SSRS reads the data with MAX runID.

Appreciate all your help.
Thanks
Harman15Author Commented:
Great advice
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.