A cursor with the name already exists

  • krypto69

    SSChampion

    Points: 13502

    Hi,

    I have this code in a job that needs to run every 5 minutes. It works most of the time without issue. But 4-5 times a day I get the "a cursor with the name already exists" error.

    I added the LOCAL to the cursor declaration, but still get the error.

    I checked for triggers but don't see any on the tables referenced in the query:

    USE [iTest2_InternetSQL]
    GO
    /****** Object: StoredProcedure [dbo].[PEC_AuthsInterface_AE_To_Plexis] Script Date: 12/4/2019 9:01:51 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO


    /********************************************************************************************************
    ** Purpose: Push Auths from the AE system to system
    **
    **
    *********************************************************************************************************/
    ALTER PROCEDURE [dbo].[PEC_AuthsInterface_AE_To_Plexis](
    @recordsToProcess INT = NULL,
    @logToTable INT = NULL)
    AS
    DECLARE @AuthUKey VARCHAR(255)
    DECLARE @ModifiedDate DATETIME
    DECLARE @currentModifiedDate DATETIME
    DECLARE @IntegrationStatus INT
    DECLARE @currentRecord INT
    DECLARE @timeRecordToPull DATETIME
    DECLARE @timeOffSetMinute INT
    DECLARE @intExtractID INT
    DECLARE @returnErrorString VARCHAR(MAX)
    DECLARE @RC INT
    DECLARE @blnSuccess BIT
    DECLARE @blnRetry BIT

    -- B Test
    --PEC_AuthsInterface_AE_To_Plexis
    --PEC_AuthsInterface_AE_To_Plexis 0
    --PEC_AuthsInterface_AE_To_Plexis -1
    --PEC_AuthsInterface_AE_To_Plexis 2
    --PEC_AuthsInterface_AE_To_Plexis 1000
    --CREATE TABLE [dbo].[PEC_AuthsInterface_AE_To_Plexis_Debug]([id] [bigint] IDENTITY(1,1) NOT NULL, [info] [varchar](max) NOT NULL, CONSTRAINT [PK_PEC_AuthsInterface_AE_To_Plexis_Debug] PRIMARY KEY CLUSTERED ([id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    -- E Test

    SET NOCOUNT ON

    -- B Set global variables
    -- @timeOffSetMinute and @timeRecordToPull
    -- is used to pull records that have settled for more than @timeOffSetMinute which in this case is 10 minutes
    -- so lets use integers to repsent time and @timeOffSetMinute is set to 5 the formula is (t-x) > ModifiedDate so we have the following
    -- [... 10 9 8 7 6 5 4 3 2 1...] so will pull records back from 4 3 2 1 ... and so on
    SELECT @timeOffSetMinute = -10
    SELECT @timeRecordToPull = DATEADD(mi, @timeOffSetMinute, CURRENT_TIMESTAMP)
    SELECT @returnErrorString = ''
    -- E Set global variables

    -- B Create Temp Table Section
    SELECT AuthUKey, ModifiedDate, IntegrationStatus INTO #Authorization_Staging_Temp FROM Authorization_Staging WHERE 1 = 0
    -- E Create Temp Table Section

    -- B Create temp table for called stored procedure
    -- Doing it this way so I don't have all that code here it is diffcult seeing the flow of logic
    GOTO B_CREATETEMPTABLES
    E_CREATETEMPTABLES:
    -- E Create temp table for called stored procedure

    -- B Populate temp table
    -- See where statment below to see what records are returned
    -- Note: may need to change this logic

    -- Remove Pended Records
    Delete AuS
    From Authorization_Staging AuS
    Inner join Auths A on AuS.AuthUkey = A.AuthUKey
    Where A.Status = 'Pended'

    INSERT #Authorization_Staging_Temp
    SELECT
    AuS.AuthUKey,
    ModifiedDate,
    IntegrationStatus
    FROM Authorization_Staging AuS
    INNER JOIN Auths A on AuS.AuthUkey = A.AuthUKey
    WHERE AuS.IntegrationStatus IN (0, 2)
    AND @timeRecordToPull > AuS.ModifiedDate
    AND A.Status <> 'Pended'

    -- E Populate temp table

    -- B Declare and open cursor
    --12/03/2019 ROB ADDED 'LOCAL' BELOW TO RESOLVE FOR CURSOR ERRORS
    DECLARE Authorization_Staging_Temp_Cursor CURSOR LOCAL FOR
    SELECT AuthUKey, ModifiedDate, IntegrationStatus FROM #Authorization_Staging_Temp ORDER BY AuthUKey
    OPEN Authorization_Staging_Temp_Cursor
    -- E Declare and open cursor

    SELECT @currentRecord = 0
    --if @recordsToProcess is null then process all records in table #Authorization_Staging_Temp
    IF (@recordsToProcess IS NULL OR @recordsToProcess <= 0)
    BEGIN
    SELECT @recordsToProcess = COUNT(*) FROM #Authorization_Staging_Temp
    END

    FETCH NEXT FROM Authorization_Staging_Temp_Cursor INTO @AuthUKey, @ModifiedDate, @IntegrationStatus
    WHILE (@@fetch_status = 0)
    BEGIN
    --Process
    -- select auths
    -- process auths
    -- record
    -- getting extract id and message
    -- record in staginghistory
    -- decided to delete from staging or flag error
    -- B Do work here

    IF (@logToTable IS NOT NULL)
    BEGIN
    INSERT PEC_AuthsInterface_AE_To_Plexis_Debug(info) SELECT 'B Loop: (' + ISNULL(@AuthUKey, 'NULL') + ')'
    END

    -- B Lock records by setting the InegrationStatus = 1
    UPDATE Authorization_Staging SET IntegrationStatus = 1 WHERE AuthUKey = @AuthUKey
    -- E Lock records by setting the InegrationStatus = 1

    -- B Assume everything is good
    SELECT
    @currentModifiedDate = ModifiedDate,
    @intExtractID = NULL,
    @blnSuccess = 1,
    @blnRetry = 0,
    @returnErrorString = NULL
    FROM Authorization_Staging
    WHERE AuthUKey = @AuthUKey
    -- E Assume everything is good

    --SP that the exports data to Plexis system, if no error occure
    EXECUTE @RC = PEC_AuthExport
    @returnErrorString OUTPUT,
    @AuthUKey = @AuthUKey
    IF (@RC <> 0)
    BEGIN
    --Encountered an error
    SELECT @blnSuccess = 0, @blnRetry = 0
    END
    ELSE
    BEGIN
    --Everything good
    SELECT @blnSuccess = 1, @blnRetry = 0
    END
    IF (@logToTable IS NOT NULL)
    BEGIN
    INSERT PEC_AuthsInterface_AE_To_Plexis_Debug(info) SELECT 'E Loop: (' + ISNULL(@AuthUKey, 'NULL') + ')' + '(' + ISNULL(@returnErrorString, 'NULL') + ') (' +
    CAST(@currentModifiedDate AS VARCHAR(MAX)) + ') (' + CAST(@ModifiedDate AS VARCHAR(MAX)) + ') '
    END

    --SP to insert into Authorization_StagingHistory table
    EXECUTE Authorization_Staging_RealTime_InsertStagingHistory
    @strAuthUKey = @AuthUKey,
    @dtModifiedDate = @currentModifiedDate,
    @intStagingID = @intExtractID OUTPUT

    IF (@currentModifiedDate <> @ModifiedDate)
    BEGIN
    --Record has changed since staging was ran
    SELECT @blnSuccess = 0, @blnRetry = 1
    END
    --This sp will delete from Authorization_Staging if @blnSuccess = 1
    EXECUTE Authorization_Staging_RealTime_UpdateStagingHistory
    @strAuthUKey = @AuthUKey,
    @intExtractID = @intExtractID,
    @blnSuccess = @blnSuccess,
    @strMsg = @returnErrorString,
    @blnRetry = @blnRetry

    IF (@logToTable IS NOT NULL)
    BEGIN
    INSERT PEC_AuthsInterface_AE_To_Plexis_Debug(info) SELECT 'E Loop: (' + ISNULL(@AuthUKey, 'NULL') + ')' + '(' + ISNULL(@returnErrorString, 'NULL') + ')'
    END
    -- E Processing Authorization_Staging and Authorization_StagingHistory
    -- E Do work here

    SELECT @currentRecord = @currentRecord + 1
    IF (@currentRecord = @recordsToProcess)
    BEGIN
    BREAK
    END
    FETCH NEXT FROM Authorization_Staging_Temp_Cursor INTO @AuthUKey, @ModifiedDate, @IntegrationStatus
    END
    CLOSE Authorization_Staging_Temp_Cursor
    DEALLOCATE Authorization_Staging_Temp_Cursor

    -- B Clean up
    DROP TABLE #Authorization_Staging_Temp
    DROP TABLE #Updated_Procedure_id
    DROP TABLE #ReferralProcedures
    DROP TABLE #Eligibility
    DROP TABLE #Provider
    DROP TABLE #referral_list
    DROP TABLE #Authorization_Procedures
    -- E Clean up
    return

    B_CREATETEMPTABLES:

    -- B Create table #Updated_Procedure_id
    CREATE TABLE #Updated_Procedure_id(
    referral_procedure_id INT NOT NULL)
    -- E Create table #Updated_Procedure_id

    -- B Create table #ReferralProcedures
    CREATE TABLE #ReferralProcedures (
    referral_procedure_id INT,
    referral_id INT,
    referral_ud VARCHAR(35),
    authorization_number VARCHAR(35),
    px_code VARCHAR(35),
    requested_quantity INT,
    approved_quantity INT,
    modifier_1 VARCHAR(15),
    modifier_2 VARCHAR(15),
    modifier_3 VARCHAR(15),
    modifier_4 VARCHAR(15),
    effective_date_from DATETIME,
    effective_date_thru DATETIME,
    estimated_cost MONEY,
    quantity_to_date INT,
    cost_to_date MONEY,
    average_cost MONEY,
    last_modified_datetime DATETIME)
    -- E Create table #ReferralProcedures

    -- B Create table #Eligibility
    CREATE TABLE #Eligibility (
    eligibility_id INT,
    eligibility_ud VARCHAR(35),
    member_id INT,
    benefit_contract_id INT,
    benefit_contract_ud VARCHAR(35),
    benefit_contract_type VARCHAR(35),
    effective_date_from DATETIME,
    effective_date_thru DATETIME,
    is_primary_contract_type_for_effective_dates BIT,
    last_mofified_datetime DATETIME)
    -- E Create table #Eligibility

    -- B Create table #Provider
    CREATE TABLE #Provider (
    provider_id INT,
    provider_tag_ud VARCHAR(35),
    last_or_org_name VARCHAR(35),
    first_name VARCHAR(35),
    middle_name VARCHAR(35),
    gender VARCHAR(35),
    call_back_group_id INT,
    call_back_group_ud VARCHAR(35),
    last_modified_datetime DATETIME)
    -- E Create table #Provider

    -- B Create table #referral_list
    CREATE TABLE #referral_list (
    referral_id INT,
    referral_ud VARCHAR(35),
    authorization_number VARCHAR(35),
    status VARCHAR(15),
    eligibility_id INT,
    eligibility_ud VARCHAR(35),
    referring_provider_id INT,
    referring_provider_tag_ud VARCHAR(35),
    referred_to_provider_id INT,
    referred_to_provider_tag_ud VARCHAR(35),
    primary_dx_code VARCHAR(35),
    secondary_dx_code VARCHAR(35),
    effective_date_from DATETIME,
    effective_date_thru DATETIME,
    max_visits INT,
    symptoms TEXT,
    comments TEXT,
    estimated_procedure_cost MONEY,
    estimated_total_cost MONEY,
    claims_to_date INT,
    visits_to_date INT,
    procedure_cost_to_date MONEY,
    total_cost_to_date MONEY,
    average_procedure_cost MONEY,
    average_total_cost MONEY,
    total_expected_cost MONEY,
    last_modified_datetime DATETIME)
    -- E Create table #referral_list

    -- B Create table #Authorization_Procedures
    SELECT * INTO #Authorization_Procedures FROM Authorization_ProcedureData WHERE 1 = 0
    -- E Create table #Authorization_Procedures
    GOTO E_CREATETEMPTABLES



  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    the only way that can happen is if it is in the same session and the previous cursor did not shut down correctly

    put a try catch block around the code in your cursor so that the "close cursor, deallocate cursor" always happens

    MVDBA

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    and maybe stick some transaction management in there begin tran, commit , rollback etc

    MVDBA

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply