Solved

SQL Server 2005 Union View Exception

Posted on 2011-02-18
3
1,000 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 500 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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 an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

734 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