多次执行动态跨服务器操作出现的奇怪问题

Question

alter procedure [dbo].CopyProdCRMToK3
(
@FID int, ---K3Bos单据内码
@TaskID int, ---任务ID
@DetailName nvarchar(200), ---明细表名
@ProdTable   nvarchar(200)---产品表名
)
As
Begin
Declare @ProdID int ---产品的CRM编号
Declare @ProdNum int --产品数量
Declare @ProdPrice float ---产品价格
Declare @BatchNo nvarchar(50) ---赠品批号
DEclare @ReturnInfo nVarchar(100) --退换货信息
Declare @GiftRequest nVarchar(50) --赠品要求
Declare @K3ProdID int --K3的产品内码
Declare @ProdK3 nvarchar(50) --K3产品的代码
Declare @UnitID   int--单位K3内码
Declare @Index   int ---明细档序号
Declare @SQLStr   nvarchar(2000)---SQL语句长度
DECLARE @SQLParam nvarchar(2000) ---变量类型定义长度
Declare @SQLStr1   nvarchar(2000)---SQL语句长度
DECLARE @SQLParam1 nvarchar(2000) ---变量类型定义长度--因为目前无法使用动态SQL查询结果赋值给游标，所以绕弯
delete from TempTableForCursor
Set @SQLStr =N' insert into TempTableForCursor (prod_id,RowIndex,prod_volume,pino,returnprodinfo,prodrequest,
          FItemID,prod_last_price,fsaleunitid)
          Select Relation.prod_id,ROW_NUMBER() OVER (order by Relation.prod_id) as RowIndex,
          prod_volume,pino,returnprodinfo,prodrequest,K3.FItemID,prod_last_price,K3.fsaleunitid 
          from rel_task_prod Relation 
          left join tc_product main on Relation.owner_id =main.owner_id and Relation.prod_id = main.prod_id 
          Left join tc_prod_attribute attr on main.prod_id=attr.prod_id 
          left join '+@ProdTable+' K3 on K3.Fnumber =attr.prod_char001
          where Relation.owner_id =1 and Relation.task_id=@TaskID'
Set @SQLParam =N'@TaskID int'
select @SQLStr,@FID,@ProdTable,@TaskID
exec sp_executesql @SQLStr,@SQLParam,@TaskID=@TaskIDSet @SQLStr1 =N' Insert into '+@DetailName+' ([FID],[FIndex],[fitemid],[funitid],[FQty],[FBatchNo],FNotes,Fprice)
         values(@FID,@Index,@K3ProdID,@UnitID,@ProdNum,@BatchNo,@ReturnInfo,@ProdPrice)'
Set @SQLParam1 =N'@FID int,@Index int,@K3ProdID int,@UnitID int,@ProdNum int,@BatchNo nvarchar(50),@ReturnInfo nVarchar(100),@ProdPrice float'
--
Declare   CRMProd1 cursor for
   select * from TempTableForCursor
open CRMProd1
Fetch Next From CRMProd1 Into @ProdID,@Index,@ProdNum,@BatchNo,@ReturnInfo,@GiftRequest,@K3ProdID,@ProdPrice,@UnitID
While(@@fetch_status =0)
   Begin
    --插入K3明细档
    set @ReturnInfo =@ReturnInfo+isnull(@GiftRequest,'')
    select @SQLStr,@FID,@Index,@K3ProdID,@UnitID,@ProdNum,@BatchNo,@ReturnInfo,@ProdPrice,@UnitID
    exec sp_executesql @SQLStr1,@SQLParam1,@FID=@FID,@Index=@Index,@K3ProdID=@K3ProdID,
             @UnitID=@UnitID,@ProdNum=@ProdNum,@BatchNo=@BatchNo,@ReturnInfo=@ReturnInfo,@ProdPrice=@ProdPrice
   
    Fetch Next From CRMProd1 Into @ProdID,@Index,@ProdNum,@BatchNo,@ReturnInfo,@GiftRequest,@K3ProdID,@ProdPrice,@UnitID
   End
