Hi.

I have a situation where SQL is behaving irrationally.
I have some code that uses a cursor to read through about 500,000 rows in a SQL table. When this code is copied from the Stored Procedure into Query Analyzer and run, it runs correctly. When I execute the Stored Procedure (with that same code) from Query Analyzer it fails with an error message that the data for a row may be cut off. This error happens after about 3/4 of the 500,000 rows have been processed.

Any suggestions will be greatly appreciated!

Thanks.

Rita

Hi Rita,

Is there any possiblity of taking a look at the code here to understand what it does?

_________________________
- Vijay G
Strive for Perfection

Hi Vijay,

It's a lot of code but here goes!

CREATE PROCEDURE MapPPO24PPOF
AS
BEGIN

        -- Locally declares variables --
        Declare @PPOID varchar (12)
        Declare @PractOrHosp char (1)
        Declare @ProviderID varchar (8)
        Declare @FederalTaxID varchar (15)
        Declare @AddressID varchar (10)
        Declare @TierSequenceNumber varchar (5)

        Declare @TotRecs int
        Declare @RecCnt int

        -- Initialize local variables --
        Set @TotRecs = 0
        Set @RecCnt = 0

        -- Local variables --
        Declare @PractOrHosp1 char (1)
        Declare @PractOrHosp2 char (1)
        Declare @DefaultCodeFrom varchar (11)
        Declare @DefaultCodeTo varchar (11)
        Declare @ProviderAgreementEffDate smalldatetime
        Declare @ProviderAgreementTermDate smalldatetime
        Declare @DefaultPercentDiscount decimal(5, 4)
        Declare @AnesthesiaConversionFactor decimal(7, 2)
        Declare @TextFeeScheduleID varchar (6)
        Declare @OutpatientInd char (1)
        Declare @InpatientInd char (1)

        -- Initialize local variables --
        Set @PractOrHosp1 = 'P'
        Set @PractOrHosp2 = 'H'
        Set @DefaultCodeFrom = '00100'
        Set @DefaultCodeTo = '69999'
        Set @OutpatientInd = 'O'
        Set @InpatientInd = 'I'

    Set NOCOUNT ON

    -- Map Staging table data (stPhysicianTemp /stFacilityTemp) into the Output table (outPPOF)
    Set @TotRecs = (SELECT Count (*) FROM PPOIDS)

    IF @TotRecs > 0 -- Check for empty table
    BEGIN
        -- Declare the Cursor for reading PPOID
        Declare PPOID_Cursor CURSOR FOR

        SELECT Cast(PPOID as varchar(12)), PractOrHosp, ProviderID, FederalTaxID, AddressID, TierSequenceNumber
            FROM PPOIDS Order by PPOID
        OPEN PPOID_Cursor

        -- First record processing
        FETCH NEXT FROM PPOID_Cursor
        INTO @PPOID, @PractOrHosp, @ProviderID, @FederalTaxID, @AddressID, @TierSequenceNumber
        Set @RecCnt = 1

        -- Map first PPOID derived Contract table data into the Output tables (outPPOF and outLINK)
        Exec UpdatePPOFandLINK @PPOID, @PractOrHosp, @ProviderID, @FederalTaxID, @AddressID, @TierSequenceNumber

        -- Subsequent record processing
        WHILE @RecCnt < @TotRecs -- Ensure correct record count
        BEGIN

            FETCH NEXT FROM PPOID_Cursor
            INTO @PPOID, @PractOrHosp, @ProviderID, @FederalTaxID, @AddressID, @TierSequenceNumber
            Set @RecCnt = @RecCnt + 1

            -- Map PhysicianTemp Staging table data into the Output tables (outPPOF and outLINK)
            IF @PractOrHosp = @PractOrHosp1
            BEGIN
                -- Populate Contract table (outPPOF) from PhysicianTemp
                INSERT INTO outPPOF
                    (TIN,
                    PPOID,
                    LastName,
                    FirstName,
                    PracticeAddress1,
                    PracticeAddress2,
                    PracticeCity,
                    PracticeState,
                    PracticeZip,
                    BillingAddress1,
                    BillingAddress2,
                    BillingCity,
                    BillingState,
                    BillingZip,
                    PhoneNumber,
                    EffectiveDate,
                    TerminationDate,
                    OPUCRPercentile,
                    OPLinkKey,
                         OPEffectiveDate,
                    OPTerminationDate,
                    PPOToFSID)
                SELECT TOP 1
                    FederalTaxID,
                    @PPOID,
                    RTrim(LastName),
                    RTrim(FirstName),
                    Left(RTrim(Address1),30),
                    Left(RTrim(Address2),30),
                    Left(RTrim(City),20),
                    State,
                    Left(RTrim(ZipCode),9),
                    Left(RTrim(Address1),30),
                    Left(RTrim(Address2),30),
                    Left(RTrim(City),20),
                    State,
                    Left(RTrim(ZipCode),9),
                    Left(RTrim(PhoneNumber),10),
                    ProviderEffectiveDate,
                    ProviderTermDate,
                    RTrim(GeoPercentile), -- OPUCRPercentile
                    @PPOID, -- OPLinkKey
                    ProviderEffectiveDate, -- OPEffectiveDate
                    ProviderTermDate, -- OPTerminationDate
                    @PPOID -- PPOToFSID
                FROM stPhysicianTemp
                WHERE ProviderID = @ProviderID -- Identical PhysicianTemp Contract record needed for LINK load
                    AND FederalTaxID = @FederalTaxID
                    AND AddressID = @AddressID
                    AND TierSequenceNumber = @TierSequenceNumber
                ORDER BY ProviderID, FederalTaxID, AddressID, TierSequenceNumber

                -- Populate LINK table (outLINK) from PhysicianTemp
                SELECT TOP 1
                    @ProviderAgreementEffDate = ProviderAgreementEffDate,
                    @ProviderAgreementTermDate = ProviderAgreementTermDate,
                    @AnesthesiaConversionFactor = AnesthesiaConversionFactor,
                    @DefaultPercentDiscount = DefaultPercentDiscount,
                    @TextFeeScheduleID = TextFeeScheduleID -- Required for potential Comment record
                    FROM stPhysicianTemp

                    WHERE ProviderID = @ProviderID
                        AND FederalTaxID = @FederalTaxID
                        AND AddressID = @AddressID
                        AND TierSequenceNumber = @TierSequenceNumber
                    ORDER BY ProviderID, FederalTaxID, AddressID, TierSequenceNumber

                INSERT INTO outLINK
                    (LinkName,
                    CodeFrom,
                    CodeTo,
                    EffectiveDate,
                    TerminationDate,
                    PercentDiscount,
                    AnesthesiaRate)
                Values
                    (@PPOID,
                    @DefaultCodeFrom,
                    @DefaultCodeTo,
                    @ProviderAgreementEffDate,
                    @ProviderAgreementTermDate,
                    @DefaultPercentDiscount,
                    @AnesthesiaConversionFactor)

                -- Populate Comment table (outPPOC) from PhysicianTemp
                IF RTrim(@TextFeeScheduleID) <> '' -- ONLY update with Comment record if a TextFeeScheduleID exists
                        BEGIN
                            Exec UpdatePPOC @PPOID, @TextFeeScheduleID
                        END
            END

            -- Map FacilityTemp Staging table data into the Output tables (outPPOF and outLINK)
            IF @PractOrHosp = @PractOrHosp2
            BEGIN
                -- Populate Contract table (outPPOF) from FacilityTemp
                INSERT INTO outPPOF
                    (TIN,
                    PPOID,
                    LastName,
                    PracticeAddress1,
                    PracticeAddress2,
                    PracticeCity,
                    PracticeState,
                    PracticeZip,
                    BillingAddress1,
                    BillingAddress2,
                    BillingCity,
                    BillingState,
                    BillingZip,
                    PhoneNumber,
                    EffectiveDate,
                    TerminationDate,
                    OPUCRPercentile,
                    OPLinkKey,
                    OPLineItemDefaultDiscount,
                         OPEffectiveDate,
                    OPTerminationDate,
                    IPLinkKey,
                         IPEffectiveDate,
                    IPTerminationDate,
                    PPOToFSID)
                SELECT TOP 1
                    FederalTaxID,
                    @PPOID,
                    RTrim(ProviderName),
                    Left(RTrim(Address1),30),
                    Left(RTrim(Address2),30),
                    Left(RTrim(City),20),
                    State,
                    Left(RTrim(ZipCode),9),
                    Left(RTrim(Address1),30),
                    Left(RTrim(Address2),30),
                    Left(RTrim(City),20),
                    State,
                    Left(RTrim(ZipCode),9),
                    Left(RTrim(PhoneNumber),10),
                    ProviderEffectiveDate,
                    ProviderTermDate,
                    RTrim(GeoPercentile), -- OPUCRPercentile
                    @PPOID + @OutpatientInd, -- OPLinkKey
                    CASE -- OPLineItemDefaultDiscount (OPLinkFileDefaultDiscount)
                        WHEN (OutpatientDiscountPercent IS NOT NULL)
                            THEN OutpatientDiscountPercent
