SYBASE如何跨server操作?
例子两台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 结论:可以在存储过程中对本地及远程表进行操作,并利用事务来保证一致性 书写仓促,难免疏漏,见谅! |