Solved

SQL Server 2005 Union View Exception

Posted on 2011-02-18
3
991 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
3 Comments
 
LVL 39

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 40

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
A short film showing how OnPage and Connectwise integration works.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

943 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

9 Experts available now in Live!

Get 1:1 Help Now