SQL 2008 Stored Procedure Runs Successfully Every Other Time It Is Run
-
I posted this question on StackOverflow and they suggested I ask it here.
Ok, I've been stuck on this one for a day and a half now with no resolution in site.
I am running SQL Server 2008 Standard Edition (64-bit) The stored procedure in question is at the bottom of this post
I have a stored procedure that will only run successfully every other time I execute it. Basically what this SP does is take some values from a couple of tables (both tables on the same local server, two different databases) and inserts these values into a third table. When executed successfully the table that is the target of the insert command has a new row appended. When it does not execute successfully 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 issue happens both when I right click and execute the SP directly or when I run it from an Agent job (the Agent job, if I have it set to retry, always will end up successful on the retry). When The "every other execution" nature of the problem is very repeatable.
The table I insert into does have a PrimaryKey, that I don't address in the SP because I let SQL handle incrementing that value.
Here is the SP (actually it's the Alter Procedure output 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 insert 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
Basically I am seeing fatal exceptions every other time this stored procedure is run. I'm wondering if I am not completing the tasks of the SP correctly, IE not closing something that has been opened etc. Very frustrating!
Here is the conversation brought over from Stack Overflow:
Thanks for your reply. I ran the CheckDB and only received a Command(s) Completed Successfully. In looking at the Server Logs there are too many errors to count in the timeframes that this SP is running. I have been having it running every 10 minutes. I stopped that schedule from running, I will give the logs some time to get timestamp separation, then manually run the SP again twice to generate the error. At that time I will grab the server logs and post whatever info is helpful. Thanks again! – CraigB 17 hours ago
I ran the test and have an export of the error log. What is the SOP for posting a fairly large dump such as this? I don't think I can post it in less than 500 characters and I don't see an attach file mechanism. The first few lines of the error that look to me most important: – CraigB 16 hours ago
02/06/2013 13:07:40,spid96,Unknown,Using 'dbghelp.dll' version '4.0.5' – CraigB 16 hours ago
02/06/2013 13:07:40,spid96,Unknown,SqlDumpExceptionHandler: Process 96 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. 02/06/2013 13:07:40,spid96,Unknown,***Stack Dump being sent to D:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\SQLDump0105.txt – CraigB 16 hours ago
At this point I am going to advise you to contact MS Support. If you are not ready to do that yet, then I suggest that you take this question and the info to someplace like SQLServerCentral.com or the Msdn forums that can better respond to this kind of complex diagnostic need. – RBarryYoung 13 hours ago
-
I would start off with SQL Profiler, and profile execution of the stored procedure at the statement level to try and identify the actual statement that is causing the exception.
-
Thanks for the response.
When tracing using SQL Server Profiler and template TSQL_SPs the initial event I get is:
exec sp_executesql N'SELECT
SCHEMA_NAME(sp.schema_id) AS [Schema],
sp.name AS [Name]
FROM
sys.all_objects AS sp
WHERE
(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(sp.name=@_msparam_3 and SCHEMA_NAME(sp.schema_id)=@_msparam_4)',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'P',@_msparam_1=N'RF',@_msparam_2=N'PC',@_msparam_3=N'sp210_Daily_Summary_21006',@_msparam_4=N'dbo'
I get many events after that similar to this:
exec sp_executesql N'SELECT
sp.name AS [Name],
sp.object_id AS [ID],
sp.create_date AS [CreateDate],
sp.modify_date AS [DateLastModified],
ISNULL(ssp.name, N'''') AS [Owner],
CAST(case when sp.principal_id is null then 1 else 0 end AS bit) AS [IsSchemaOwned],
SCHEMA_NAME(sp.schema_id) AS [Schema],
CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N''microsoft_database_tools_support'')
is not null then 1
else 0
end
AS bit) AS [IsSystemObject],
CAST(OBJECTPROPERTYEX(sp.object_id,N''ExecIsAnsiNullsOn'') AS bit) AS [AnsiNullsStatus],
CAST(OBJECTPROPERTYEX(sp.object_id,N''ExecIsQuotedIdentOn'') AS bit) AS [QuotedIdentifierStatus],
CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
CAST(ISNULL(smsp.is_recompiled, ssmsp.is_recompiled) AS bit) AS [Recompile],
case when amsp.object_id is null then N'''' else asmblsp.name end AS [AssemblyName],
case when amsp.object_id is null then N'''' else amsp.assembly_class end AS [ClassName],
case when amsp.object_id is null then N'''' else amsp.assembly_method end AS [MethodName],
case when amsp.object_id is null then case isnull(smsp.execute_as_principal_id, -1) when -1 then 1 when -2 then 2 else 3 end else case isnull(amsp.execute_as_principal_id, -1) when -1 then 1 when -2 then 2 else 3 end end AS [ExecutionContext],
case when amsp.object_id is null then ISNULL(user_name(smsp.execute_as_principal_id),N'''') else user_name(amsp.execute_as_principal_id) end AS [ExecutionContextPrincipal],
CAST(ISNULL(spp.is_auto_executed,0) AS bit) AS [Startup],
CAST(CASE sp.type WHEN N''RF'' THEN 1 ELSE 0 END AS bit) AS [ForReplication],
CASE WHEN sp.type = N''P'' THEN 1 WHEN sp.type = N''PC'' THEN 2 ELSE 1 END AS [ImplementationType]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.database_principals AS ssp ON ssp.principal_id = ISNULL(sp.principal_id, (OBJECTPROPERTY(sp.object_id, ''OwnerId'')))
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
LEFT OUTER JOIN sys.assembly_modules AS amsp ON amsp.object_id = sp.object_id
LEFT OUTER JOIN sys.assemblies AS asmblsp ON asmblsp.assembly_id = amsp.assembly_id
LEFT OUTER JOIN sys.procedures AS spp ON spp.object_id = sp.object_id
WHERE
(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(sp.name=@_msparam_3 and SCHEMA_NAME(sp.schema_id)=@_msparam_4)',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'P',@_msparam_1=N'RF',@_msparam_2=N'PC',@_msparam_3=N'sp210_Daily_Summary_21006',@_msparam_4=N'dbo'
When it successfully runs I get this (which I don't really understand, why would a return value = the stored procedure, or is that just where the value is going to?:
EXEC@return_value = [dbo].[sp210_Daily_Summary_21006]
Maybe I am getting this error because in my SP I don't specify a Return?
-
Not sure if it's the problem, but that query has a load of individual reads against tables that could be combined, if you do this:
Select @Date = GETDATE()
SELECT
@Shop_Order = SHOP_ORDER_NUMBER,
@Lot_Number = LOT_NUMBER,
@Part_Number = ITEM_NUMBER,
@Balance_Left = BalanceDue
FROM dbo.CurrentJobSetupsAutomation
WHERE (WORK_CENTER = 21006)
SELECT
@Day_Shift_Supervisor_Notes = Day_Shift_Supervisor_Notes,
@Day_Shift_Tech_Notes = Day_Shift_Tech_Notes,
@Day_Shift_Repair_Log = Day_Shift_Repair_Log,
@Day_Shift_QA_Notes = Day_Shift_QA_Notes,
@Day_Shift_NCMR_QIF = Day_Shift_NCMR_QIF,
@Night_Shift_Supervisor_Notes = Night_Shift_Supervisor_Notes,
@Night_Shift_Tech_Notes = Night_Shift_Tech_Notes,
@Night_Shift_Repair_Log = Night_Shift_Repair_Log,
@Night_Shift_QA_Notes = Night_Shift_QA_Notes,
@Night_Shift_NCMR_QIF = Night_Shift_NCMR_QIF
FROM dbo.[210_Daily_Temp]
WHERE (WORK_CENTER = 21006)
--- Day Shift Good (7:00 AM to 7:00 PM previous day) when report runs at 6:30 AM
SELECT @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())))
)
--- Night Shift Good (7:00 PM to 7:00 AM current day) when report runs at 6:30 AM
SELECT @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())))
)
You could combine the @Day_Shift_Good and @Night_Shift_Good into one select too but I'd need to know more about the size, structure & indexes on the Database2.dbo.BPAQualityLog table before I suggested something there.
Cheers
Gaz
-
Agree with Gazareth, best to start by simplifying the query.
It looks like you take everything in as a parameter, and then overwrite the value of each parameter...am I missing a subtlety? Why have the parameters?
-
craig.bates (2/7/2013)
When it successfully runs I get this (which I don't really understand, why would a return value = the stored procedure, or is that just where the value is going to?:
EXEC@return_value = [dbo].[sp210_Daily_Summary_21006]
Maybe I am getting this error because in my SP I don't specify a Return?
No, that's fine. When called like that, the proc returns 0 for success or non-zero for failure.
-
David McKinney (2/7/2013)
It looks like you take everything in as a parameter, and then overwrite the value of each parameter...am I missing a subtlety? Why have the parameters?Good point David - I missed that. You'd probably be better declaring the parameters inside the proc if they're not needed to be passed values when calling the proc.
-
You could probably do the whole thing without any parameters or variables (or maybe one variable for the sum.)
-
Thank you both.
I'm obviously more of a Joe than a Pro at this point. I combined the multiple lookups and I can now run the SP without issue.
Post mortem summary - I idiotically wrote the SP.
Thanks again!
-
As far as the parameters go, I'm used to having to pass parameters to my SP's for other purposes. True when I run the SP I leave all at NULL.
Any quick and easy clean up example for that one?
Thanks all!
-
craig.bates (2/7/2013)
As far as the parameters go, I'm used to having to pass parameters to my SP's for other purposes. True when I run the SP I leave all at NULL.Any quick and easy clean up example for that one?
Thanks all!
Like this:
(But you need to make sure anything that currently calls the proc doesn't give values for the parameters)
Cheers
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]
AS
BEGIN
DECLARE
@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
... rest of code here
END
-
Is case it wasn't obvious, I am the person at StackOverflow who suggested that Craig post this problem over here (extended interactive diagnosis doesn't really work back there).
A couple of points:
I had him run
DBCC CHECKDB (<DatabaseName>) WITH NO_INFOMSGS, ALL_ERRORMSGSbut it didn't find anything.I had him check the SQL Server Error Log, where he said that he found hundreds of messages.
My gut feeling is that there's some kind of table corruption, but DBCC didn't find anything , plus, that's not my area of expertise.
Craig: If you can go ahead and attach a text file with some of the lines from your Error Log. (Unlike SO, you can do that here. I think that you have to use the [Edit Attachments] button inside the [Post Options] bar, just below the edit window here.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Am I mistaken, Barry? On page 1 of this thread, Craig says he found the solution. Or did I misread that?
-
No he did not find the solution. He found a workaround that prevents the error from occurring.
The fact is that a fatal error has occurred, and it should not be possible to cause that by coding something in a particular way.
There is obviously an underlying problem - whether an obscure bug in SQL Server, a memory fault, a dodgy disk, whatever...
-
I'm not so sure there's corruption anywhere - in the original query, there's 15 subselects chained together, most against the same table, and some in a different database. Think it just caused the optimiser or the db engine itself to go a bit loopy. All speculation of course 😀
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply