January 13, 2003 at 2:24 pm
I occasionally get calls from users that no data is being returned to them when they know that it was there the other day.
I run the sp, and like them, no data. I run the sp debugger in QA and ... Data is returned and they now see data as well ?
What's up ?
January 13, 2003 at 2:43 pm
How often do these occurr? Also, when it occurrs again try sp_recompile YOURSPNAMEHERE and see if the same thing occurrs. If that works as well you may have something in your code that is saving a bad execution plan that ultimately is the cause. It might help if we can see what your code to understand what it may be doing, please post?
January 13, 2003 at 2:49 pm
I can only say that this has happened about a 1/2 dozen times over the past 5 months, and I do not think that the sp's have been the same one.
CREATE PROCEDURE spLIMS_LotResult
@pSITE_NO INT,
@pItem_NO NVARCHAR(12),
@pLot_No NVARCHAR(12),
@pSampleSet INT,
@pGrade_No INT,
@pSpec_No INT
AS
SELECT
COA_LOT_RESULT.ITEM_NO,
COA_LOT_RESULT.LOT_NO,
COA_LOT_RESULT.GRADE_NO,
COA_LAB_TEST.TEST_NAME,
COA_PROPERTY.PROPERTY,
COA_SPEC_DETAIL.LOWER_CTRL_LIMIT,
COA_SPEC_DETAIL.UPPER_CTRL_LIMIT,
COA_SPEC_DETAIL.LOWER_SPEC_LIMIT,
COA_SPEC_DETAIL.UPPER_SPEC_LIMIT
FROMCOA_LOT_RESULT
INNER JOIN COA_LAB_TEST
ON COA_LAB_TEST.SITE_NO = @pSITE_NO
AND COA_LAB_TEST.TEST_NO = COA_LOT_RESULT.TEST_NO
INNER JOIN COA_PROPERTY
ON COA_PROPERTY.SITE_NO = @pSITE_NO
AND COA_PROPERTY.PROPERTY_NO = COA_LOT_RESULT.PROPERTY_NO
INNER JOIN COA_SPEC_DETAIL
ON COA_SPEC_DETAIL.SITE_NO = @pSITE_NO
AND COA_SPEC_DETAIL.spec_no = @pSPEC_NO
AND COA_SPEC_DETAIL.test_no = COA_LOT_RESULT.test_no
AND COA_SPEC_DETAIL.property_no = COA_LOT_RESULT.property_no
WHERE COA_LOT_RESULT.ITEM_NO = @pItem_NO
AND COA_LOT_RESULT.LOT_NO = @pLOT_NO
AND COA_LOT_RESULT.SAMPLESET = @pSampleSet
AND COA_LOT_RESULT.GRADE_NO = @pGRADE_NO
ORDER BY COA_LAB_TEST.TEST_NAME, COA_PROPERTY.PROPERTY
GO
January 20, 2003 at 8:45 am
Has anyone else had similar experiences?
January 21, 2003 at 4:24 am
Sometimes the setting
CONCAT NULL YIELDS NULL
is the culprit.
You can cheeck that setting and wheteher it is set from the connection.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply