Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    5

    Unanswered: New session inside SP?

    Hi:

    I want to open a new session/connection inside the execution of a stored procedure. Is this possible ?
    I ask this because I need a new sesssion with its own transaction.

    Thanks,
    Rui Ferreira

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are ways to do this, but none of them are pretty. They all involve breaking at least one of the fundamental rules about transaction managment.

    Can you explain what you are trying to do in more detail? If I knew more about what you wanted, I could give you a clearer, simpler answer than trying to list all of the methods with their pros and cons for you to sort through!

    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    xp_cmdshell, osql will creat their own spid...

    Mostly they've been a royal pain in the donkey.....
    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
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    xp_cmdshell, osql will creat their own spid...

    Mostly they've been a royal pain in the donkey.....
    Yeah, that too.

    Just from a security standpoint, the OLE objects are much better. The problem with all of the solutions to this kind of problem is that they have so many caveats and potential "tiger trap" situations embedded in them that there isn't a simple answer.

    -PatP

  5. #5
    Join Date
    Sep 2004
    Posts
    5
    Quote Originally Posted by Pat Phelan
    There are ways to do this, but none of them are pretty. They all involve breaking at least one of the fundamental rules about transaction managment.

    Can you explain what you are trying to do in more detail? If I knew more about what you wanted, I could give you a clearer, simpler answer than trying to list all of the methods with their pros and cons for you to sort through!

    -PatP
    I have a stored procedure that generates sequential IDs based on an auxiliar table. I want to include this SP in its own transaction to avoid concurrency problems I'm having now. When one transaction that uses the SP lasts a little longer the table is locked too much time.
    I hope my explanation helps...

    Thanks,
    Rui Ferreira

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What sort of concurrency problems are you having? This sounds like a bad thing to me to try to sidestep any concurrency issues, because if they are causing problems now I think that side-stepping them will cause even bigger problems.

    -PatP

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sounds like the original design is the bigger problem....

    Want to show us some code?
    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.

Posting Permissions

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