Cursor error

Hi guys,

I've just encountered yet another error in my code, and wandered if any of you could help me fix it please?

The error I get is this:

(0 row(s) affected)
Msg 16915, Level 16, State 1, Procedure usp_HandleData, Line 15
A cursor with the name 'c' already exists.

Unfortunately, I think i need to paste the entire chunk again, so here it is:

IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[JustinProc]'))
DROP Procedure JustinProc
GO

CREATE Procedure JustinProc
AS
       IF  exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[fn_ParseCSVString]') and xtype in (N'FN', N'IF', N'TF'))
            DROP FUNCTION fn_ParseCSVString
      DECLARE @sql nvarchar(4000)
      SET @sql = 'CREATE FUNCTION fn_ParseCSVString
      (
            @CSVString     varchar(8000),
            @Delimiter    varchar(10)
      )
      returns @tbl TABLE (s varchar(1000))
      AS
      BEGIN
      DECLARE @i int ,
            @j int
            SELECT     @i = 1
            WHILE @i <= len(@CSVString)
            BEGIN
                  SELECT    @j = charindex(@Delimiter, @CSVString, @i)
                  IF @j = 0
                  BEGIN
                        SELECT    @j = len(@CSVString) + 1
                  END
                  INSERT    @tbl select substring(@CSVString, @i, @j - @i)
                  SELECT    @i = @j + len(@Delimiter)
            END
            RETURN
      END'
      EXEC sp_executeSQL @sql
      GO

      IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[usp_HandleData]'))
            DROP Procedure usp_HandleData
      GO

      CREATE Procedure [dbo].[usp_HandleData]
      (
            @data as nvarchar(50),
            @equipment as nvarchar(50),
            @serial as nvarchar(50),
            @description as nvarchar(50)
      )
      /*
      returns @dump TABLE (Results nvarchar(50))
      */
      AS
      BEGIN
            DECLARE c CURSOR FOR
            SELECT * FROM dbo.fn_ParseCSVString (@data,'-')

            DECLARE @varA nvarchar(50)
            DECLARE @varB nvarchar(50)
            DECLARE @varC nvarchar(50)
            DECLARE @varD nvarchar(50)
            DECLARE @varE nvarchar(50)
            DECLARE @varF nvarchar(50)
            DECLARE @varG nvarchar(50)

            OPEN c
            FETCH NEXT FROM c INTO @varA
            FETCH NEXT FROM c INTO @varB
            FETCH NEXT FROM c INTO @varC
            FETCH NEXT FROM c INTO @varD
            FETCH NEXT FROM c INTO @varE
            FETCH NEXT FROM c INTO @varF
            FETCH NEXT FROM c INTO @varG
            CLOSE c
            DEALLOCATE c

            DECLARE @site nvarchar(50)
            DECLARE @room nvarchar(50)
            DECLARE @cab nvarchar(50)
            DECLARE @device nvarchar(50)
            DECLARE @module nvarchar(50)

            SELECT @site    = @varA + '-' + @varB
            SELECT @room    = @varA + '-' + @varB + '-' + @varC
            SELECT @cab     = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE
            SELECT @device  = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE + '-' + @varF
            SELECT @module  = @varA + '-' + @varB + '-' + @varC + '-' + @varD + '-' + @varE + '-' + @varF + '-' +@varG

            IF @varA IS NULL
                  BEGIN
                        INSERT INTO dbo.tblLogs
                        (
                              Action,
                              Description,
                              [Timestamp]
                        ) VALUES (
                              'Error',
                              'Error: `' + @data + '` is a blank field.',
                              ''
                        )
                  END
            ELSE IF @varB IS NULL
                  BEGIN
                        INSERT INTO dbo.tblLogs
                        (
                              Action,
                              Description,
                              [Timestamp]
                        ) VALUES (
                              'Error',
                              'Error: `' + @data + '` does not contain enough data.',
                              ''
                        )
                  END
            ELSE IF @varC IS NULL
                  BEGIN
                        INSERT INTO dbo.tblLogs
                        (
                              Action,
                              Description,
                              [Timestamp]
                        ) VALUES (
                              'Error',
                              'Error: `' + @data + '` does not contain enough data.',
                              ''
                        )
                  END
            ELSE IF @varD IS NULL
                  BEGIN
                        INSERT INTO dbo.tblLogs
                        (
                              Action,
                              Description,
                              [Timestamp]
                        ) VALUES (
                              'Error',
                              'Error: `' + @data + '` does not contain enough data.',
                              ''
                        )
                  END
            ELSE IF @varE IS NULL
                  BEGIN
                        INSERT INTO dbo.tblLogs
                        (
                              Action,
                              Description,
                              [Timestamp]
                        ) VALUES (
                              'Error',
                              'Error: `' + @data + '` does not contain enough data.',
                              ''
                        )
                  END
            ELSE IF @varF IS NULL
                  BEGIN
                        INSERT INTO dbo.tblLogs
                        (
                              Action,
                              Description,
                              [Timestamp]
                        ) VALUES (
                              'Error',
                              'Error: `' + @data + '` does not contain enough data.',
                              ''
                        )
                  END
            ELSE IF @varG IS NULL
                  BEGIN
                        IF EXISTS(SELECT 1 FROM dbo.tblDevice WHERE SAP_EQ = @Equipment AND DeviceID = @data)
                              BEGIN
                                    /*
                                    Update Log File
                                    */
                                    INSERT INTO dbo.tblLogs
                                    (
                                          Action,
                                          Description,
                                          [Timestamp]
                                    ) VALUES (
                                          'Update',
                                          'Update for `' + @Equipment + '` not required.',
                                          ''
                                    )
                              END
                        ELSE IF EXISTS(SELECT 1 FROM dbo.tblDevice WHERE SAP_EQ = @Equipment)
                              BEGIN
                                    /*
                                    Update tblDevice
                                    */
                                    UPDATE dbo.tblDevice
                                    SET DeviceID = @data
                                    WHERE SAP_EQ = @Equipment
                                    /*
                                    Update Log File
                                    */
                                    INSERT INTO dbo.tblLogs
                                    (
                                          Action,
                                          Description,
                                          [Timestamp]
                                    ) VALUES (
                                          'Update',
                                          'Updated `' + @Equipment + '` in tblDevice.',
                                          ''
                                    )
                              END
                        ELSE
                              BEGIN
                                    IF EXISTS(SELECT 1 FROM dbo.tblCab WHERE Cab_FL = @cab)
                                          BEGIN
                                                DECLARE @varUID nvarchar(50)
                                                /*
                                                DECLARE c CURSOR FOR
                                                SELECT UID FROM dbo.tblCab WHERE Cab_FL = @data
                                                OPEN c
                                                      FETCH NEXT FROM c INTO @varUID
                                                CLOSE c
                                                DEALLOCATE c
                                                */
                                                SELECT @varUID = UID FROM dbo.tblCab WHERE Cab_FL = @cab
                                                /*
                                                Insert new Device Record
                                                */
                                                INSERT INTO dbo.tblDevice
                                                (
                                                      CabID,
                                                      DeviceID,
                                                      SerialNumber
                                                ) VALUES (
                                                      @varUID,
                                                      @Equipment,
                                                      @serial
                                                )
                                                /*
                                                Update Log File
                                                */
                                                INSERT INTO dbo.tblLogs
                                                (
                                                      Action,
                                                      Description,
                                                      [Timestamp]
                                                ) VALUES (
                                                      'Insert',
                                                      'Inserted `' + @cab + '` into Device Table',
                                                      ''
                                                )
                                                IF EXISTS(SELECT 1 FROM dbo.tblDevice_Cat WHERE Model = @description)
                                                      BEGIN
                                                            DECLARE @varCatUID nvarchar(50)
                                                            DECLARE c CURSOR FOR
                                                            SELECT UID FROM dbo.tblDevice_Cat WHERE Model = @description
                                                            OPEN c
                                                                  FETCH NEXT FROM c INTO @varCatUID
                                                            CLOSE c
                                                            /*
                                                            Update DeviceCatID in tblDevice
                                                            */
                                                            UPDATE dbo.tblDevice
                                                            SET DeviceCatID = @varCatUID
                                                            WHERE SAP_EQ = @Equipment
                                                            /*
                                                            Update Log File
                                                            */
                                                            INSERT INTO dbo.tblLogs
                                                            (
                                                                  Action,
                                                                  Description,
                                                                  [Timestamp]
                                                            ) VALUES (
                                                                  'Update',
                                                                  'Updated `' + @varCatUID + '` in Device Table with CatID `' + @varCatUID + '`',
                                                                  ''
                                                            )
                                                      END
                                          END
                                    ELSE
                                          BEGIN
                                                /*
                                                Update Log File
                                                */
                                                INSERT INTO dbo.tblLogs
                                                (
                                                      Action,
                                                      Description,
                                                      [Timestamp]
                                                ) VALUES (
                                                      'Message',
                                                      'Message: `' + @cab + '` does not exist in tblCab.',
                                                      ''
                                                )
                                          END
                              END
                  END
            ELSE
                  BEGIN
                        /*
                        Update Log File
                        */
                        INSERT INTO dbo.tblLogs
                        (
                              Action,
                              Description,
                              [Timestamp]
                        ) VALUES (
                              'Message',
                              'Message: `' + @data + '` is a Module.',
                              ''
                        )
                  END
            RETURN
      END
      GO

      DECLARE @FunctLocation varchar(30)
      DECLARE @Equipment varchar(30)
      DECLARE @Serial varchar(30)
      DECLARE @Description varchar(30)
      DECLARE Cur Cursor FOR
      SELECT FunctLocation, Equipment, [Serial number], Description FROM dbo.tblImportData
      OPEN Cur
      IF @@ERROR<>0
      BEGIN
            CLOSE Cur
            DEALLOCATE Cur
            RETURN
      END
      FETCH NEXT FROM Cur INTO @FunctLocation,@Equipment, @Serial, @Description

      WHILE @@FETCH_STATUS= 0
      BEGIN
            IF OBJECT_ID('Tempdb..#temp') IS NOT NULL
                  DROP Table #temp
            CREATE Table #temp (results nvarchar(50))
            INSERT INTO #temp
            EXEC usp_HandleData @FunctLocation,@Equipment,@Serial,@Description
            FETCH NEXT FROM Cur INTO @FunctLocation,@Equipment,@Serial,@Description
      END

      CLOSE Cur
      DEALLOCATE Cur
