SP's occasionally dont return data.

  • 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 ?

  • 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?

  • 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

  • Has anyone else had similar experiences?

  • 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