[On SQL3] 建立Linked server 連到SQL4
Use DB1
go
CREATE SYNONYM tbl
FOR SQL4.myDB.dbo.tbl
GO
create view v_test
as
select * from SQL4.myDB.dbo.tbl
GO
Use TempDB
go
CREATE SYNONYM tbl
FOR SQL4.myDB.dbo.tbl
GO
create view v_test
as
select * from SQL4.myDB.dbo.tbl
GO
[On SQL1] 建立Linked server 連到SQL3
Query linked server :
User DB及System DB的錯誤號碼不一樣 / workaround : 改用Ｖiew
select * from SQL3.DB1.dbo.tbl
/*
Msg 7357, Level 16, State 2, Line 2
Cannot process the object ""DB1"."dbo"."tbl"". The OLE DB provider "MSOLEDBSQL" for linked server "SQL3" indicates that either the object
has no columns or the current user does not have permissions on that object.
Query was canceled by user.
*/
select * from SQL3.DB1.dbo.v_test
--OK
select * from SQL3.tempdb.dbo.tbl
/*
Msg 7314, Level 16, State 1, Line 3
The OLE DB provider "MSOLEDBSQL" for linked server "SQL3" does not contain the table ""tempdb"."dbo"."tab1"".
The table either does not exist or the current user does not have permissions on that table.
*/
select * from SQL3.tempdb.dbo.v_test
/*
Msg 7314, Level 16, State 1, Line 16
The OLE DB provider "MSOLEDBSQL" for linked server "SQL3" does not contain the table ""tempdb"."dbo"."v_test"".
The table either does not exist or the current user does not have permissions on that table.
*/
