A cursor with the name already exists

Question

Post reply

A cursor with the name already exists

krypto69

SSChampion

Points: 13591

December 4, 2019 at 2:25 pm

#3704267

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](
@recordsToProcessINT = NULL,
@logToTableINT = NULL)
AS
DECLARE @AuthUKeyVARCHAR(255)
DECLARE@ModifiedDateDATETIME
DECLARE@currentModifiedDateDATETIME
DECLARE@IntegrationStatusINT
DECLARE@currentRecordINT
DECLARE@timeRecordToPullDATETIME
DECLARE@timeOffSetMinuteINT
DECLARE @intExtractIDINT
DECLARE@returnErrorStringVARCHAR(MAX)
DECLARE @RCINT
DECLARE@blnSuccessBIT
DECLARE@blnRetryBIT

-- 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
@returnErrorStringOUTPUT,
@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_idINT,
referral_idINT,
referral_udVARCHAR(35),
authorization_numberVARCHAR(35),
px_codeVARCHAR(35),
requested_quantityINT,
approved_quantityINT,
modifier_1VARCHAR(15),
modifier_2VARCHAR(15),
modifier_3VARCHAR(15),
modifier_4VARCHAR(15),
effective_date_fromDATETIME,
effective_date_thruDATETIME,
estimated_costMONEY,
quantity_to_dateINT,
cost_to_dateMONEY,
average_costMONEY,
last_modified_datetimeDATETIME)
-- E Create table #ReferralProcedures

-- B Create table #Eligibility
CREATE TABLE #Eligibility (
eligibility_idINT,
eligibility_udVARCHAR(35),
member_idINT,
benefit_contract_idINT,
benefit_contract_udVARCHAR(35),
benefit_contract_typeVARCHAR(35),
effective_date_fromDATETIME,
effective_date_thruDATETIME,
is_primary_contract_type_for_effective_datesBIT,
last_mofified_datetimeDATETIME)
-- E Create table #Eligibility

-- B Create table #Provider
CREATE TABLE #Provider (
provider_idINT,
provider_tag_udVARCHAR(35),
last_or_org_nameVARCHAR(35),
first_nameVARCHAR(35),
middle_nameVARCHAR(35),
genderVARCHAR(35),
call_back_group_idINT,
call_back_group_udVARCHAR(35),
last_modified_datetimeDATETIME)
-- E Create table #Provider

-- B Create table #referral_list
CREATE TABLE #referral_list (
referral_idINT,
referral_udVARCHAR(35),
authorization_numberVARCHAR(35),
statusVARCHAR(15),
eligibility_idINT,
eligibility_udVARCHAR(35),
referring_provider_idINT,
referring_provider_tag_udVARCHAR(35),
referred_to_provider_idINT,
referred_to_provider_tag_udVARCHAR(35),
primary_dx_codeVARCHAR(35),
secondary_dx_codeVARCHAR(35),
effective_date_fromDATETIME,
effective_date_thruDATETIME,
max_visitsINT,
symptomsTEXT,
commentsTEXT,
estimated_procedure_costMONEY,
estimated_total_costMONEY,
claims_to_dateINT,
visits_to_dateINT,
procedure_cost_to_dateMONEY,
total_cost_to_dateMONEY,
average_procedure_costMONEY,
average_total_costMONEY,
total_expected_costMONEY,
last_modified_datetimeDATETIME)
-- 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

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

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

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: 21797 More actions · Answer 2

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

MVDBA