WHEN (OutpatientDiscountPercent IS NULL) AND (EmergencyDiscountPercent IS
                NOT NULL)
                            THEN EmergencyDiscountPercent
                        WHEN (OutpatientDiscountPercent IS NULL) AND (EmergencyDiscountPercent IS
NULL) AND (InpatientDiscountPercent IS NULL)
                            THEN DefaultPercentDiscount
                    END,
                    ProviderEffectiveDate, -- OPEffectiveDate
                    ProviderTermDate, -- OPTerminationDate
                    @PPOID + @InpatientInd, -- IPLinkKey
                    ProviderEffectiveDate, -- IPEffectiveDate
                    ProviderTermDate, -- IPTerminationDate
                    @PPOID -- PPOToFSID
                FROM stFacilityTemp
                WHERE ProviderID = @ProviderID -- Identical FacilityTemp Contract record needed for LINK load
                    AND FederalTaxID = @FederalTaxID
                    AND AddressID = @AddressID
                    AND TierSequenceNumber = @TierSequenceNumber
                ORDER BY ProviderID, FederalTaxID, AddressID, TierSequenceNumber

                -- Populate LINK table (outLINK) from FacilityTemp
                SELECT TOP 1
                    @ProviderAgreementEffDate = ProviderAgreementEffDate,
                    @ProviderAgreementTermDate = ProviderAgreementTermDate,
                    @AnesthesiaConversionFactor = AnesthesiaConversionFactor,
                    @DefaultPercentDiscount = DefaultPercentDiscount,
                    @TextFeeScheduleID = TextFeeScheduleID -- Required for potential Comment record
                    FROM stFacilityTemp
                    WHERE ProviderID = @ProviderID
