attempt to initiate a new sql server operation with results pending

[eracn]

语句如下：
step1:
insert into tablea(column1,column2,...) values(:v1,:v2...);
commit;

step2:
sqlca.autocommit = true
执行一存储过程，该存储过程中会从tablea中读取数据，插入tableb，并有返回值returnvalue
sqlca.autocommit = false

step3:
if returnvalue
update tablea set column2 = :d where column1 = :v1;


现象：
能在tablea中插入需要的记录；tableb数据正确；但step3未能执行，通过sqlca.sqlerrtext，则为标题之错误提示。

从该提示看应该是有事务被挂起，但不是及时commit了吗？还是其他原因？

[永生天地]

怎么判断到：step3未能执行，通过sqlca.sqlerrtext，则为标题之错误提示。

[eracn]

未能正确update呀，所以才通过sqlca.sqlerrtext进行查看的。返回的sqlca.sqlcode 为-1

[sun1976]

pb版本,连接方式,检查sqlca.lock如何设置的

[wag_enu]

大至意思是:
在结果挂起时,企图启动一个新的SQL操作.

检查一下你step2 中提及的存储过程,看看有没有遗漏什么.

[eracn]

to sun1976:
PB 9 + SQL 2000，未对sqlca.lock作显式设定（我还不知道在哪儿可设定）

to wag_enu :
存储过程中基本是insert into tableb(...) select ... from tablea;
我想了解的是存储过程的返回值是在该存储过程执行完成后才会返回还是在过程中只要对该值作设置即会返回？因为该返回值是在代码中间进行赋值的

以上，谢谢两位

[eracn]

to sun1976:
sqlca.lock设为ru，仍同样错误信息

[wag_enu]

在哪里赋值都可以,遇上 return 时返回

[eracn]

那不应该呀，因为有正确返回值时表示已运行至最后了，而且对tablea都只是读。

我现在怀疑问题是在step1上，因为调用该存储过程时未设置sqlca.autocommit = true时tableb中是无记录的。

可是step1及时commit了呀。

另外，试过在step1前即设sqlca.autocommit = true，step3中的update不能正确执行

[sun1976]

调存储过程的语句贴出来,看看你怎么接受返回值的
返回值在传给存储过程前最后给出一个初始值

[eracn]

REATE procedure p_domesticenteraccountvouch(@ent_unique int,@ccode varchar(15) output)---收款确认生成之会计科目
as

SET NOCOUNT ON

declare @cus_id varchar(10),@cus_name varchar(60),@ent_bankid varchar(3),@style_id smallint,@digt1 varchar(60) ,@ord_currency varchar(10),@account_currency varchar(10),@curr_name varchar(10)
declare @total decimal(18,2), @ominus float,@iminus float, @kfbank varchar(10),
@citemcode varchar(10),@nflat dec(18,6),@nflat1 dec(18,6),@nflat2 dec(18,6), @cn_id varchar(10),@status tinyint,@incom_bank varchar(26),
@ent_date datetime,@account_amount float,@md float,@mc float,@mccode varchar(20)

-- ent_staus 0 已确认，1 冲款未完成， 2 已冲款

if (select count(*) from d_enteraccount where if_vouch=0 and ent_unique=@ent_unique) > 0
begin

select @cus_id=cus_id,@cus_name=cus_company,@ent_date=ent_date,@style_id=style_id,@ent_bankid=ent_bankid,@incom_bank=ent_bank,@kfbank=incom_kfbank,
@ord_currency=ord_currency,@account_currency = account_currency,@total=ent_value, @ominus=incom_ominus,@iminus = incom_iminus
from d_enteraccount where ent_unique=@ent_unique

set @digt1='收'+convert(varchar(50),rtrim(substring(@cus_name,1,50)))+'款'

if @ominus is null set @ominus=0
if @iminus is null set @iminus = 0

if @ent_bankid is null or len(ltrim(rtrim(@ent_bankid))) =0
select @ccode=code from d_paymenttermcode where term_id=@style_id and (type=0 or type=3)
else
select top 1 @ccode=ccode from d_bank where bank_code=@ent_bankid and curr_kind = @ord_currency --exch_name='美元'
if @ccode is null select @ccode = ''

