Creating a view with a table to a linked database

Question

Creating a view with a table to a linked database

Ann-176222

SSC Enthusiast

Points: 184
More actions
May 30, 2007 at 7:54 am

#196049

I created a view with a join between a table in the master-database & a table
in a linked database. When I run this view, there is no problem at all. When
I try to save this view. The system is hanging. When I stop the process and
look in the current activities there is a locking process caused by the fact
that I'm creating that view.
Does anybody know what can be the cause of this problem and how to solve it?
Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

I have never seen this, but have no relation to understand exactly what you are doing. Can you post the query for the view (complete DDL of the view) so we can see what you are doing, will help us better help you.

Ann-176222 SSC Enthusiast Points: 184 More actions · Answer 2

Included the query:

SELECT e_orderheader.OrderNumber, edw_orderheader.[Order number]

FROM SESLSESGW014.E_Business.dbo.OrderHeader e_orderheader LEFT OUTER JOIN

dbo.[Order Header] edw_orderheader ON e_orderheader.OrderNumber = edw_orderheader.[Order number] COLLATE Latin1_General_CI_AS

WHERE (edw_orderheader.[Order number] IS NULL)

Strange thing. When I update the query via Design-mode, the system hangs, when I update the view via properties, I can save it:

CREATE VIEW [Search_New_eOrders]

AS SELECT e_orderheader.ordernumber, edw_orderheader.[order number]

from seslsesgw014.e_business.dbo.orderheader as e_orderheader

left join [order header] as edw_orderheader on e_orderheader.[ordernumber] =edw_orderheader.[order number] COLLATE Latin1_General_CI_AS

Where edw_orderheader.[order number] is null

Lowell SSC Guru Points: 323518 More actions · Answer 3

could it be a permissions issue? you might have SELECT permission in the master database, but NOT dbowner (so that you can CREATE VIEW) in the other database? what user context are you in the other database?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!