Sybase IQ 数据备份

bdk82924

 
 
备份可以采用 temp_extract_name1 的语法备份，但是在用定时任务的时候会出现如下的错误
 
 
Handler for event 'test_ev' caused SQLSTATE '09W03'
Result set not permitted in 'test_ev'
 
解决办法
 
例如：CREATE PROCEDURE proc1()BEGINSET TEMPORARY OPTION Temp_Extract_Column_Delimiter = '|';SET TEMPORARY OPTION temp_extract_name1 = 'foo.out';SELECT * FROM iq_table;SET TEMPORARY OPTION temp_extract_name1 = '';  END; CREATE EVENT "test_ev" ENABLE HANDLERBEGINSELECT * INTO #tmp FROM proc1();END;TRIGGER EVENT test_ev; 
 
 
动态执行备份语句
 
if exists(select 1 from sys.sysprocedure where proc_name = 'p_test' and user_name(creator)='bdk') then   drop procedure bdk.p_testend if;create procedure bdk.p_test()on exception resumebegin declare @sql         varchar(1000); select 1;  --必须加上这句话，不然会报错 试试看呢 set temporary option Temp_Extract_Column_Delimiter = '|';set @sql='set temporary option Temp_Extract_Name1 =''/home/backup/abc.bak''';execute(@sql); set @sql='select * from table01;';execute(@sql);set temporary option Temp_Extract_Name1 = '';end;bdk.p_test