SYBASE如何跨server操作.doc

SYBASE如何跨server操作？例子两台ASE服务器名称为Server1,Server2，需要在Server1中建立代理表，并通过Server1上的存储过程对Server1自身及Server2中表进行更新1.建立代理表在Server2数据库pubs2建立表t_testproxycreatetablet_testproxy(idint,namechar(10)在Server1数据库test中添加代理表信息usetestgocreateproxy_tablet_testproxyatServer2.pubs2.dbo.t_testproxy代理表名与原表名可以不同Server1上查看代理表信息如下：NameOwnerObject_type-t_testproxydbousertableData_located_on_segmentWhen_created-defaultNov28200211:59AMColumn_nameTypeLengthPrecScaleNullsDefault_nameRule_nameAccess_Rule_nameIdentity-idint4NULLNULL0NULLNULLNULL0namechar10NULLNULL0NULLNULLNULL0exp_row_sizereservepagegapfillfactormax_rows_per_pageidentity_gap-10000concurrency_opt_threshold-0ObjectisRemote/External-presales.pubs2.dbo.t_testproxy/可从此处看出代理表所指向的真正对象ObjectcreatedwithexistingoptionObjectdoesnothaveanyindexes.Nodefinedkeysforthisobject.Objectisnotpartitioned.LockschemeAllpagesTheattributeexp_row_sizeisnotapplicabletotableswithallpageslockscheme.Theattributeconcurrency_opt_thresholdisnotapplicabletotableswithallpageslockscheme.2.代理表测试Server2上插入数据：insertintot_testproxyvalues(1,Server2)Server2上插入数据：insertintot_testproxyvalues(2,Server1)Server1上查询数据：select*fromt_testproxyidname-1Server22Server13.视图测试在Server1上test数据库中建立用户表t_testproxyviewcreatetablet_testproxyview(idint,addressvarchar(30)插入测试数据insertintot_testproxyview(1,Chengdu)insertintot_testproxyview(2,Sichuan)insertintot_testproxyview(3,sky)建立视图createviewv_proxyasselecta.*,b.addressfromt_testproxya,t_testproxyviewbwherea.id=b.id查询视图select*fromv_proxyidnameaddress-1Server2Chengdu2Server1Sichuan视图的更新updatev_proxysetaddress=testwhereid=2更新本地表执行成功updatev_proxysetname=testwhereid=2更新远程表报告错误如下：Theoptimizercouldnotfindauniqueindexwhichitcouldusetoscantablepubs2.dbo.t_testproxyforcursorC11.必须为远程表建立主键或者唯一索引Server2上执行（不能在Server1上为代理表建立主键）：altertablet_testproxyaddconstraintpk_t_testproxyprimarykey(id)updatev_proxysetname=testwhereid=2再次更新代理表，成功idnameaddress-1Server2Chengdu2testtest对试图中本地表及代理表同时作更新updatev_proxysetname=test1,address=test1whereid=1报告错误：Viewv_proxyisnotupdatablebecausetheFROMclausenamesmultipletables.Sybase不支持在视图中一次更新多表4.存储过程测试在Server1上createprocp_proxy(idint)asbeginbegintranupdatet_testproxysetname=test3whereid=id-更新代理表updatet_testproxyviewsetaddress=test3whereid=idcommitendexecp_proxy2idnameaddress-1Server2Chengdu2test3test3结论：可以在存储过程中对本地及远程表进行操作，并利用事务来保证一致性需要注意一点的是：建代理表时,远程表的结构数据已经存储在了本地,并且不会检测该表的结构是否发生改变,因此远程表的结构发生改变时,本地是不知道的,需要重新创建代理表,相当于重新刷新本地的存储的结构.5、代理表的索引问题。如上注意中提到，修改表结构后，需