難道這真的是SQLServer的BUG么?

显示全部楼层 · 2012-5-22 15:05:35

use xxx
declare @KeepDay as int
set @KeepDay = 90

delete PURCHASEORDERSITEMDETAIL with (rowlock)
from PURCHASEORDERSITEMDETAIL a(nolock), [NAB2BEDIDBARC].xxx_ARC.dbo.PURCHASEORDERSITEMDETAIL b

where a.lasteditdt < getdate() -@KeepDay
and a.[SENDID] = b.[SENDID]
and a.[RECEID] = b.[RECEID]
and a.[MESGID] = b.[MESGID]
and a.[ORDNUM] = b.[ORDNUM]
and a.[ITMNUM] = b.[ITMNUM]
and a.[SUBITM] = b.[SUBITM]
and (exists(select d.ordnum from asnmain c(nolock),asnitem d(nolock)

where c.batchno = d.batchno and c.sendid = d.sendid and c.receid = d.receid and c.loadid = d.loadid and c.pallet_id = d.pallet_id

and c.ackstatus = 'Y'and d.ordnum=a.ordnum)

or
exists(select e.ordnum from purchaseordersmain e(nolock)

where e.lasteditdt < getdate() -@KeepDay

and uploadflag = 'F' and e.ordnum=a.ordnum) )
將上面這段sql語句拿到查詢分析器中檢查語法沒錯,但是一運行就報錯.
Server: Msg 8624, Level 16, State 1, Line 5
Internal SQL Server error.
在網上看到也有人說這個問題但是好像還是沒解決..
環境:SQLServer 2000 企業版...2055

千问 · 2012-5-22 15:05:35

delete PURCHASEORDERSITEMDETAIL with (rowlock) -----------这里是不是要用 a这个别名
from PURCHASEORDERSITEMDETAIL a(nolock), [NAB2BEDIDBARC].xxx_ARC.dbo.PURCHASEORDERSITEMDETAIL b
where a.lasteditdt < getdate() -@KeepDay
and a.[SENDID] = b.[SENDID]
and a.[RECEID] = b.[RECEID]
and a.[MESGID] = b.[MESGID]
and a.[ORDNUM] = b.[ORDNUM]
and a.[ITMNUM] = b.[ITMNUM]
and a.[SUBITM] = b.[SUBITM]
and (exists(select d.ordnum from asnmain c(nolock),asnitem d(nolock)
where c.batchno = d.batchno and c.sendid = d.sendid and c.receid = d.receid and c.loadid = d.loadid and c.pallet_id = d.pallet_id
and c.ackstatus = 'Y'and d.ordnum=a.ordnum)
or
exists(select e.ordnum from purchaseordersmain e(nolock)

where e.lasteditdt < getdate() -@KeepDay

and uploadflag = 'F' and e.ordnum=a.ordnum) )

千问 · 2012-5-22 15:05:35

delete PURCHASEORDERSITEMDETAIL with (rowlock)
from PURCHASEORDERSITEMDETAIL a(nolock),
感觉这边的锁加的有问题，一边是提示加rowlock，一边又要nolock，直接把MSSQL搞晕了吧。去掉这两个Hints看看。
：）

千问 · 2012-5-22 15:05:35

delete PURCHASEORDERSITEMDETAIL with (rowlock)
把 with (rowlock) 去掉试试

千问 · 2012-5-22 15:05:35

想不明白，delete 表的数据，需要 rowlock 吗？

千问 · 2012-5-22 15:05:35

以前這樣寫都是沒問題的...