Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jan 2005
    Posts
    36

    Unanswered: Strange Stored Procedure Issue

    Hi

    I have this problem that has me banging my head on the wall

    I have a stored procedure - has a cursor & a temp table in it.

    Problem is - it works, but it will only run once.

    If I close the query manager page & open a new one it will run again - giving the output recordset. When calling it from an ASP page - it runs fine once, then returns an empty recordset. If I kill the ASP sessions - it will run again - but only once unless I kill the ASP sessions again.

    I did a test to return the @@Fetch_status and it is coming back -1 on any second attempts to run the procedure per session. But, at the end of the procedure I close the cursor and deallocate the cursor.

    Any clues on why this cursor would still remain alive when the procedure is recalled by the same session?

    Many thanks!
    Last edited by DrewM; 01-08-05 at 05:03.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Post the code, please.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2005
    Posts
    36
    CREATE PROCEDURE dbo.getphotosbypage
    @UserID int,

    AS
    DECLARE @pagecounter int,
    @monthcounter int,
    @priorDate datetime,
    @photoID int,
    @comments varchar(500),
    @nextdate datetime,

    CREATE TABLE photos_temp
    (monthcount int,
    photoID int,
    comments varchar(500),
    dateuploaded datetime)

    set @monthcounter = 1

    DECLARE photos_cursor CURSOR FOR
    SELECT photoID,comments,dateuploaded FROM Photos
    WHERE UserID = @UserID
    ORDER BY Dateuploaded

    OPEN photos_cursor

    WHILE @@FETCH_STATUS = 0
    BEGIN

    FETCH NEXT FROM photos_cursor
    INTO @PhotoID, @comments, @Nextdate

    if datepart(mm,@nextdate) <> datepart(mm,@priordate) OR
    datepart(yy,@nextdate) <> datepart(yy, @priordate)
    BEGIN
    SET @monthcounter = @monthcounter + 1
    END
    If @priorID <> @photoID
    BEGIN
    insert into photos_temp (monthcount,photoID,comments,dateuploaded)
    values (@monthcounter, @photoID, @comments, @Nextdate)
    END
    SET @priordate = @Nextdate
    SET @priorID = @photoID
    END

    select * from photos_temp

    SET @photoID = null
    SET @comments = null
    SET @nextdate = null
    DROP TABLE photos_temp
    CLOSE photos_cursor
    DEALLOCATE photos_cursor

    GO

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest:
    Code:
    CREATE PROCEDURE dbo.getphotosbypage
       @UserID int,
    AS
    
    SELECT 
       (SELECT 1 + DateDiff(month, Min(o.dateuploaded), p.dateuploaded)
          FROM Photos AS o
          WHERE  o.UserID = p.UserID)) AS monthcount
    ,  photoID,comments,dateuploaded
       FROM Photos AS p
       WHERE UserID = @UserID
       ORDER BY Dateuploaded
    
    RETURN
    Note that your original procedure would number months based on activity, with no gaps in numbering (so an 11 month hiatus wouldn't disturb the sequence of the month count). This code would show the actual number of months elapsed since the user's original posting.

    -PatP

  5. #5
    Join Date
    Jan 2005
    Posts
    36
    Hi

    Thanks for the response - but that is what I want it do to - any hiatus in months should not increase the numbering. The actual proc works 100% as expected. That's not an issue.

    My problem is it will only run once per session. It's something related to the cursor not dying. I can only run the proc once - and second run returns no recordset.

    Thanks

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, sooner or later you are going to run into problems with:

    "CREATE TABLE photos_temp"

    This is going to fail if you have multiple simultaneous executions. You should use a table variable or a temporary table instead, both of which are specific to each users session and last only as long as the scope of the procedure.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2005
    Posts
    36
    The table shouldn't be an issue - I drop the table at the end of the procedure.

    But anyway - any clues on why the cursor won't die? This is what has me banging my head against the wall

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I still think you need to loose the cursor, the table, and the related shenanigans. Ok, how about:
    Code:
    CREATE PROCEDURE dbo.getphotosbypage
       @UserID int,
    AS
    
    SELECT 
       (SELECT Count(*)
          FROM Photos AS o
          WHERE  o.UserID = p.UserID
             and p.dateuploaded <= o.dateuploaded
          GROUP BY Convert(CHAR(7), o.dateuploaded, 121)) AS monthcount
    ,  photoID,comments,dateuploaded
       FROM Photos AS p
       WHERE UserID = @UserID
       ORDER BY Dateuploaded
    
    RETURN
    -PatP

  9. #9
    Join Date
    Jan 2005
    Posts
    36
    Thanks Pat,

    I tried to run your procedure - it's certainly a more elegant solution than my coding (I'm fairly new to SQL). It wouldn't run - gave an error that said more than one result - not allowed with a subquery.

    I'll look at your code because it's probably a good lesson for me to look at your approach, but beyond that my code does work fine - I don't have any issue with my code (of course it could be better I am sure)

    My question is not really related to getting the procedure to work as intended - it does exactly what I want it to do. The question is - has anybody ever ran into this situation. I run the procedure from either query manager or an ASP page and it runs fine once, then the next time it's called - returns nothing. The only way to get it to work again is to kill the session and re-run. I think it's related to the the cursor not deallocating. Right now I'm thinking this is a microsoft bug in SQL server - I hope there is a better answer than that.

    Has anybody come across this before?

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oops, my bad! Let's try:
    Code:
    CREATE PROCEDURE dbo.getphotosbypage
       @UserID int,
    AS
    
    SELECT 
       (SELECT Count(*)
          FROM (SELECT 1
             FROM Photos AS o
             WHERE  o.UserID = p.UserID
                AND  p.dateuploaded <= o.dateuploaded
          GROUP BY Convert(CHAR(7), o.dateuploaded, 121)) AS a) AS monthcount
    ,  photoID,comments,dateuploaded
       FROM Photos AS p
       WHERE UserID = @UserID
       ORDER BY Dateuploaded
    
    RETURN
    Without having your database, and the queries you were using, I'm not sure that I can find an answer for you. The code looks Ok at first blush, although I try desperately to avoid using cursors (and can't think of a time that I've ever used one in production code), so I'm no expert on that topic.

    -PatP

  11. #11
    Join Date
    Jan 2005
    Posts
    36
    Thanks Pat,

    I guess my run only once procedure must be a microsoft bug and will need to do what I want in a different way to avoid it. I tried your re-write of my code and I get this error when I try to update the procedure (syntax error)

    No column was specified for column 1 of 'a'

    Thanks

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Why do you need to DROP the table? And not using temp tables is really gonna get you one day. But if you insist that "your code is corerct and all", - at least consider this change (and don't forget to remove DROP TABLE at the end):

    ...
    if object_id('dbo.photos_temp') is not null
    delete dbo.photos_temp
    else
    CREATE TABLE photos_temp
    (monthcount int,
    photoID int,
    comments varchar(500),
    dateuploaded datetime)
    ...
    --DROP TABLE photos_temp
    ...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    SELECT
    (SELECT [my count]=Count(*)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  14. #14
    Join Date
    Jan 2005
    Posts
    36
    Thanks - I wasn't trying to say you were wrong on the tables thing. I'm new to SQL (2 days) and I thought I was creating a temporary table.

    My issue was not the table though - it was that the procedure will only ever run once per session. When it runs - it works perfectly, although I am sure my code is crappy - that's another issue though. It'll take me some time to be able to code SQL as well as you guys. Pat's re-write of my code showed me that there was obviously far better ways to code what I had in mind.

    If I could get pats code working then although it wouldn't resolve my actual question - it would solve my problem because I'd have something that worked more than once per session.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, then:
    Code:
    CREATE PROCEDURE dbo.getphotosbypage
       @UserID int,
    AS
    
    SELECT 
       (SELECT Count(*)
          FROM (SELECT 1 AS dummy_name
             FROM Photos AS o
             WHERE  o.UserID = p.UserID
                AND  p.dateuploaded <= o.dateuploaded
          GROUP BY Convert(CHAR(7), o.dateuploaded, 121)) AS a) AS monthcount
    ,  photoID,comments,dateuploaded
       FROM Photos AS p
       WHERE UserID = @UserID
       ORDER BY Dateuploaded
    
    RETURN
    -PatP

Posting Permissions

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