When the client code calls a Stored Procedure (SP), a new connection is created. The first thing I typically do inside the SP is to do the
BEGIN TRAN
SET TRANSACTION ISOLATOIN LEVEL <value
....<sp code
Now, if the current SP calls another SP and I also do define a new transaction (BEGIN TRAN, SET TRANS...) inside that called SP (@@trancount = 2), what's the effect of the SET TRANS... statement inside that called SP.

When reading the help file, it says "SET TRANS...." ... "Controls the default transaction locking behavior for all Microsoft® SQL Server™ SELECT statements issued by a connection".

My question, when a calling SP calls another SP, will there be a new connection created or is another SET TRANS... statement inside a called SP really meaningless

Thx, although this wasn't really my question. Let me be more specific
1. I have a top-level SP A where I set the ISOLATION LEVEL
2. Then from there, I want to call another SP
3. In SP B, I want (!) to do a certain SELECT statement under a different ISOLATOIN LEVEL than has been set in SP

My questions
1. What happens when SP A calls SP B and inside of B a new BEGIN TRAN is defined. Will the calling of B by A establish a new connection ? Then, I would guess, I can set the ISOLATION LEVEL again. However, MS-SQL Help says the ISOLATION LEVEL setting is set at the connection level

2. If, in the above example, in SP A, I set ISOLATION LEVEL SERIALIZABLE and in SP B (called from A), I want to do a SELECT using ISOLATION LEVEL READ UNCOMMITTED... do I lose the transactional integrity that 'SERIALIZABLE' provided. Does that mean for nested SPs, I would always have to apply the highest ISOLATION LEVEL needed at any of the nested SPs, otherwise they would be kind of 'downgraded' to READ UNCOMMITTED if I were to specify that in a lower-level, nested SP (see example above)

Thanks


----- SriSamp wrote: ----

You do not need to do this in each SP. It is enough if you do it when yo
establish the connection. This ensure that all calls for the duration of th
transaction follow the set behavior. You can override it if need be in
particular code block
--
HTH
SriSam
Please reply to the whole group only
http://www32.brinkster.com/srisam

"Peter" <anonymous@discussions.microsoft.com> wrote in messag
news:9CA13D5B-24FE-418D-B646-83F0B7D7DC37@microsoft.com..
[quoted text, click to view]
transaction (BEGIN TRAN, SET TRANS...) inside that called SP (@@trancount
2), what's the effect of the SET TRANS... statement inside that called SP
[quoted text, click to view]
default transaction locking behavior for all Microsoft® SQL ServerT SELEC
statements issued by a connection"
[quoted text, click to view]
connection created or is another SET TRANS... statement inside a called S
really meaningless

Hello,

Say you have Two transaction, one on Isolation A.=20
Isolation x will run as Isoliation A until it finds its=20
corresponding end transation.

Now if you have a new transaction of Isolation (B) inside=20
Isolation A, it is still wrapped up in Isolation A. So it=20
will run internally as Isolation B (with all the Isolation=20
Level benefits / contraints), then at the end End=20
Transaction, puts it back into the control of Isolation A.

Apologies if seems a bit complex.

You will not need another connection, you can nest=20
transactions to your hearts content (although it gets very=20
hard for SQL Server), all will happen is that your log=20
file will keep on growing until it gets the final End=20
Transaction (or rollback) of the top level transaction.

If you wish we can run through some scenarios. My email is=20
little_flowery_me@hotmail.com.

J


[quoted text, click to view]
statement under a different ISOLATOIN LEVEL than has been=20
set in SP A
[quoted text, click to view]
a new BEGIN TRAN is defined. Will the calling of B by A=20
establish a new connection ? Then, I would guess, I can=20
set the ISOLATION LEVEL again. However, MS-SQL Help says=20
the ISOLATION LEVEL setting is set at the connection level.
[quoted text, click to view]
ISOLATION LEVEL SERIALIZABLE and in SP B (called from A),=20
I want to do a SELECT using ISOLATION LEVEL READ=20
UNCOMMITTED... do I lose the transactional integrity=20
that 'SERIALIZABLE' provided. Does that mean for nested=20
SPs, I would always have to apply the highest ISOLATION=20
LEVEL needed at any of the nested SPs, otherwise they=20
would be kind of 'downgraded' to READ UNCOMMITTED if I=20
were to specify that in a lower-level, nested SP (see=20
example above).
[quoted text, click to view]
code>> Now, if the current SP calls another SP and I also=20
do define a new
[quoted text, click to view]