--print @ccode

if len(ltrim(rtrim(@ccode)))=0 return
/*
if @ent_bankid is null or len(ltrim(rtrim(@ent_bankid))) =0
begin
select @ccode=code from d_paymenttermcode where term_id=@style_id and (type=0 or type=3)
end
else
begin
select top 1 @ccode=ccode from d_bank where bank_code=@ent_bankid and curr_kind='RMB' and i_style=0
end */
/*
if len(ltrim(rtrim(@ccode)))>0
begin
insert d_domesticenteraccountaccvouch(i_status,invo_id,cdigest,ccode,md,ccus_id,cn_id,cdefine10,dt_date,bpcsedit,bdeptedit,bitemedit)
select 0,convert(varchar(15),ent_unique),@digt1,@ccode,ent_value,cus_id,ent_num,ent_unique,convert(datetime,convert(varchar,ent_date,111)),1,1,1 from d_enteraccount where ent_unique=@ent_unique

insert d_domesticenteraccountaccvouch(i_status,invo_id,cdigest,ccode,mc,ccus_id,cdefine10,dt_date,bpcsedit,bdeptedit,bitemedit)
select 0,convert(varchar(15),ent_unique),@digt1,113101,ent_value,cus_id,ent_unique,convert(datetime,convert(varchar,ent_date,111)),1,1,1 from d_enteraccount where ent_unique=@ent_unique
end


*/

---取外币汇率

if @ord_currency = 'RMB'
select @nflat = 1
else
select top 1 @nflat=curr_exchangerate from c_currency where curr_kind=@ord_currency and year(curr_date)=year(@ent_date) and month(curr_date)=month(@ent_date)

if @account_currency = 'RMB'
select @nflat1 = 1
else
select top 1 @nflat1=curr_exchangerate,@curr_name=curr_name from c_currency where curr_kind=@account_currency and year(curr_date)=year(@ent_date) and month(curr_date)=month(@ent_date)


if @ord_currency <> @account_currency
select @account_amount = @total*@nflat/@nflat1
else
select @account_amount = @total

--DR：银行存款
insert d_domesticenteraccountaccvouch (invo_id,i_status,cdigest,ccode,md,md_f,cexch_name,nfrat,cdefine10,dt_date,ccus_id,bpcsedit,bdeptedit,bitemedit)
select convert(varchar(15),ent_unique),0,@digt1,@ccode,convert(decimal(18,2),@total*@nflat),case @account_currency when 'RMB' then 0 else convert(decimal(18,2),@account_amount) end,
case @account_currency when 'RMB' then null else @curr_name end,case @account_currency when 'RMB' then 0 else @nflat1 end,convert(varchar(15),ent_unique),convert(datetime,convert(varchar,ent_date,111)),@cus_id,1,1,1
from d_enteraccount where ent_unique=@ent_unique

--DR：外汇结算手续费
if @ominus > 0
begin
select @citemcode=citemcode from c_bank where bank_code=@kfbank
insert d_domesticenteraccountaccvouch(invo_id,i_status,cdigest,ccode,md,md_f,cexch_name,nfrat,citem_id,citem_class,cdefine10,dt_date,bpcsedit,bdeptedit,bitemedit)

select convert(varchar(15),ent_unique),0,@digt1,'6603030202',convert(decimal(18,2),@ominus*@nflat),case @account_currency when 'RMB' then 0 else convert(decimal(18,2),@ominus*@nflat/@nflat1) end,
case @account_currency when 'RMB' then null else @curr_name end,case @account_currency when 'RMB' then 0 else @nflat1 end,@citemcode,'03',
convert(varchar(15),ent_unique),convert(datetime,convert(varchar,ent_date,111)),1,1,1
from d_enteraccount where ent_unique=@ent_unique
end

