CODEWITHSUNDEEP
sql-servert-sql
Jason Clark
Jason Clark

Reputation: 1425

sqlstate 5001 error "There is already an object named PKCURSOR in the database"

We have Powerbuilder app that ran fine on the 2000 db before we migrated to 2005. Now we get the following error:

sqlstate 5001 error "There is already an object named PKCURSOR in the database"

The partial code below has been modified by adding a drop contraint PKCURSOR . So the error does not pop up now for 2 of the dba's who have powerbuilder installed and they run the app from their network drive. The other user runs it from her network drive and gets the error. I've also made that user a dbo and still gets the error. Any ideas?

ALTER PROCEDURE [dbo].[GUMBO_SP_PROP_ACTUAL_ACCOMPLISHMENTS_PF]
    @PROG_YEAR CHAR(4)
AS

DECLARE @PROGRAM_YEAR                   CHAR(4),
        @SUM_LOW_MOD_PERSONS_PROPOSED   INTEGER,
        @SUM_LOW_MOD_PERSONS_ACTUAL     INTEGER,
        @SUM_TOTAL_PERSONS_PROPOSED     INTEGER,
        @SUM_TOTAL_PERSONS_ACTUAL       INTEGER,
        @ERROR_STRING                   CHAR(132),
        @ACTIVITY_CODE                  CHAR(3)

CREATE TABLE #ACCOMPLISHMENTS(
    PROGRAM_YEAR                CHAR(4) NOT NULL,
    ACTIVITY_CODE               CHAR(3) NOT NULL,
    TOTAL_PERSONS_PROPOSED      DECIMAL(18,2) DEFAULT 0,
    TOTAL_PERSONS_ACTUAL        DECIMAL(18,2) DEFAULT 0,
    LOW_MOD_PERSONS_PROPOSED    DECIMAL(18,2) DEFAULT 0,
    LOW_MOD_PERSONS_ACTUAL      DECIMAL(18,2) DEFAULT 0
)
-- Alter the temporary table to have a primary key of application number and program year.
ALTER TABLE #ACCOMPLISHMENTS
    ADD CONSTRAINT PKCURSOR PRIMARY KEY (PROGRAM_YEAR, ACTIVITY_CODE)

DECLARE ACTIVITY_CURSOR CURSOR FOR
    SELECT dbo.ACTIVITY_CODE.activity_code
    FROM dbo.ACTIVITY_CODE
    WHERE 
        (dbo.ACTIVITY_CODE.activity_code LIKE 'P%%') 
        and (dbo.ACTIVITY_CODE.activity_code <> 'P01')
    ORDER BY dbo.ACTIVITY_CODE.activity_code

ALTER TABLE #ACCOMPLISHMENTS
    DROP CONSTRAINT PKCURSOR

Upvotes: 0

Views: 674

Answers (1)

Deep Kalra
Deep Kalra

Reputation: 1428

This might happened because you have a primary key with the same name already in the same schema. To find on which table it is on, run the following query:

SELECT
DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME = 'PKCURSOR'

Solution:

Add the code below to drop the key if it exists, put this snippet after CREATE TABLE #ACCOMPLISHMENTS part of your stored procedure.

IF EXISTS(select * from sys.key_constraints
WHERE name ='PKCURSOR')
ALTER TABLE #ACCOMPLISHMENTS
  DROP CONSTRAINT PKCURSOR

Upvotes: 2

Related Questions