Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server 2005 Union View Exception

Posted on 2011-02-18
3
Medium Priority
?
1,014 Views
Last Modified: 2012-05-11
The following View works just fine, producing the union of two tables each named TodaysFrames, one table is on the same server as the View, the other table is on another (Linked) server.


SELECT     FrameID, FrameLabel
FROM         dbo.TodaysFrames
UNION
SELECT     FrameID, FrameLabel
FROM         SERVER2.DBX.dbo.TodaysFrames AS TodaysFrames_2

The problem occurs if the Linked server goes off line, in this situation, running the View gives an error as you would expect.
Is there any way thay I can code the View such that at least the results from the first part of
the union is returned?
If I could use a stored procedure then I could use a BEGIN TRY..END TRY block but the existing system uses
a View and there are several procedures that are dependent on the View so I am looking for
a change to the View to minimise the change required.

0
Comment
Question by:alcindor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 34930046
I believe that could work if you create a SQL Table Function with the same name however if you select from the view without DBO. prefix you must add that to all calls to the view.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34930367
Instead of View, if you are able to create a function, you can conditionally execute the query based on test connection with linked server. If possible, create function like this.
create function dbo.your_view() 
returns @result table(FrameID int, FrameLabel varchar(200)) as
begin
declare @srvr nvarchar(128), @retval int;
set @srvr = 'SERVER2';
exec @retval = sys.sp_testlinkedserver @srvr;
if @retval = 0
insert @result
SELECT     FrameID, FrameLabel
FROM         dbo.TodaysFrames
UNION
SELECT     FrameID, FrameLabel
FROM         SERVER2.DBX.dbo.TodaysFrames AS TodaysFrames_2
else 
insert @result
SELECT     FrameID, FrameLabel
FROM         dbo.TodaysFrames
return
end

Open in new window

You can get the result with the query
select * from dbo.your_view()

Open in new window

0
 
LVL 2

Author Closing Comment

by:alcindor
ID: 34932833
I used a Table function and replaced the code in my original view with select * from MyNewFunction()

Thanks for your help

Roger
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

636 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