none
当前命令发生了严重错误。应放弃任何可能产生的结果。 RRS feed

  • 问题

  • 从AMD64位的SQLServer2005服务器数据库通过DBLink向Intel32位的SQL Server2000的数据库服务器插入数据.是使用存储过程产生脚本,然后向目标数据库插入数据.

    但是一执行的时候,就报错:"当前命令发生了严重错误。应放弃任何可能产生的结果。"

    在网上有找到,说要执行DBCC CHECKTABLE命令,那么这个命令如果后面只加表的名称,其他默认的选项是什么?

    我关注的是:会不会修复数据.如果我不想修复数据,那么应该使用什么选项?


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年10月24日 2:59

全部回复

  • 版本分别是:Microsoft SQL Server 2005 - 9.00.4035.00 (X64)   Nov 24 2008 16:17:31   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

     

    Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

    至于OS是什么版本 就不得而知了


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

    • 已编辑 Wison-Ho 2011年10月24日 3:01
    2011年10月24日 3:01
  • CREATE  PROCEDURE [dbo].[SP_US_Metro_Arc]
     
    AS

    SET NOCOUNT ON
    SET XACT_ABORT ON

     

    DECLARE @_Sql  NVARCHAR(4000),
      @_ColListA VARCHAR(4000),
      @_ColListB VARCHAR(4000),
      @_TabName VARCHAR(50),
      @_ArcPrefix VARCHAR(50),
      @_ProdPrefix VARCHAR(50),
      @_SortNo INT,
      @_TabCount INT,
      @_TabType VARCHAR(20),
      @_TransactionId VARCHAR(32)
      
    DECLARE @_Error NVARCHAR(1000),
      @_ErrorSeverity INT,
      @_LogFilePath VARCHAR(100),
      @_ShellCmd NVARCHAR(1000)
      
    -- Create two temp Table for archive detail log
    CREATE TABLE #_Temp_Log
    (
     TABLE_NAME VARCHAR(100),
     AFFECT_QTY INT,
     ACTION_NAME VARCHAR(50),
     TXN_TIME DATETIME,
     CACHED_SQL NTEXT
    )

    DECLARE @_Temp_Log TABLE
    (
     TABLE_NAME VARCHAR(100),
     AFFECT_QTY INT,
     ACTION_NAME VARCHAR(50),
     TXN_TIME DATETIME,
     CACHED_SQL NTEXT
    )

     
    -- Create temp table
    CREATE TABLE #_TableLists(
     SeqNo  INT IDENTITY(1, 1),
     TabName VARCHAR(50) PRIMARY KEY,
     TabType VARCHAR(20) DEFAULT 'SWS')
     
    INSERT #_TableLists
    (TabName)
    SELECT 'LotTestSWS_BAND_UNIFORMSummary'
    UNION ALL SELECT 'LotTestSWS_BW'
    UNION ALL SELECT 'LotTestSWS_BW_CC'
    UNION ALL SELECT 'LotTestSWS_BW_CCSummary'
    UNION ALL SELECT 'LotTestSWS_BW_P1P2'
    UNION ALL SELECT 'LotTestSWS_BW_P1P2Summary'
    UNION ALL SELECT 'LotTestSWS_BWSummary'
    UNION ALL SELECT 'LotTestSWS_Coupler'
    UNION ALL SELECT 'LotTestSWS_DarkIL'
    UNION ALL SELECT 'LotTestSWS_DarkILSummary'
    UNION ALL SELECT 'LotTestSWS_DeltaIL_Accuracy'
    UNION ALL SELECT 'LotTestSWS_ExtractParms'
    UNION ALL SELECT 'LotTestSWS_IL_PDL_RPL'
    UNION ALL SELECT 'LotTestSWS_IL_PDL_RPLSummary'
    UNION ALL SELECT 'LotTestSWS_ISO_GW'
    UNION ALL SELECT 'LotTestSWS_ISO_GW_EXT7'
    UNION ALL SELECT 'LotTestSWS_ISO_GW_EXT7Summary'
    UNION ALL SELECT 'LotTestSWS_ISO_GWSummary'
    UNION ALL SELECT 'LotTestSWS_ISO_IB'
    UNION ALL SELECT 'LotTestSWS_ISO_IBSummary'
    UNION ALL SELECT 'LotTestSWS_IsoAVG_GW'
    UNION ALL SELECT 'LotTestSWS_IsoAVG_GWSummary'
    UNION ALL SELECT 'LotTestSWS_IsoAVG_IB'
    UNION ALL SELECT 'LotTestSWS_IsoAVG_IBSummary'
    UNION ALL SELECT 'LotTestSWS_WL_Grid'
    UNION ALL SELECT 'LotTestSWS_WL_GridSummary'

    INSERT #_TableLists
    SELECT 'LotTestDQPSK_Resistors', 'DQPSK'
    UNION ALL SELECT 'LotTestDQPSK_OptSummary', 'DQPSK'
    UNION ALL SELECT 'LotTestDQPSK_OptDetail', 'DQPSK'
    UNION ALL SELECT 'LotTestDQPSK_ElecSummary', 'DQPSK'
    UNION ALL SELECT 'LotTestDQPSK_ElecDetail', 'DQPSK'
    UNION ALL SELECT 'LotTestDQPSK_DriveSummary', 'DQPSK'

    INSERT #_TableLists
    SELECT 'LotPkgLotNumber', 'LOTPKG'
    UNION ALL SELECT 'LotPkgLotNumberXStep', 'LOTPKG'
    UNION ALL SELECT 'LotPkgHist', 'LOTPKG'
    UNION ALL SELECT 'LotPkgHistDetail', 'LOTPKG'

    INSERT #_TableLists
    SELECT 'LotTest', 'LOTTEST'
    UNION ALL SELECT 'LotTestAttribute', 'LOTTEST'
    UNION ALL SELECT 'LotTestMIXER', 'LOTTEST'
    UNION ALL SELECT 'LotTestOXSW', 'LOTTEST'
    UNION ALL SELECT 'LotTestCPLR', 'LOTTEST'
    UNION ALL SELECT 'LotTestSVT', 'LOTTEST'
    UNION ALL SELECT 'LotTestVOA', 'LOTTEST'
    UNION ALL SELECT 'LotTestDQPSK', 'LOTTEST'
    UNION ALL SELECT 'LotTestHeader', 'LOTTEST'
    UNION ALL SELECT 'LotTestTAP','LOTTEST'

    INSERT #_TableLists
    SELECT 'LotMetrology', 'BYWAFER'
    UNION ALL SELECT 'LotQualF', 'BYWAFER'


    SELECT @_TabCount = COUNT(1),
      @_SortNo = 1,
      @_TransactionId = REPLACE(NEWID(), '-', '')
    FROM #_TableLists

     

    SELECT @_ArcPrefix='NEO05.MetroArchive3',@_ProdPrefix='MetroProdBE'
    -- Insert Archive Log Table
    INSERT dbo.ARC_LOG
    (
      TXN_ID,TXN_TYPE,START_TIME,DURATION,STATUS,ARC_CONDTION
    )
    SELECT @_TransactionId, 'MetroARC', GETDATE(), 0, 'BEGIN',
      'Archive inactive Chips'
     
     
    BEGIN TRY

     BEGIN TRAN @_TransactionId
      
      -- Get need to archive Chips

      SELECT TOP 10 PKGLOTNUMBERID,LOTID,WAFERID,CHIPID,DATE_SYS into #ARC_CHIP  from W_D_ARCHIVE_CHIPS where STATUS='TO_ARCHIVE_DB' ORDER BY Pkg_TimeChanged --DESC
      
      --==== Loop all table for inserting data
      WHILE @_SortNo <= @_TabCount
      BEGIN
       SELECT @_TabName = TabName,
         @_TabType = TabType
       FROM #_TableLists
       WHERE SeqNo = @_SortNo
       
       
       SELECT @_ColListA = '', @_ColListB = ''
        -- Build column list
       SELECT @_ColListA = @_ColListA +'['+ [name] + '], ',
        @_ColListB = @_ColListB + 'P.[' + [name] + '], '
        
         FROM dbo.syscolumns  -- Target database columns list
         WHERE [id] = OBJECT_ID('dbo.' + @_TabName) AND xtype<>189
         ORDER BY colid
         --FOR     XML PATH('')
       
       -- Remove last ','
       IF ISNULL(@_ColListA, '') <> ''
       BEGIN
        -- Remove last ','
        SET @_ColListA = '(' + LEFT(@_ColListA, LEN(@_ColListA) - 1) + ')'
        SET @_ColListB = LEFT(@_ColListB, LEN(@_ColListB) - 1)
       END
       ELSE
       BEGIN
        SET @_Error = 'Cannot find the table ' + @_TabName + ' Columns'
        RAISERROR('%s', 16, 1, @_Error)
       END

          -- Archive Data from product database to Archive
       IF @_TabType = 'LotPKG'
       BEGIN
        
        SET @_Sql =N'
           
        INSERT @ArcPrefix@.dbo.@TabName@  @ColListA@
        SELECT @ColListB@
        FROM @ProdPrefix@.dbo.@TabName@ AS P
          WHERE P.PkgLotnumberid IN (SELECT PkgLotnumberid FROM #ARC_CHIP)
               
        '
       
       END
       ELSE IF @_TabType = 'Lottest'
       BEGIN
       
        SET @_Sql =N' 
        
        INSERT @ArcPrefix@.dbo.@TabName@   @ColListA@
        SELECT @ColListB@
        FROM @ProdPrefix@.dbo.@TabName@ AS P 
          INNER JOIN #ARC_CHIP AS T
          ON P.Lotid = T.Lotid AND P.Waferid = T.Waferid AND P.Chipid = T.Chipid
                 
        '
       END
       ELSE IF @_TabType = 'DQPSK'
       BEGIN
        
        SET @_Sql = N'
        
        INSERT @ArcPrefix@.dbo.@TabName@   @ColListA@
        SELECT @ColListB@ from @ProdPrefix@.dbo.@TabName@ AS P 
        INNER JOIN @ProdPrefix@.dbo.LotTestDQPSK as L
         ON P.Lottestid=L.Lottestid AND P.Lotid=L.lotid
        INNER JOIN #ARC_CHIP AS T
         ON L.Lotid=T.Lotid AND L.Waferid=T.Waferid AND L.Chipid=T.Chipid
       
        '
        
       END
       ELSE IF @_TabType = 'BYWAFER'
       BEGIN
        
        SET @_Sql = N'
        
        INSERT @ArcPrefix@.dbo.@TabName@   @ColListA@
        SELECT @ColListB@ from @ProdPrefix@.dbo.@TabName@ AS P 
        INNER JOIN #ARC_CHIP AS T
         ON P.Lotid=T.Lotid AND P.Waferid=T.Waferid
       
        '
        
       END
       ELSE IF @_TabType = 'SWS'
       BEGIN
       
        SET @_Sql =
        N' 
        
        INSERT @ArcPrefix@.dbo.@TabName@   @ColListA@
        SELECT @ColListB@
        FROM @ProdPrefix@.dbo.@TabName@ AS P 
          INNER JOIN @ProdPrefix@.dbo.Lottestheader AS L
           ON P.Lottestid = L.Lottestid AND P.Lotid = L.Lotid
          INNER JOIN  #ARC_CHIP AS T
           ON L.Lotid=T.Lotid AND L.Waferid=T.Waferid AND L.Chipid=T.Chipid 
           
            '
       END
       ELSE
       BEGIN
        -- Ignore sql
        SET @_Sql = ''
       END

       SET @_Sql = REPLACE(@_Sql, '@ProdPrefix@', @_ProdPrefix)
       SET @_Sql = REPLACE(@_Sql, '@ArcPrefix@', @_ArcPrefix)
       SET @_Sql = REPLACE(@_Sql, '@TabName@', @_TabName)
       SET @_Sql = REPLACE(@_Sql, '@ColListA@', @_ColListA)
       SET @_Sql = REPLACE(@_Sql, '@ColListB@', @_ColListB)
       
      
       PRINT @_Sql
       EXEC (@_Sql)
       
       INSERT #_Temp_Log
          SELECT @_TabName, @@ROWCOUNT, 'INSERT_ARC', GETDATE(), @_Sql
        
       SET @_SortNo = @_SortNo + 1
      
      END
      
      
    COMMIT TRAN @_TransactionId
     
     --PRINT '--============BEGIN DELETE=========================================='
      
     SELECT @_TabCount = COUNT(1),
       @_SortNo = 1
     FROM #_TableLists
     
     --BEGIN TRAN @_TransactionId
     
     --==== Loop all table for deleting data
     WHILE @_SortNo <= @_TabCount
     BEGIN
      
      SELECT @_TabName = TabName,
        @_TabType = TabType
      FROM #_TableLists
      WHERE SeqNo = @_SortNo
      
       
      --================ Delete by #_Temp_Arc
      IF @_TabType = 'LotPKG'
      BEGIN
       SET @_Sql =
       N'  
       DELETE @ProdPrefix@.dbo.@TabName@ 
       FROM @ProdPrefix@.dbo.@TabName@ AS P
               WHERE P.PkgLotnumberid IN (SELECT PkgLotnumberid FROM #ARC_CHIP)
                       
         '
      END
      ELSE IF @_TabType = 'SWS'
      BEGIN
      
       SET @_Sql =
       N' 
       DELETE @ProdPrefix@.dbo.@TabName@ 
       FROM @ProdPrefix@.dbo.@TabName@ AS P 
         INNER JOIN @ProdPrefix@.dbo.Lottestheader AS L
          ON P.Lottestid = L.Lottestid AND P.Lotid = L.Lotid
         INNER JOIN  #ARC_CHIP AS T
             ON T.Lotid = L.Lotid AND T.Waferid = L.Waferid AND T.Chipid=L.Chipid
          
       '
      END
      ELSE IF @_TabType = 'Lottest'
      BEGIN
      
       SET @_Sql =
       N' 
       DELETE @ProdPrefix@.dbo.@TabName@ 
       FROM @ProdPrefix@.dbo.@TabName@ AS P
                INNER JOIN  #ARC_CHIP AS T
                ON P.Lotid = T.Lotid AND P.Waferid = T.Waferid AND P.Chipid = T.Chipid
       '
      END
      ELSE IF @_TabType = 'BYWAFER'
      BEGIN
      
       SET @_Sql =
       N' 
       DELETE @ProdPrefix@.dbo.@TabName@ 
       FROM @ProdPrefix@.dbo.@TabName@ AS P
                INNER JOIN  #ARC_CHIP AS T
                ON P.Lotid = T.Lotid AND P.Waferid = T.Waferid
       '
      END
      ELSE IF @_TabType = 'DQPSK'
      BEGIN
      
       SET @_Sql =
       N' 
       DELETE @ProdPrefix@.dbo.@TabName@ 
       FROM @ProdPrefix@.dbo.@TabName@ AS P 
       INNER JOIN @ProdPrefix@.dbo.LotTestDQPSK as L
           ON P.Lottestid=L.Lottestid AND P.Lotid=L.lotid
       INNER JOIN #ARC_CHIP AS T
           ON L.Lotid=T.Lotid AND L.Waferid=T.Waferid AND L.Chipid=T.Chipid
       
       '
      END
      
      SET @_Sql = REPLACE(@_Sql, '@ProdPrefix@', @_ProdPrefix)
      SET @_Sql = REPLACE(@_Sql, '@ArcPrefix@', @_ArcPrefix)
      SET @_Sql = REPLACE(@_Sql, '@TabName@', @_TabName)
      
       
      PRINT @_Sql
      EXEC (@_Sql)
      
      INSERT #_Temp_Log
      SELECT @_TabName, @@ROWCOUNT, 'DELETE_PROD', GETDATE(), @_Sql
      
       
      SET @_SortNo = @_SortNo + 1
      
     END 
          
     --COMMIT TRAN @_TransactionId
      --Insert archive detail Log temp table
     INSERT @_Temp_Log
     SELECT *
     FROM #_Temp_Log
      
     -- If archive successful, update Archive Log
     UPDATE dbo.ARC_LOG WITH (ROWLOCK)
     SET  DURATION = DATEDIFF(ss, START_TIME, GETDATE()),
       STATUS = 'SUCCESS'
     WHERE TXN_ID = @_TransactionId
       


     UPDATE W_D_ARCHIVE_CHIPS
     SET STATUS='FINISH_TO_ARCHIVE_DB',Date_TM=GETDATE(),Txn_ID=@_TransactionId
     FROM W_D_ARCHIVE_CHIPS
     INNER JOIN #ARC_CHIP
     ON W_D_ARCHIVE_CHIPS.PKGLOTNUMBERID =#ARC_CHIP.PKGLOTNUMBERID AND W_D_ARCHIVE_CHIPS.DATE_SYS=#ARC_CHIP.DATE_SYS
     
     INSERT INTO W_D_ARCHIVE_CHIPS_LOG SELECT * FROM dbo.W_D_ARCHIVE_CHIPS WHERE Txn_ID=@_TransactionId
     
     DELETE FROM dbo.W_D_ARCHIVE_CHIPS WHERE Txn_ID=@_TransactionId
      
    END TRY 
    BEGIN CATCH
     
     SELECT @_Error = ERROR_MESSAGE()
     SELECT @_ErrorSeverity = ERROR_SEVERITY()
     
     -- Insert archive detail Log temp table
     INSERT @_Temp_Log
     SELECT *
     FROM #_Temp_Log
     
     
     IF @@TRANCOUNT > 1
      COMMIT TRAN @_TransactionId
     ELSE IF @@TRANCOUNT = 1
      ROLLBACK TRAN @_TransactionId

      IF @_ErrorSeverity <> 11
     BEGIN
      -- If archive failure, update Archive Log
      UPDATE dbo.ARC_LOG WITH (ROWLOCK)
      SET  DURATION = DATEDIFF(ss, START_TIME, GETDATE()),
        STATUS = 'FAILURE',
        MESSAGE = @_TabName+':'+@_Error
      WHERE TXN_ID = @_TransactionId
      
      DECLARE @Subjectcontent VARCHAR(500)
      SELECT @Subjectcontent='Metro Archive fail,because of '+@_Error+'on table:'+@_TabName
      EXEC master.dbo.SendMail_cdo_sp @from ='Metro-Archive',@TO='wentong_he@neophotonics.com.cn',@Subject='Metro Archive Fail',@Body=@Subjectcontent
      
      UPDATE W_D_ARCHIVE_CHIPS
      SET STATUS='Error'
      FROM W_D_ARCHIVE_CHIPS
      INNER JOIN #ARC_CHIP
      ON W_D_ARCHIVE_CHIPS.PKGLOTNUMBERID =#ARC_CHIP.PKGLOTNUMBERID AND W_D_ARCHIVE_CHIPS.DATE_SYS=#ARC_CHIP.DATE_SYS

     END
     ELSE
     BEGIN
      ---- If cannot found the Archive data, update Archive Log
      UPDATE dbo.ARC_LOG WITH (ROWLOCK)
      SET  DURATION = DATEDIFF(ss, START_TIME, GETDATE()),
        STATUS = 'SUCCESS',
        MESSAGE = @_Error
      WHERE TXN_ID = @_TransactionId
     END
     
     RAISERROR('%s', 16, 1, @_Error)
     
    END CATCH


    -- Insert Archive Log Detail table
    INSERT dbo.ARC_DETAIL_LOG WITH (ROWLOCK)
    (
      TXN_ID,TABLE_NAME,AFFECT_QTY,ACTION_NAME,TXN_TIME,CACHED_SQL
    )
    SELECT @_TransactionId, TABLE_NAME, AFFECT_QTY, ACTION_NAME, TXN_TIME, CACHED_SQL
    FROM @_Temp_Log


    DROP TABLE #_TableLists
    DROP TABLE #ARC_CHIP
    DROP TABLE #_Temp_Log


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年10月24日 3:03
  • 我在其他的数据库中运行这个就是可以的.

    只是环境是windows 2003的,sqlserver 2008 R2 ,而且都是32位的


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年10月24日 3:48
  • 而且我对整个数据库进行dbcc checkdb未发现任何错误.


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年10月24日 6:29
  • Do you know which sql statement caused error? Can trace that in profiler. By the way, win nt 5.2 is win2k3.
    2011年10月24日 15:54