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 FerreiraThere 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|||xp_cmdshell, osql will creat their own spid...

Mostly they've been a royal pain in the donkey....|||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|||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|||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|||Sounds like the original design is the bigger problem...

Want to show us some code?