Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Jan 2004
    Posts
    19

    Unanswered: SP produces intermingled results

    I have a custom SP running from a PB application. When I am retrieving information for a certain client, a co-worker decides to run the same custom SP for another client before my results are displayed. When both results display, I end up with certain pieces of data from his client and he ends up with certain pieces of data from my client. What code in my SP do I need to prevent this from happening?
    tfil

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Post the SP in question, but as a hunch, - it's a pretty obvious design issue.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's that damned miracle module at work again....

    I hate when that happens.....

    Just a thought though....is there a user id involved in the Sproc?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I'll lay money on there being a permanent "temp table" somewhere in the mix. Something like:

    1) Put my data in "temp table"
    2) crucnch my data
    3) select * from "temp table"
    4) delete my data from "temp table" so no one sees it.


    Takers?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by MCrowley
    I'll lay money on there being a permanent "temp table" somewhere in the mix.
    Sounds like a better investment than the market....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2004
    Posts
    19
    Actually, there is a delete of the temp table at the beginning, then there are 4 data inserts into that temp table, then their is a select from that temp table which goes to report in Powerbuilder...at the end, their is a cleanup process which deletes the temp table....

    Could this be the problem:
    In the report, I have ID as an input parameter...the ID is being inserted into the temp table... when someone else accesses the report at the same time with a different ID, is this what's causing the same data to be in two different reports for different IDs ?
    tfil

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You got it

    And MCrowley could have retired by now

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    And if it weren't for all the money I pumped into Nortel at $50 a share.... ;-)

    Just to make sure, though. This temp table. Does the name of it begin with either # or ##? If it does, then we may not have the right answer. If not, then I think we got it.

  9. #9
    Join Date
    Jan 2004
    Posts
    19
    it doesn't
    tfil

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey, it may be a permanent-temporary too.

  11. #11
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by rdjabarov
    Hey, it may be a permanent-temporary too.
    Well .. that was easy to figure out .... wasnt it ... otherwise it would have been a miracle !!!!!
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If it doesn't start with # or ##, then it is not a temp table. As a matter of fact, it is unlikely (but possible) that you would get this error at all if it were a session-specific temporary table (not global).

    Avoid using regular database tables for storing process-related data in a multi-user environment. It will always get you into trouble. Convert your code to use #Temp tables and you should eliminate the problem.
    If it's not practically useful, then it's practically useless.

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

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I agree with Blindman. For this process, real temp tables are the way to go. Also, if you create a temp table inside a stored procedure, make sure to drop the temp table at the end of the stored procedure. If you do not explicitly drop the temp table, you can encounter problems if the procedure is run many times in rapid succession. The automatic cleanup of temporary tables is just a hair slower than the logout/login process in some cases.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Never seen that happen with temp tables not explicitly dropped, but I'll take your word for it. I always drop the tables at the end anyway so that if I need to copy the code out for testing in QA it will execute cleanly.
    If it's not practically useful, then it's practically useless.

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

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Doesn't dropping of a temp table cause a recompile? Never did it, ever since 6.5 came out. But if it's 2K environment, why bother with # or ## when you can have @ (unless you either do SELECT...INTO or INSERT...EXEC)?? And there is nothing to drop there either

Posting Permissions

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