Solved

SQL Server 2005 Union View Exception

Posted on 2011-02-18
3
989 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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

22 Experts available now in Live!

Get 1:1 Help Now