• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1016
  • Last Modified:

SQL Server 2005 Union View Exception

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
alcindor
Asked:
alcindor
1 Solution
 
lcohanDatabase AnalystCommented:
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
 
SharathData EngineerCommented:
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
 
alcindorAuthor Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now