none
关于SQL问题请高手解答,在线等,急 RRS feed

  • 问题

  • 原来使用的是SQL2000后来直接将SQL2000升级成SQL008

    在软件应用分页的时候出现错误,在用SQL跟踪后得到如何查询

    declare @p1 int
    set @p1=NULL
    declare @p9 int
    set @p9=NULL
    exec sp_prepexec @p1 output,N'@P1 int,@P2 int,@P3 varchar(4000),@P4 varchar(1000),@P5 int,@P6 int OUTPUT',N'declare @PageIndex int, @PageSize int, @Where nvarchar(4000), @OrderBy nvarchar(1000), @NewID int declare @PageLowerBound int, @PageUpperBound int, @RowsToReturn int, @r int, @TotalRows int, @Sql nvarchar(4000) SET Transaction Isolation Level Read UNCOMMITTED SELECT @PageIndex=@P1, @PageSize=@P2, @Where=@P3, @OrderBy=@P4, @NewID=@P5 SET @PageLowerBound = @PageSize * @PageIndex SET @PageUpperBound = @PageLowerBound + @PageSize + 1 SET @RowsToReturn = @PageSize * (@PageIndex + 1) + 1 SET @sql = N''SELECT @TotalRows=COUNT(A.ID) FROM myt_dshipapply A INNER JOIN
          myt_remployee C ON A.EMPID = C.EMPID INNER JOIN
          myt_customer D ON A.CUSID = D.ID INNER JOIN
          myt_bpaymethod p ON a.PAYMETHOD = p.PAYMETHOD INNER JOIN
          myt_V_sections V on A.SECID=V.SECTIONID LEFT OUTER JOIN
          myt_remployee F ON F.EMPID = A.BEEMPNAM
     left outer join (select b.empnam as logcreate,a.dodate as logcreatedate,a.recordid from myt_mlogs a inner join myt_remployee b on a.empid=b.empid where a.isnew=1 and a.formid=38) ly on ly.recordid=A.ID left outer join (select b.empnam as logmodify,a.dodate as logmodifydate,a.recordid from myt_mlogs a inner join myt_remployee b on a.empid=b.empid where a.isnew=0 and a.formid=38 and a.islast=1) lz on lz.recordid=A.ID  WHERE ''+@Where+N''  SELECT @r=@@ROWCOUNT'' Exec sp_executesql @sql, N''@TotalRows int OUTPUT,@r int OUTPUT'',@TotalRows OUTPUT,@r OUTPUT IF ''''<>'''' SET @TotalRows=@r SELECT @P6 = @TotalRows SET ROWCOUNT @RowsToReturn CREATE TABLE #PageIndexForMaster(IndexID int IDENTITY (1, 1) NOT NULL, RecordID int, SubID decimal(18,6) NULL, ThirdID decimal(18,6) NULL) IF @NewID<>-1 INSERT INTO #PageIndexForMaster(RecordID) VALUES(@NewID) SET @sql = N''INSERT INTO #PageIndexForMaster(RecordID) SELECT A.ID FROM myt_dshipapply A INNER JOIN
          myt_remployee C ON A.EMPID = C.EMPID INNER JOIN
          myt_customer D ON A.CUSID = D.ID INNER JOIN
          myt_bpaymethod p ON a.PAYMETHOD = p.PAYMETHOD INNER JOIN
          myt_V_sections V on A.SECID=V.SECTIONID LEFT OUTER JOIN
          myt_remployee F ON F.EMPID = A.BEEMPNAM
     left outer join (select b.empnam as logcreate,a.dodate as logcreatedate,a.recordid from myt_mlogs a inner join myt_remployee b on a.empid=b.empid where a.isnew=1 and a.formid=38) ly on ly.recordid=A.ID left outer join (select b.empnam as logmodify,a.dodate as logmodifydate,a.recordid from myt_mlogs a inner join myt_remployee b on a.empid=b.empid where a.isnew=0 and a.formid=38 and a.islast=1) lz on lz.recordid=A.ID  WHERE A.ID<>@NewID AND ''+@Where+N''  ''+@OrderBy Exec sp_executesql @sql, N''@NewID int'',@NewID SET @sql = N''SELECT A.ysf, A.shlxrdh, A.shlxr, A.ID, A.SHIPAPPID, A.SECID,V.IDNAME as SSORDID, A.CUSID,A.CMPID,A.EMPID, A.SHIPAPPDate, A.CRYUNT, A.Amount,
          A.RMBAMT, A.Mark, A.STATUS, C.EMPNAM, D.CUSNAM,A.transmod,A.CONTACT,A.PHONE,A.SHIPADDR,A.REASON,A.PAYMETHOD,A.Note,A.ProjID,
          A.BEEMPNAM,A.MASKDATE, F.EMPNAM AS BEEMPNAM2,p.DESCRIPT AS paymethods,A.SHIPDATE
    ,ly.logcreate,ly.logcreatedate,lz.logmodify,lz.logmodifydate  FROM #PageIndexForMaster,myt_dshipapply A INNER JOIN
          myt_remployee C ON A.EMPID = C.EMPID INNER JOIN
          myt_customer D ON A.CUSID = D.ID INNER JOIN
          myt_bpaymethod p ON a.PAYMETHOD = p.PAYMETHOD INNER JOIN
          myt_V_sections V on A.SECID=V.SECTIONID LEFT OUTER JOIN
          myt_remployee F ON F.EMPID = A.BEEMPNAM
     left outer join (select b.empnam as logcreate,a.dodate as logcreatedate,a.recordid from myt_mlogs a inner join myt_remployee b on a.empid=b.empid where a.isnew=1 and a.formid=38) ly on ly.recordid=A.ID left outer join (select b.empnam as logmodify,a.dodate as logmodifydate,a.recordid from myt_mlogs a inner join myt_remployee b on a.empid=b.empid where a.isnew=0 and a.formid=38 and a.islast=1) lz on lz.recordid=A.ID  WHERE 1=1
        AND A.ID=#PageIndexForMaster.RecordID AND   #PageIndexForMaster.IndexID>@PageLowerBound AND #PageIndexForMaster.IndexID<@PageUpperBound  ORDER BY #PageIndexForMaster.IndexID'' Exec sp_executesql @sql,N''@PageLowerBound int, @PageUpperBound int,@NewID int'',@PageLowerBound,@PageUpperBound,@NewID drop table #PageIndexForMaster
    ',0,10000,'1=1
       ',' ORDER BY A.SHIPAPPDATE DESC, A.ID DESC
    ',-1,@p9 output
    select @p1, @p9

    一直得不到任何的数据,但是在SQL2000上执行正常 请高手给个答案吧

    2010年7月22日 7:42

答案

  • sql都自己跟踪出来了

    下面就是抽丝剥茧,把sql一层一层的剥开分析

    直到发现没有数据的点。

    你给出的sql,没环境是没办法帮你调试的。


    family as water
    2010年7月23日 1:21
  • 1. rebuild 所有的 index.

    use 你的库名;

    exec sp_msforeachtable 'alter index all on ? rebuild;'

    2. 在 managent studio 中, 分析你的 T-SQL, 看看执行计划的那些地方资源开销大, 做对应的调整.

    2010年7月23日 4:29

全部回复