CLose CRMProd1
DEALLOCATE CRMProd1
End
go这个问题真的很怪，若是这个存储过程单独执行是ok的，但是被下面这个存储过程调用就会提示事务已取消，怀疑是不是微软有对动态执行SQL优化方面有限制还是SQL2005的BUG,希望高手帮忙解决一下，谢谢！alter procedure AddNewBosRow
(
@BillNo    nvarchar(50),---Bos单据编号
@CustID    int,---客户K3内码
@BosType   nVarchar(50),--Bos单据类型
@EmpName nVarchar(50),--申请员工姓名
@TaskID    int,---   服务任务ID
@MasterName nvarchar(100), ---主表名
@DetailName nvarchar(100), ---明细表名
@ProdTable nvarchar(100),---产品明细
@FClassTypeID int,     ---Bos单据类型内码
@FID     int output --Bos单据的内码 
)
as
begin
Declare @SQLStr   nvarchar(2000)---SQL语句长度
DECLARE @SQLParam nvarchar(1000) ---变量类型定义长度
--先查FID
Set @SQLStr =N' Select @FID=Max(FID)+1 From '+@MasterName+' Where FClassTypeID=@FClassTypeID'
Set @SQLParam =N'@FID int output,@FClassTypeID int'
exec sp_executesql @SQLStr,@SQLParam,@FID=@FID output,@FClassTypeID=@FClassTypeID
--插入主档
Set @SQLStr =N' Insert into '+@MasterName+' (FID,FClassTypeID,FBillNo,fcustid,fbilltype,fnote,FDate,Fbiller,Fcrmer)
         Values(@FID,@FClassTypeID,@BillNo,isnull(@CustID,0),@BosType,'''',getdate(),1,@EmpName)'
Set @SQLParam =N'@FID int ,@FClassTypeID int, @BillNo nvarchar(50),@CustID int,@BosType nVarchar(50),@EmpName nVarchar(50)'
exec sp_executesql @SQLStr,@SQLParam,@FID=@FID,@FClassTypeID=@FClassTypeID,@BillNo=@BillNo,
           @CustID=@CustID,@BosType=@BosType,@EmpName=@EmpName
-- ---插入明细
select @SQLStr,@FID,@FClassTypeID,@FClassTypeID,@BillNo,@CustID,@BosType,@EmpName 
exec CopyProdCRMToK3 @FID,@TaskID,@DetailName,@ProdTableend
go

Answer 1

Set @SQLStr =N' Select @FID=Max(FID)+1 From @MasterName Where FClassTypeID=@FClassTypeID' 
Set @SQLParam =N'@FID int output,@FClassTypeID int' 这些地方不用加'+'这些东西,因为你是用SP_EXECUTESQL执行

Answer 2

你自己试过吗,单独执行那些SQL

Answer 3

太长了，头晕，提个醒，动态语句可以直接定义游标啊
比如
exec ('declare c1 cursor for  select  * from T')
open c1
...
close c1
deallocate c1第二点，在定义游标时，有用到 动态跨服务器(serverlink）的最后对游标进行只读定义，
可以看下我的blog
http://blog.csdn.net/playwarcraft/archive/2009/03/13/3986908.aspx

Answer 4

我测试出来的结果好像因为在分布式事务之内不允许Insert into A.DBO.XX Select * From B.DBo.XX。感觉若是这样话，好像不合理，有什么依据呢？因为我把这段放到事务外围，执行结果就OK！

Answer 5

关键是我要跨服务器select,Insert，而且这些都写在触发器中，用一个事务包在里面。所以才会有这么长，我也喜欢短，但是已经不能再细分割了（感觉）。因为我要用SQL实现程序实现的功能，所以比较别扭！

Answer 6

我这个是表名变量，所以要这样写，因为动态SQL不支持字段名、表名，所以才会混合用

Answer 7

你用EXEC执行,你那样应该会提示要声明表变量吧,你又要有输出参数,麻烦

Answer 8

呵呵,没办法,这是业务要求,现在还没有优化,不过也不知道怎么优化,以前对SQL掌握不是很熟悉...所以有一些都是现学现卖