SQL 2008 Stored Procedure Executes Successfully Every Time It Runs

Okay, I was stuck with this for a day and a half without permission from the site.

I am running SQL Server 2008 Standard Edition (64 bit) The stored procedure is at the bottom of this post

I have a stored procedure that will only run successfully every time I execute it. Basically what this SP does is take some values ​​from multiple tables (both tables on the same local server, two different databases) and insert those values ​​into a third table. On successful execution of the table that is the target of the insert command, a new row is added. When it fails, I get the following errors:

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.

      

        
        
        
      

    

The problem occurs when I right click and run the SP directly or when I run it from an agent job (the agent job, if configured to retry, will always succeed on restart). When the problem of "every other performance" is very repeatable.

The table I am inserting into has a PrimaryKey which I am not addressing to SP because I am allowing the SQL handle to increment that value.

Here is the SP (actually this is the output of the Alter Procedure for this SP), any ideas?

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

      

        
        
        
      

    

And here is the query I used to create the table I am inserting into:

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