AND FederalTaxID = @FederalTaxID
                        AND AddressID = @AddressID
                        AND TierSequenceNumber = @TierSequenceNumber
                    ORDER BY ProviderID, FederalTaxID, AddressID, TierSequenceNumber

                INSERT INTO outLINK
                    (LinkName,
                    CodeFrom,
                    CodeTo,
                    EffectiveDate,
                    TerminationDate,
                    PercentDiscount,
                    AnesthesiaRate)
                Values
                    (@PPOID,
                    @DefaultCodeFrom,
                    @DefaultCodeTo,
                    @ProviderAgreementEffDate,
                    @ProviderAgreementTermDate,
                    @DefaultPercentDiscount,
                    @AnesthesiaConversionFactor)

                -- Populate Comment table (outPPOC) from FacilityTemp
                IF RTrim(@TextFeeScheduleID) <> '' -- ONLY update with Comment record if a TextFeeScheduleID exists
                        BEGIN
                            Exec UpdatePPOC @PPOID, @TextFeeScheduleID
                        END
            END

        END

        CLOSE PPOID_Cursor
        DEALLOCATE PPOID_Cursor
    END
    Set NOCOUNT OFF
END

Hi,

At a glance it looks like a problem related to Cursor overhead and procedure cache. Execution plan and mem usage are different for a Script and stored procedure. Exact Error message is required to trace out the cause.

Have a try using DBCC FLUSHPROCINDB to tackle the procedure cache and also alter your procedure using WITH RECOMPILE. If not required a lock, Use NOLOCK in cursor query.

I hope you have searched enough with a trace. It would be helpfull to run record a trace through profiler to catch where exactly problems is comming from. As u r saying it gets failed only if stored procedure is running and it works fine if fired as a free script.

Not sure about ur server settings exactly. How your server flushes the memory, what is mem usage etc.

Apart from all these efforts I like to say that its better to avoid cursors with such a large number of rows. If I were u, I would have picked the rowcount exactly as u have done (@TotRecs) and then start a transaction block. Within transaction it would be working with lesser block of rows say per block contains (@TotRecs/4) records.

Gud luck.

B. Anant

Hi Anant,

Many thanks for your response.
I think your suggestions are all good ones and I'll try all of them first except for starting the transaction block.

If it still fails I'll then recode to include the transaction block logic.

Rita