GO
LVL 4
Cyber-DrugsAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
Cyber-Drugs,
--You must do some error handling, check for the value of @@FETCH_STATUS immediately after each ftch operation

 CREATE Procedure [dbo].[usp_HandleData]
     (
          @data as nvarchar(50),
          @equipment as nvarchar(50),
          @serial as nvarchar(50),
          @description as nvarchar(50)
     )
     /*
     returns @dump TABLE (Results nvarchar(50))
     */
     AS
     BEGIN
    DECLARE c CURSOR FOR
    SELECT * FROM dbo.fn_ParseCSVString (@data,'-')
   
    DECLARE @varA nvarchar(50)
    DECLARE @varB nvarchar(50)
    DECLARE @varC nvarchar(50)
    DECLARE @varD nvarchar(50)
    DECLARE @varE nvarchar(50)
    DECLARE @varF nvarchar(50)
    DECLARE @varG nvarchar(50)
   
    OPEN c
    FETCH NEXT FROM c INTO @varA
    IF @@FETCH_STATUS = 0
        FETCH NEXT FROM c INTO @varB
    ELSE
        GOTO tes

    IF @@FETCH_STATUS = 0
        FETCH NEXT FROM c INTO @varC
    ELSE
        GOTO tes
    IF @@FETCH_STATUS = 0
        FETCH NEXT FROM c INTO @varD
    ELSE
        GOTO tes
    IF @@FETCH_STATUS = 0
        FETCH NEXT FROM c INTO @varE
    ELSE
        GOTO tes
    IF @@FETCH_STATUS = 0
        FETCH NEXT FROM c INTO @varF
    ELSE
        GOTO tes
    IF @@FETCH_STATUS = 0
        FETCH NEXT FROM c INTO @varG

