偶尔出现UPDATE不能实现的情况

sunsoncheng

        Cnn.BeginTrans


Update A  set A.切割数=isnull(A.切割数,0)- B.数量         

        from ((select 单号,序号,isnull(数量,0)  as  数量 from 切割流程表                     
                  WHERE  页码 = '148183' and 生产人员 is not null ) as B                        
                     left join   数据记录 A                  
              on (A.单号=B.单号 and A.序号=B.序号) )   


;  delete  计件工资   WHERE   页码 = '148183'  and    所做工艺 = '开介'  and      纯计件 = 0
;  delete 加工流程表  WHERE   页码 = '148183'  and    生产人员 is  null

;  delete  切割流程表 WHERE   页码 = '148183'



        Cnn.CommitTrans

1  是SQL2000数据库  通过VBA访问
2  使用了事务处理
3  不能确知  死锁的情况

语句的作用
目标 删除一张流程表
1 将数据汇总表中关于这张表的相关数据调整

2 将此流程表已产生的计件工资删除

3 将相关下工序的流程表删除

4 删除流程表本身

出现的情况
保存流程表，是反向操作的  从未发现问题

而执行删除操作时  偶尔出现第一步不能成功减除相关的数据的情况,
并且没有错误码提示反馈
也不出现回滚

其它的都能执行.

是偶尔，但又找不到规律

请各位帮忙分析下原因与解决方法

谢

sunsoncheng

要不要转到VBA开发呢

sunsoncheng

有好多其它的查询，
在四张表中联合查询出该工序的未完成数

表格应不大，都在100W条以内

一个工序就耗时10S左右，好多工序一起查就用时较多了，有时是分钟为单位的
各位
你们认为这个正常吗？

sunsoncheng

        SQL = " IF  EXISTS (SELECT * FROM dbo.sysindexes " & _
              "             WHERE id = OBJECT_ID(N'[dbo].[" & Arr(i, 1) & "]') AND name = N'order') " & _
              "   DROP INDEX [dbo].[" & Arr(i, 1) & "].[order]" & _


              " ; CREATE NONCLUSTERED INDEX [order] ON [dbo].[" & Arr(i, 1) & "](    [单号] ASC,    [序号] Asc) ON [PRIMARY]"

;表示一句，上面就是两句了
现在的问题是 DROP INDEX  为什么不是一句，不用分号呢？

sunsoncheng

i学写SQL语句
但错了
请指教
'这是只有单号没有序号的数据库中的表
StrSQL = "select @TableNeed =name from sysobjects where xtype='U' " & _
        " and id in (select distinct id from syscolumns where name='单号') " & _
        " and id not in (select distinct id from syscolumns where name='序号') " & _
        " and name in(select name from sysobjects where xtype='U')"
        
'将这些赋于了变量@TableNeed 并测试成功
  SQL = "declare @TableNeed  nvarchar(15) " & _
        " ; " & StrSQL
        
        
        
'利用这个变量时出错了
'将上述表的中singleorder索引删除，并重新处理单号的作为索引
  SQL = "declare @TableNeed  nvarchar(15) " & _
        " ; " & StrSQL & _
        " ; IF  EXISTS (SELECT * FROM dbo.sysindexes " & _
        "             WHERE id = OBJECT_ID(@TableNeed) AND name = N'singleorder') " & _
        "   DROP INDEX @TableNeed.[singlorder]" & _
        " ; CREATE NONCLUSTERED INDEX [singlorder] ON @TableNeed([单号]  ) "

sunsoncheng

sunsoncheng 发表于 2018-1-18 15:47
有好多其它的查询，
在四张表中联合查询出该工序的未完成数

主要是索引不合理做成的。有些还没有索引

重设了相关的索引后，在10秒内能出数据

所以才有了后面的问题  想在一定的时候将索引重写

sunsoncheng

    Dim StrSQL$, Arr, i%, JoinSQL$
    Dim SQL$
   
    '所有用户表，查有否有单号与序号的表
    '这是所有有单号同时又有序号的表not
    StrSQL = "select name from sysobjects where xtype='U' " & _
             " and id in (select distinct id from syscolumns where name='单号') " & _
             " and id not in (select distinct id from syscolumns where name='序号') " & _
             " and name in(select name from sysobjects where xtype='U')"
   
    Arr = RsToArr(StrSQL)

    For i = LBound(Arr) + 1 To UBound(Arr)
        '所有流程表加入单号序号 且名为Order的索引
        SQL = " IF  EXISTS (SELECT * FROM dbo.sysindexes " & _
              "             WHERE id = OBJECT_ID(N'[dbo].[" & Arr(i, 1) & "]') AND name = N'singleorder') " & _
              "   DROP INDEX [dbo].[" & Arr(i, 1) & "].[singleoorder]" & _
              " ; CREATE NONCLUSTERED INDEX [singleoorder] ON [dbo].[" & Arr(i, 1) & "]([单号] ) "
        JoinSQL = JoinMySQL(JoinSQL, SQL)
    Next
   
    Call ExecuteSQL(JoinSQL, "重建所有只有单号的表的索引", 1)

上面的做法完全能达到要求，只是想用一段SQL语句能否达到要求呢
   

sunsoncheng

