December 4, 2019 at 2:25 pm
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
December 4, 2019 at 3:00 pm
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
December 4, 2019 at 3:34 pm
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