--DR：国内结算手续费
if @iminus > 0
begin
select @citemcode=citemcode from c_bank where bank_code=@ent_bankid
insert d_domesticenteraccountaccvouch(invo_id,i_status,cdigest,ccode,md,citem_id,citem_class,cdefine10,dt_date,bpcsedit,bdeptedit,bitemedit)
select convert(varchar(15),ent_unique),0,@digt1,'66030301',@iminus*@nflat,@citemcode,'03',convert(varchar(15),ent_unique),convert(datetime,convert(varchar,ent_date,111)),1,1,1
from d_enteraccount where ent_unique=@ent_unique
end

--CR：应收帐款
select @mccode = ''
if @account_currency = 'USD'
select @mccode = '112202'
else
if @account_currency = 'JPY'
select @mccode = '112203'
else
if @account_currency = 'EUR'
select @mccode = '112204'
else
select @mccode = '112201'

insert d_domesticenteraccountaccvouch (invo_id,i_status,cdigest,ccode,mc,mc_f,cexch_name,nfrat,cdefine10,dt_date,ccus_id,bpcsedit,bdeptedit,bitemedit)
select convert(varchar(15),ent_unique),0,@digt1,@mccode,convert(decimal(18,2),(@total+@ominus+@iminus)*@nflat),
case @mccode when '112201' then 0 else convert(decimal(18,2),((@total+@ominus+@iminus)*@nflat)/@nflat1) end,
case @mccode when '112201' then null else @curr_name end,case @mccode when '112201' then 0 else @nflat1 end,convert(varchar(15),ent_unique),convert(datetime,convert(varchar,ent_date,111)),@cus_id,1,1,1
from d_enteraccount where ent_unique=@ent_unique

----DR：汇兑损益
select @md = sum(md) from d_domesticenteraccountaccvouch where invo_id = convert(varchar(15),@ent_unique)
select @mc = sum(mc) from d_domesticenteraccountaccvouch where invo_id = convert(varchar(15),@ent_unique)
if @md <> @mc
insert d_domesticenteraccountaccvouch (invo_id,i_status,cdigest,ccode,mc,cdefine10,dt_date,ccus_id,bpcsedit,bdeptedit,bitemedit)
select convert(varchar(15),ent_unique),0,@digt1,'660302',@md-@mc,convert(varchar(15),ent_unique),convert(datetime,convert(varchar,ent_date,111)),@cus_id,1,1,1
from d_enteraccount where ent_unique=@ent_unique

end

[eracn]

step1:
insert into d_enteraccount(ord_export,ent_id,ent_date,ent_style,cus_id,cus_company,ord_currency,account_currency,ent_value,ent_bank,ent_bankid,ent_memo,ent_status,ent_unique,ent_ava,ent_remain,if_vouch,style_id)
select ord_export,entapply_id,accept_date,term_desc,cus_id,cus_company,entapply_currency,entapply_currency,entapply_value,c.bank_name,a.entapply_bank,entapply_memo,-1,:ll_unique,'有效',entapply_value,0,style_id
from d_enteraccount_apply a join d_paymentterm b on a.style_id = b.term_id
left join c_bank c on a.entapply_bank = c.bank_code
where entapply_id = :ls_entapply_id;

step2:
DECLARE ms_me procedure for p_domesticenteraccountvouch
@ent_unique =:ll_unique,@ccode=:ls_ccode output using sqlca;
EXECUTE ms_me;
fetch ms_me into :ls_ccode;

step3:
if len(trim(ls_ccode))>=4 then
update d_enteraccount set ent_status = 0,if_vouch = 1 where ent_unique = :ll_unique;
...

[永生天地]

取完返回值，存储过程关闭了吗？
close 存储过程;

[wag_enu]

用查询分析器运行过存储过程没有?
可以先看它是什么返回结果.

[wag_enu]

[Quote=引用 12 楼 xys_777 的回复:]
取完返回值，存储过程关闭了吗？
close 存储过程;
[/Quote]

是的检查一下.

[eracn]

谢谢。

不知何时不小心把close procedure给屏蔽掉了，自己居然还没发现。

感谢各位，结贴去