SQL 2008存储过程每隔一次运行一次就会成功运行一次

Question

好的，我已经在这个问题上纠结了一天半了，在网站上还没有解决方案。

我正在运行SQL Server 2008 Standard Edition (64位)本文的底部有问题的存储过程

我有一个存储过程，只有在每隔一次执行它时才能成功运行。基本上，此SP所做的是从两个表(两个表在同一本地服务器上，两个不同的数据库)中提取一些值，并将这些值插入到第三个表中。成功执行后，作为insert命令目标的表将追加一个新行。当它不能成功执行时，我得到以下错误：

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

当我右键单击并直接执行SP时，或者从Agent作业运行SP时，都会出现此问题(如果将Agent作业设置为重试，则在重试时始终会成功)。当“每执行一次”的问题是非常可重复的。

我插入的表确实有一个PrimaryKey，我没有在SP中寻址，因为我让SQL处理该值的递增。

这是SP (实际上是这个SP的Alter Procedure输出)，有什么想法吗？

USE [Database1]
GO
/****** Object:  StoredProcedure [dbo].[sp210_Daily_Summary_21006]    Script Date: 02/06/2013 10:26:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[sp210_Daily_Summary_21006]

/*** 21006 ***/
@Date datetime = NULL,
@Shop_Order int = NULL,
@Lot_Number nvarchar(16) = NULL,
@Part_Number nvarchar(16) = NULL,
@Balance_Left int = NULL,
@Day_Shift_OEE real = NULL,
@Day_Shift_Good int = NULL,
@Day_Shift_Supervisor_Notes nvarchar(256) = NULL,
@Day_Shift_Tech_Notes nvarchar(256) = NULL,
@Day_Shift_Repair_Log int = NULL,
@Day_Shift_QA_Notes nvarchar(256) = NULL,
@Day_Shift_NCMR_QIF int = NULL,
@Night_Shift_OEE real = NULL,
@Night_Shift_Good int = NULL,
@Night_Shift_Supervisor_Notes nvarchar(256) = NULL,
@Night_Shift_Tech_Notes nvarchar(256) = NULL,
@Night_Shift_Repair_Log int = NULL,
@Night_Shift_QA_Notes nvarchar(256) = NULL,
@Night_Shift_NCMR_QIF int = NULL

AS
BEGIN

Set NoCount On;

Select
@Date = GETDATE(),
@Shop_Order = (Select SHOP_ORDER_NUMBER from dbo.CurrentJobSetupsAutomation WHERE (WORK_CENTER = 21006)),
@Lot_Number = (Select LOT_NUMBER from dbo.CurrentJobSetupsAutomation WHERE (WORK_CENTER = 21006)),
@Part_Number = (Select ITEM_NUMBER from dbo.CurrentJobSetupsAutomation WHERE (WORK_CENTER = 21006)),
@Balance_Left = (Select BalanceDue from dbo.CurrentJobSetupsAutomation WHERE (WORK_CENTER = 21006)),

--- Day Shift Good (7:00 AM to 7:00 PM previous day) when report runs at 6:30 AM
@Day_Shift_Good = (SELECT sum(Actual)
FROM Database2.dbo.BPAQualityLog
where AssetID = 3 
and SubQualityName = 21006
and DATEADD(HH,DATEDIFF(hour,getutcdate(),getdate()),RecordUpdated) >= DATEADD(HH,-17, DATEADD(DD,0, DATEDIFF(DD, 0, GETDATE())))
and DATEADD(HH,DATEDIFF(hour,getutcdate(),getdate()),RecordUpdated) <= DATEADD(HH,-5, DATEADD(DD,0, DATEDIFF(DD, 0, GETDATE())))
),

@Day_Shift_Supervisor_Notes = (Select Day_Shift_Supervisor_Notes from dbo.[210_Daily_Temp] WHERE (WORK_CENTER = 21006)),
@Day_Shift_Tech_Notes = (Select Day_Shift_Tech_Notes from dbo.[210_Daily_Temp] WHERE (WORK_CENTER = 21006)),
@Day_Shift_Repair_Log = (Select Day_Shift_Repair_Log from dbo.[210_Daily_Temp] WHERE (WORK_CENTER = 21006)),
@Day_Shift_QA_Notes = (Select Day_Shift_QA_Notes from dbo.[210_Daily_Temp] WHERE (WORK_CENTER = 21006)),
@Day_Shift_NCMR_QIF = (Select Day_Shift_NCMR_QIF from dbo.[210_Daily_Temp] WHERE (WORK_CENTER = 21006)),

