Professional Web Applications Themes

Cannot deallocate a cursor? - Microsoft SQL / MS SQL Server

Please post the complete code of your trigger and someone here will rewrite it without a cursor. Cursor are very seldom needed and sofar your scenario doesn't sound like you need one. This will be a far better solution than trying to fix the cursor. -- Jacco Schalkwijk MCDBA, MCSD, MCSE Database Administrator Eurostop Ltd. "jess" <UNBUENAMIGO1HOTMAIL.COM> wrote in message news:076801c346f1$2d3708f0$a001280aphx.gbl... > I have a trigger using a cursor that contains among other > tables the inserted 'i' table (a SELECT-type cursor). This > trigger is executed everytime a new record is inserted > into one of my tables. What ...

  1. #1

    Default Re: Cannot deallocate a cursor?

    Please post the complete code of your trigger and someone here will rewrite
    it without a cursor. Cursor are very seldom needed and sofar your scenario
    doesn't sound like you need one. This will be a far better solution than
    trying to fix the cursor.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "jess" <UNBUENAMIGO1HOTMAIL.COM> wrote in message
    news:076801c346f1$2d3708f0$a001280aphx.gbl...
    > I have a trigger using a cursor that contains among other
    > tables the inserted 'i' table (a SELECT-type cursor). This
    > trigger is executed everytime a new record is inserted
    > into one of my tables. What my cursor basically does is
    > store the value of certain values in some variables. Those
    > variables are used to update the table the trigger belongs
    > to. This seems to be crappy programming - I am using a
    > cursor -, but it is the only solution I have come up with.
    >
    > This cursor works ok for the first inserted record but if
    > fails to store the variables for the following inserted
    > record. It is as if my cursor had not been released from
    > memory, SQL disregarding its declaration when the trigger
    > is executed again. The cursor actually stores the same
    > values stored for the firt inserted record. This problem
    > is repeated for the next records. That is, the variables
    > contain the value corresponding to the SELECT statement of
    > the cursor when the trigger was executed for the first
    > time. Once this happens, SQL seems to ignore the
    > declaration for the cursor.
    >
    > The declaration for my cursor has this aspect:
    >
    >
    > DECLARE CURSOR1
    > CURSOR
    > LOCAL
    > SCROLL
    > FOR
    >
    > SELECT .................. (This select statement contains
    > the inserted i table)
    >
    >
    > OPEN CURSOR1
    >
    > FETCH NEXT
    > FROM CURSOR1
    > INTO (List of variables)
    >
    >
    > CLOSE CURSOR1
    >
    > DEALLOCATE CURSOR1
    >
    > Can anybody help me?
    >
    >
    >
    >
    >
    >

    Jacco Schalkwijk Guest

  2. #2

    Default Re: Cannot deallocate a cursor?

    Cursors where once a beautiful new friend, they are now my enemy and the bain of
    all who love freedom and chocolate.

    I found that by including an int column (status int) or status variable set to
    0 on insert or initialization, I can then ON MY OWN update each row changing the
    status to 1 from zero or whatever if I want to trap for conditionals.

    I do this with select top 1 where status = 0

    I'm not sure if the following will suite your needs, but consider....

    set counter = (select count(*) from X)
    select top 1 id = id ,fieldA = fieldA ,fieldB = fieldB from X where status =
    0
    while counter > 0
    begin
    do something....
    if all is well
    begin
    update X
    set status = 1 where id = id
    end
    select top 1 id = id ,fieldA = fieldA ,fieldB = fieldB from X where status =
    0
    end

    HTH

    JeffP....
    .... a cursor free world, is that too much to ask for....?

    "jess" <UNBUENAMIGO1HOTMAIL.COM> wrote in message
    news:076801c346f1$2d3708f0$a001280aphx.gbl...
    > I have a trigger using a cursor that contains among other
    > tables the inserted 'i' table (a SELECT-type cursor). This
    > trigger is executed everytime a new record is inserted
    > into one of my tables. What my cursor basically does is
    > store the value of certain values in some variables. Those
    > variables are used to update the table the trigger belongs
    > to. This seems to be crappy programming - I am using a
    > cursor -, but it is the only solution I have come up with.
    >
    > This cursor works ok for the first inserted record but if
    > fails to store the variables for the following inserted
    > record. It is as if my cursor had not been released from
    > memory, SQL disregarding its declaration when the trigger
    > is executed again. The cursor actually stores the same
    > values stored for the firt inserted record. This problem
    > is repeated for the next records. That is, the variables
    > contain the value corresponding to the SELECT statement of
    > the cursor when the trigger was executed for the first
    > time. Once this happens, SQL seems to ignore the
    > declaration for the cursor.
    >
    > The declaration for my cursor has this aspect:
    >
    >
    > DECLARE CURSOR1
    > CURSOR
    > LOCAL
    > SCROLL
    > FOR
    >
    > SELECT .................. (This select statement contains
    > the inserted i table)
    >
    >
    > OPEN CURSOR1
    >
    > FETCH NEXT
    > FROM CURSOR1
    > INTO (List of variables)
    >
    >
    > CLOSE CURSOR1
    >
    > DEALLOCATE CURSOR1
    >
    > Can anybody help me?
    >
    >
    >
    >
    >
    >

    JDP@Work Guest

Similar Threads

  1. Cursor bug?
    By Tom Lane in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: January 17th, 09:25 AM
  2. cursor is gone
    By csharv in forum Macromedia Director Basics
    Replies: 2
    Last Post: April 15th, 12:47 PM
  3. look at the cursor
    By Seph webforumsuser@macromedia.com in forum Macromedia Director 3D
    Replies: 2
    Last Post: December 18th, 01:56 PM
  4. cursor 200-problem on mac but not pc? how to swap cursor image?
    By nickelsock webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 5
    Last Post: August 2nd, 10:58 AM
  5. Change the "web hand" cursor in normal arrow cursor?
    By FB1976 in forum Adobe Dreamweaver & Contribute
    Replies: 1
    Last Post: July 9th, 07:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139