例子两台ASE服务器名称为 Server1,Server2，需要在Server1中建立代理表，并通过Server1上的存储过程对Server1自身及Server2中表进行更新
1. 添加本地服务器名称
使用isql连接进入服务器Server1,Server2
1>;select @@servername
2>;go
确定服务器本地名称已经生效
如果返回NULL，
1>;sp_addserver Server_name,local
2>;go
Server_name应该跟interfaces中所用服务器名称一致
重新启动服务器，让本地名称生效
2. 添加远程服务器信息
在Server1上执行如下命令：
isql -Usa -P -SServer1
exec sp_addserver Server2, ASEnterprise, Server2
exec sp_addremotelogin Server2, sa, sa
exec sp_remoteoption Server2, sa, sa, trusted, true
go
3. 建立代理表
在Server2数据库pubs2建立表t_testproxy
create table t_testproxy (id int,name char(10))
在Server1数据库test中添加代理表信息
use test
go
create proxy_table t_testproxy at "Server2.pubs2.dbo.t_testproxy"
代理表名与原表名可以不同
Server1上查看代理表 信息如下：
Name Owner Object_type
---- ----- -----------
t_testproxy dbo user table
Data_located_on_segment When_created
----------------------- ------------
default Nov 28 2002 11:59AM
Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Identity
----------- ---- ----------- ----------- ----------- ----------- ------------ --------- ---------------- -----------
id int 4 NULL NULL 0 NULL NULL NULL 0
name char 10 NULL NULL 0 NULL NULL NULL 0
exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
------------ -------------- ----------- ----------------- ------------
1 0 0 0 0
concurrency_opt_threshold
-------------------------
0
Object is Remote/External
-------------------------
presales.pubs2.dbo.t_testproxy //可从此处看出代理表所指向的真正对象
Object created with 'existing' option
Object does not have any indexes.
No defined keys for this object.
Object is not partitioned.
Lock scheme Allpages
The attribute 'exp_row_size' is not applicable to tables with allpages lock scheme.
The attribute 'concurrency_opt_threshold' is not applicable to tables with allpages lock scheme.
4. 代理表测试
Server2上插入数据：
insert into t_testproxy values(1,'Server2')
Server2上插入数据：
insert into t_testproxy values(2,'Server1')
Server1上查询数据：
select * from t_testproxy
id name
----------- ----
1 Server2
2 Server1
5. 视图测试
在Server1上test数据库中建立用户表t_testproxyview
create table t_testproxyview (id int,address varchar(30)
插入测试数据
insert into t_testproxyview (1,'Chengdu')
insert into t_testproxyview (2,'Sichuan')
insert into t_testproxyview (3,'sky')
建立视图
create view v_proxy as select a.*,b.address from t_testproxy a,t_testproxyview b
where a.id = b.id
查询视图
select * from v_proxy
id name address
----------- ---- -------
1 Server2 Chengdu
2 Server1 Sichuan
视图的更新
update v_proxy set address = 'test' where id = 2 更新本地表 执行成功
update v_proxy set name = 'test' where id = 2 更新远程表 报告错误如下：
The optimizer could not find a unique index which it could use to scan table 'pubs2.dbo.t_testproxy' for cursor 'C11'.
必须为远程表建立 主键或者唯一索引
Server2上执行（不能在Server1上为代理表建立主键）：
alter table t_testproxy add constraint pk_t_testproxy primary key (id)
update v_proxy set name = 'test' where id = 2 再次更新代理表，成功
id name address
----------- ---- -------
1 Server2 Chengdu
2 test test
对试图中本地表及代理表同时作更新
update v_proxy set name = 'test1',address = 'test1' where id = 1
报告错误：
View 'v_proxy' is not updatable because the FROM clause names multiple tables.
Sybase不支持在视图中一次更新多表
6. 存储过程测试
在Server1上
create proc p_proxy(@id int)
as
begin
begin tran
update t_testproxy set name = 'test3' where id = @id --更新代理表
update t_testproxyview set address = 'test3' where id = @id
commit
end
exec p_proxy 2
id name address
----------- ---- -------
1 Server2 Chengdu
2 test3 test3
结论：可以在存储过程中对本地及远程表进行操作，并利用事务来保证一致性
书写仓促，难免疏漏，见谅！