[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Cursor error

Posted on 2006-05-09
8
Medium Priority
?
1,488 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:Cyber-Drugs
  • 4
  • 3
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16638012
Due to some previous errors, previously created cursor still exists,  run

CLOSE C
DEALLOCATE C
0
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 16638028
Is it safe to just close the cursor?
0
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 16638044
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 16638091
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
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 16638126
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
 
LVL 11

Expert Comment

by:Otana
ID: 16638144
I see you have "DECLARE c CURSOR FOR" twice in your code. Try replacing one of these with another name.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16638147
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
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 16638148
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question