January 22, 2015 at 9:40 am
yes the error is if I run :
Select * From V (from server A)
--- I don't Put the name of the link server because remember in server B I have the same view but the information is from database in server B.
I want the data from database-server B, from server A using a view.
if I put the name of the server :
select * From A.DB.dbo.V
and the error is the same like select * from V (from server A)
January 22, 2015 at 9:47 am
Ok, so basically when you issue
SELECT * FROM V -- from server A
what you need to do is query B.db.dbo.V
In this case, change the view definition on server A:
ALTER TABLE V
AS
SELECT *
FROM OPENQUERY(B,'SELECT * FROM DB.dbo.V')
Would this work for you?
-- Gianluca Sartori
January 22, 2015 at 9:51 am
the result is
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.
January 22, 2015 at 9:57 am
you mean,
Alter View V......
I do, and the view work perfect.!!!!! 😛
but, I want to know WHY If I run Select * from V I have this strange error. with column that I don't have, etc, etc,
And Why this view work perfect in sql server 2005....
and why if I have the same view from B to A (but name change... I think in this point you understand) this view from server B works fine too.
:/
January 22, 2015 at 10:07 am
ummmm
Is not correct the result
because when I say Select * FROM OPENQUERY(,'SELECT * FROM DB.dbo.V')
The select execute the View from server B. this View in server B have A.dbo.DB.tablename
data .
View in server A is. ..B.dbo.DB.tablename check again the query....
I mean, both View retrieve data from the same tables but the data in DB database is diferent.
when I execute select * from V (from server A) I want the data from server B and when I execute select * From V (from server B) I want the data from server A.
If I execute the openquery with B, DB.dbo.V) the data is the server A actually. becase B.db.dbo.V is data from A.
do you understand?
January 22, 2015 at 10:48 am
-- In server A:
CREATE VIEW V_hidden
AS
SELECT *
FROM MyTablesThatLiveInServerA
GO
-- In server B:
CREATE VIEW V_hidden
AS
SELECT *
FROM MyTablesThatLiveInServerB
GO
-- In server A:
CREATE VIEW V
AS
SELECT *
FROM OPENQUERY(B,'SELECT * FROM MyDB.dbo.V_hidden')
GO
-- In Server B:
CREATE VIEW V
AS
SELECT *
FROM OPENQUERY(A,'SELECT * FROM MyDB.dbo.V_hidden')
GO
Makes sense?
-- Gianluca Sartori
January 22, 2015 at 11:42 am
yes It works!
do you know why the before sentence don't work?
and why with sql server 2005 work perfect too?
I'm a women who want to know why the things happen. 😉
thank.
January 23, 2015 at 1:37 am
Honestly, I have no idea.
Maybe tweaking the linked server / provider settings could make a difference, but I have no idea.
-- Gianluca Sartori
January 23, 2015 at 5:25 am
thanks so much!!!
I change the view with OpenQuery function.
and I will investigating until continue find the explanation about that situation.
January 23, 2015 at 6:55 am
Glad I could help
-- Gianluca Sartori
Viewing 10 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply