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?

New line in query

Hi,
I was wondering if there is any way I can place a new line inside a query...
e.g.
select field1 + 'NEWLINE' + field2 from tablename
I want to place a new line between field1 and field2
Thanks in advanceselect field1 + char(13) + field2 from tablename|||Thanks for reply blindman

I have tried this already but it doesn't work :(
any other suggestions would be appreciated.

Thanks|||It works in query analyzer. What are you looking at the results in?

select 'a' + char(13) + 'b'|||I am using MSSQL 2005 Server Management Studio which replaces both the SQL Server 2000 Enterprise Manager and the Query Analyzer.

Thanks|||Odd. It still works for me.

select 'a' + char(13) + 'b'

--
a
b

(1 row(s) affected)|||Actually It works on the text mode result but not Grid mode ...still it doesn't save a and b on different lines in the database itself not sure why

Once I fetch the record it displays it on a single line...

On my other fields where I am saving text from my .net application textboxes it creates double boxes for a newline... I was wondering how would I create thoes double boxes :)|||Ahh, the grid is saving the carriage return, but it displays as a blank in grid mode.

-- Using Northwind

DECLARE @.MyText CHAR(26)
DECLARE @.MyText2 CHAR(26)
SET @.MyText = 'a' + char(13) + 'b'
PRINT @.MyText

SELECT ShipCity
FROM Orders
WHERE OrderID = 10248

UPDATE Orders
SET ShipCity = @.MyText
WHERE OrderID = 10248

SET @.MyText2 = (SELECT ShipCity
FROM Orders
WHERE OrderID = 10248)

PRINT @.MyText2

SELECT ShipCity
FROM Orders
WHERE OrderID = 10248

UPDATE Orders
SET ShipCity = 'Reims'
WHERE OrderID = 10248

Run this in text mode and you will see that the newline is saved.
Run this in grid mode and you will see that the newline is converted.

I hope this helps,|||Still it will show a one straight line when fetching that data ....what I am thinking now is create a tiny function in .net and add new lines there it did worked for my other data before....

"thank you all for the help guys this forum ROCKS :)"|||Why are you concerned with how it looks in Management Studio? Neither Management Studio or Query Analyzer is meant to be used as a user interface or reporting tool.