--- Night Shift Good (7:00 PM to 7:00 AM current day) when report runs at 6:30 AM
@Night_Shift_Good = (SELECT sum(Actual)
FROM Database2.dbo.BPAQualityLog
where AssetID = 3 
and SubQualityName = 21006
and DATEADD(HH,DATEDIFF(hour,getutcdate(),getdate()),RecordUpdated) >= DATEADD(HH,-5, DATEADD(DD,0, DATEDIFF(DD, 0, GETDATE())))
and DATEADD(HH,DATEDIFF(hour,getutcdate(),getdate()),RecordUpdated) <= DATEADD(HH,7, DATEADD(DD,0, DATEDIFF(DD, 0, GETDATE())))
),

@Night_Shift_Supervisor_Notes = (Select Night_Shift_Supervisor_Notes from dbo.[210_Daily_Temp] WHERE (WORK_CENTER = 21006)),
@Night_Shift_Tech_Notes = (Select Night_Shift_Tech_Notes from dbo.[210_Daily_Temp] WHERE (WORK_CENTER = 21006)),
@Night_Shift_Repair_Log = (Select Night_Shift_Repair_Log from dbo.[210_Daily_Temp] WHERE (WORK_CENTER = 21006)),
@Night_Shift_QA_Notes = (Select Night_Shift_QA_Notes from dbo.[210_Daily_Temp] WHERE (WORK_CENTER = 21006)),
@Night_Shift_NCMR_QIF = (Select Night_Shift_NCMR_QIF from dbo.[210_Daily_Temp] WHERE (WORK_CENTER = 21006))

Set NoCount Off;

insert into [Database1].[dbo].[210_Daily_Summary]
([Date], 
[Work_Center], 
[Shop_Order], 
[Lot_Number], 
[Part_Number], 
[Balance_Left],
[Day_Shift_Good],
[Day_Shift_Supervisor_Notes],
[Day_Shift_Tech_Notes],
[Day_Shift_Repair_Log],
[Day_Shift_QA_Notes],
[Day_Shift_NCMR_QIF],
[Night_Shift_Good],
[Night_Shift_Supervisor_Notes],
[Night_Shift_Tech_Notes],
[Night_Shift_Repair_Log],
[Night_Shift_QA_Notes],
[Night_Shift_NCMR_QIF]
)

values
(@Date,
'21006', 
@Shop_Order,
@Lot_Number,
@Part_Number,
@Balance_Left,
@Day_Shift_Good,
@Day_Shift_Supervisor_Notes,
@Day_Shift_Tech_Notes,
@Day_Shift_Repair_Log,
@Day_Shift_QA_Notes,
@Day_Shift_NCMR_QIF,
@Night_Shift_Good,
@Night_Shift_Supervisor_Notes,
@Night_Shift_Tech_Notes,
@Night_Shift_Repair_Log,
@Night_Shift_QA_Notes,
@Night_Shift_NCMR_QIF
)

END

下面是我用来创建插入到其中的表的查询：

USE [Database1]
GO

/****** Object:  Table [dbo].[210_Daily_Summary]    Script Date: 02/06/2013 10:28:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[210_Daily_Summary](
[PrimaryKey] [int] IDENTITY(1,1) NOT NULL,
[Date] [datetime] NULL,
[Work_Center] [nvarchar](16) NULL,
[Shop_Order] [int] NULL,
[Lot_Number] [nvarchar](16) NULL,
[Part_Number] [nvarchar](16) NULL,
[Balance_Left] [int] NULL,
[Day_Shift_OEE] [real] NULL,
[Day_Shift_Good] [int] NULL,
[Day_Shift_Supervisor_Notes] [nvarchar](256) NULL,
[Day_Shift_Tech_Notes] [nvarchar](256) NULL,
[Day_Shift_Repair_Log] [int] NULL,
[Day_Shift_QA_Notes] [nvarchar](256) NULL,
[Day_Shift_NCMR_QIF] [int] NULL,
[Night_Shift_OEE] [real] NULL,
[Night_Shift_Good] [int] NULL,
[Night_Shift_Supervisor_Notes] [nvarchar](256) NULL,
[Night_Shift_Tech_Notes] [nvarchar](256) NULL,
[Night_Shift_Repair_Log] [int] NULL,
[Night_Shift_QA_Notes] [nvarchar](256) NULL,
[Night_Shift_NCMR_QIF] [int] NULL
) ON [PRIMARY]

GO

Answer 1

好的，首先让我们确保你没有损坏。运行以下命令，并告诉我们结果：

DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

接下来，您需要检查SQL Server错误日志，以查看服务器是否正在同时记录任何其他错误。