在查询分析器里摸得差不多了
差也许是最后一步
use 原数
go
--有与没有这个GO 得出完全不同的结果
--定义游标
DECLARE SingleOrderTableCursor INSENSITIVE SCROLL CURSOR
FOR
select name from sysobjects where xtype='U'
             and id in (select distinct id from syscolumns where name='单号')
            and id not in (select distinct id from syscolumns where name='序号')

--打开游标
OPEN SingleOrderTableCursor
declare @TableNeed nvarchar(15)--定义相应表名变量

FETCH NEXT FROM SingleOrderTableCursor INTO @TableNeed-- 下一个位置
--没有这个下移结果是空--if这里如何办呢
WHILE (@@FETCH_STATUS = 0)
BEGIN

  IF  EXISTS (SELECT 1 FROM dbo.sysindexes WHERE id = OBJECT_ID(@TableNeed)  AND name = N'singleoorder') --PRINT @TableNeed
        DROP INDEX @TableNeed.[singloorder]

            FETCH NEXT FROM SingleOrderTableCursor INTO @TableNeed-- 下一个位置
End

--关闭游标
Close SingleOrderTableCursor
--释放游标
DEALLOCATE SingleOrderTableCursor


@TableNeed.[singloorder]

这里的语法错误码  这个变量如何写呢

sunsoncheng

这是在查询分析器上已成功的做法
但如何通过文本EXEC呢
use 原数
go
--有与没有这个GO 得出完全不同的结果
--定义游标
DECLARE SingleOrderTableCursor INSENSITIVE SCROLL CURSOR
FOR
select name from sysobjects where xtype='U'
             and id in (select distinct id from syscolumns where name='单号')
            and id not in (select distinct id from syscolumns where name='序号')
--打开游标
OPEN SingleOrderTableCursor
declare @TableNeed   nvarchar(20)--定义相应表名变量
declare @MyIndexName nvarchar(20)--定义相应索引名
declare @IndexCOLName   nvarchar(20)--定义相应索引所在的列名
set  @MyIndexName ='singleorder'
set  @IndexCOLName='单号'--'单号],[序号'这样就是新的内容

FETCH NEXT FROM SingleOrderTableCursor INTO @TableNeed-- 下一个位置
--没有这个下移结果是空--if这里如何办呢
WHILE (@@FETCH_STATUS = 0)
BEGIN

  IF  EXISTS (SELECT 1 FROM dbo.sysindexes WHERE id = OBJECT_ID(@TableNeed)  AND name =  @MyIndexName)
        --原来有索引就重建，没有就新建
        begin
        --重建索引
        exec('CREATE INDEX ['+@MyIndexName+'] ON '+@TableNeed+' (['+@IndexCOLName+'])  WITH DROP_EXISTING ON [PRIMARY]')       
        --exec('DROP INDEX '+@TableNeed+'.['+@MyIndexName+']')
        --这是直接删除，没有必要
        end
  else--没有就新建
        begin
        --新建索引
        exec('CREATE NONCLUSTERED INDEX ['+ @MyIndexName+'] ON '+@TableNeed+' (['+@IndexCOLName+'] )')
        end

  FETCH NEXT FROM SingleOrderTableCursor INTO @TableNeed-- 下一个位置

end

--关闭游标
Close SingleOrderTableCursor
--释放游标
DEALLOCATE SingleOrderTableCursor

sunsoncheng

作为一个学习的经历吧

  Sql = "use 原数  " & _
        " ;DECLARE SingleOrderTableCursor INSENSITIVE SCROLL CURSOR FOR " & _
        "       select name from sysobjects where xtype='U' " & _
        "                   and id                   in (select distinct id from syscolumns where name='单号') " & _
        "                   and id " & SingleSql & " in (select distinct id from syscolumns where name='序号') " & _
        " ;OPEN SingleOrderTableCursor " & _
        " ;declare @TableNeed   nvarchar(20),@MyIndexName nvarchar(20), @IndexCOLName   nvarchar(20)" & _
        " ;set  @MyIndexName ='" & MyIndexName & "'" & _
        " ;set  @IndexCOLName='" & IndexCOLName & "'" & _
        " ;FETCH NEXT FROM SingleOrderTableCursor INTO @TableNeed"
        
     '这一段的断句就出了问题
    Sql = Sql & " ;WHILE (@@FETCH_STATUS = 0)" & _
                "  begin" & _
                "       IF  EXISTS (SELECT 1 FROM dbo.sysindexes WHERE id = OBJECT_ID(@TableNeed)  AND name =  @MyIndexName) " & _
                "           begin " & _
                "           exec('CREATE INDEX ['+@MyIndexName+'] ON '+@TableNeed+' (['+@IndexCOLName+'])  WITH DROP_EXISTING ON [PRIMARY]')" & _
                "           End " & _
                "        Else " & _
                "           begin" & _
                "           exec('CREATE NONCLUSTERED INDEX ['+ @MyIndexName+'] ON '+@TableNeed+' (['+@IndexCOLName+'] )')" & _
                "           End" & _
                "    ; FETCH NEXT FROM SingleOrderTableCursor INTO @TableNeed" & _
                " End"
            
    Sql = Sql & " ;Close SingleOrderTableCursor" & _
                " ;DEALLOCATE SingleOrderTableCursor"

    Call ExecuteSQL(Sql, Msg, 1)