May 28, 2003 at 11:49 am
Hi,
We have a remote server (Server_Remote) that we are linking to on one of our servers (Server_A), but need access to its data on all of our servers, so we tried linking our other server to Server_A, but when try to do a "SELECT * FROM Server_A.Server_Remote.SomeDB.dbo.SomeTable", we get:
Error 117, Severity 15, The object name 'Server_A.Server_Remote.SomeDB.dbo.' contains more than the maximum number of prefixes. The maximum is 3.
Is there a way to change this maximum to 4? We are pursuing setting up replication, but the remote server admins are not cooperating.
Thanks for any insights!
Nate Pink
May 28, 2003 at 11:55 am
Use four-part naming convention. For example,
select * from tstsql01.master.dbo.sysobjects
May 28, 2003 at 12:57 pm
That will definitely let me access the database physically on Server A from our other servers, but what I am looking for is a way to access the databases through the linked server that resides on Server A, but are physically located on Server_Remote... I am not sure it can be done or that I am making any sense.
Server B, Server C, and Server D have a linked server to Server A
Server A has a linked server to Server Remote
I want to be able to access Server Remote through server A. One reason for this is we don't want to have to open our firewall for every sql server that needs access to this data.
This is probably not the solution we will want to use even if someone has the answer, but it would be a nice bandaid while we get the replication worked out.
Thanks,
Nate
May 28, 2003 at 1:17 pm
Create sp in serverA to select data from remote_server and call the sp from server B, C and D.
create procedure sp2000_helpdb
as
select * from [remote_server].master.dbo.sysdatabase
in server A
exec [server_A].master..sp2000_helpdb
in server B, C, and D
Edited by - Allen_Cui on 05/28/2003 1:20:05 PM
May 29, 2003 at 6:58 am
A view might be better
on SERVER_A
use AnotherDB
GO
create view SomeTableView as
SELECT * FROM
Server_Remote.SomeDB.dbo.SomeTable
GO
on SERVER_B etc
SELECT * FROM
Server_A.AnotherDB.dbo.SomeTableView
Far away is close at hand in the images of elsewhere.
Anon.
May 29, 2003 at 10:20 am
Interesting.Will it not be more efficient to use a proc when compared to a view?
May 29, 2003 at 10:28 am
Don't know. Might depend on what you are doing, just retrieval or more. Maybe you could try both and post your findings.
Maybe others could post their thoughts!
One advantage is that you can select from and join to a view, with a proc you have to exec.
Edited by - davidburrows on 05/29/2003 10:30:13 AM
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply