create proxy table

[babeo]

I have a table called tableA locates on ServerA, now I have another serverB, and I want to use and access this table from serverB, how can I make it using the proxy table method.
- What is exactly proxy tables doing?
- How to set up proxy tables?

Notes: I have a common/same id/pwd to access the server and database of both servers.

Could any one help?
Thanks

 

[dickiebird]

Here's an example we use - created by a DBA - I don't understand all of it - especially the sp_addobjectdef bit.
Give it a try ?

print 'sda_agent'
SETUSER 'dbo'
go

sp_addobjectdef sda_agent, 'SYBALADDIN.sda.dbo.agent', 'table'
create existing table dbo.sda_agent (
agent_code varchar(10) not null,
status tinyint not null,
postal_add1 varchar(35) null,
postal_add2 varchar(35) null,
postal_add3 varchar(35) null,
postal_add4 varchar(35) null,
postal_add5 varchar(35) null,
supplier_code char(10) null,
supplier_code2 char(10) null,
un_code smallint not null,
agent_type char(1) not null,
sl4 smallint not null,
short_na varchar(30) not null,
alt_code varchar(10) null
)
lock allpages
on 'default'
go

print 'agent_idx1'
create unique clustered index agent_idx1
on dbo.sda_agent (agent_code)
on 'default'
go

print 'agent_idx2'
create nonclustered index agent_idx2
on dbo.sda_agent (supplier_code)
on 'default'
go

print 'agent_idx3'
create nonclustered index agent_idx3
on dbo.sda_agent (supplier_code2)
on 'default'
go

print 'agent_idx4'
create nonclustered index agent_idx4
on dbo.sda_agent (agent_type)
on 'default'
go

print 'agent_idx5'
create nonclustered index agent_idx5
on dbo.sda_agent (sl4)
on 'default'
go

print 'agent_idx6'
create nonclustered index agent_idx6
on dbo.sda_agent (un_code)
on 'default'
go

grant Delete on sda_agent to AAA_USER
go

grant Insert on sda_agent to AAA_USER
go

grant Update on sda_agent to AAA_USER
go

SETUSER
go


Dickie Bird (-)))

 

[babeo]

Thanks Dickie,

I am not sure either, but you may be right. For my understand (but not clear in detail, need clarification and correction) that I should:
- Create a common id/pwd on both servers
- create database access on both servers for this id
- then create a link (?) of the table of the original server for the other server by using sp_addobjectdef command (similar to the way you post above). And this is done on the original server- serverA, not the other server.
Hope someone can correct this for me.
Thanks

 

[iziki]

Hi,
use this syntax from serverB :

CREATE PROXY_TABLE TableA at "ServerB...TableA"
go

good luck,
Iziki