Tes:    CLOSE c
    DEALLOCATE c
   
    DECLARE @site nvarchar(50)
    DECLARE @room nvarchar(50)
    DECLARE @cab nvarchar(50)
    DECLARE @device nvarchar(50)
    DECLARE @module nvarchar(50)

   

Aneesh
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Due to some previous errors, previously created cursor still exists,  run

CLOSE C
DEALLOCATE C
0
 
Cyber-DrugsAuthor Commented:
Is it safe to just close the cursor?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Cyber-DrugsAuthor Commented:
When I close it, I then get this error:

Msg 16917, Level 16, State 1, Line 15
Cursor is not open.

as well as the previous:

(0 row(s) affected)
Msg 16915, Level 16, State 1, Procedure usp_HandleData, Line 15
A cursor with the name 'c' already exists.
0
 
Cyber-DrugsAuthor Commented:
OK, I updated the code with the checks you showed above, but I still get the error:

(0 row(s) affected)
Msg 16915, Level 16, State 1, Procedure usp_HandleData, Line 15
A cursor with the name 'c' already exists.
0
 
OtanaCommented:
I see you have "DECLARE c CURSOR FOR" twice in your code. Try replacing one of these with another name.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Run this


IF ( CURSOR_STATUS('global', 'C') >= 0  /* cursor is open */
        OR CURSOR_STATUS('local', 'C') >= 0 )
    CLOSE C
IF ( CURSOR_STATUS('global','C') = -1  /* cursor is closed */
        OR CURSOR_STATUS('local','C') = -1 )
    DEALLOCATE C
0
 
Cyber-DrugsAuthor Commented:
I noticed the problem!

Higher up in the code, I closed the cursor, but didn't DEALLOCATE it as well, so that fixed it. But thanks for the help. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.