Nesting SQL Transactions

Hopefully someone can point me in the right direction, I've been searching on the net for the answer to this question and can't seem to come up with anything.

First, I'm using ASP.NET 2.0 and Visual Studio 2005 with a SQL Server 2000 backend.

My SQL database is relational and is used to store names, addresses, etc of other companies.

What I need to be able to do is have 2 transactions, one nested within the other. In pseudo-code:

BEGIN TRANSACTION1

BEGIN TRANSACTION2

INSERT INTO COMPANY TABLE

COMMIT TRANSACTION2 -OR- ROLLBACK TRANSACTION2

GET COMPANYID JUST ADDED

PERFORM REMAINING INSERTS

COMMIT TRANSACTION1 -OR- ROLLBACK TRANSACTION 1 & 2

Right now I have everything grouped into one VB.NET transaction, which doesn't work because the company is not actually added until the transaction reaches commit. Therefore, I can't retrieve the companyID halfway through.

Is what I'm trying to do even possible? Thanks in advance for the help!

BEGIN TRANSACTION

INSERT INTO COMPANY TABLE

GET COMPANYID JUST ADDED

PERFORM REMAINING INSERTS

COMMIT TRANSACTION -OR- ROLLBACK TRANSACTION

dim conn as new sqlconnection("{ConnectString}")
conn.open
dim cmd as new sqlcommand("INSERT INTO Table1(column1) VALUES (@.col1) SELECT SCOPE_IDENTITY()",conn)
cmd.parameters.add("@.col1",sqldbtype.{Whatever}).value={Whatever}
dim MyID as integer=cmd.executescalar
dim cmd2 as new sqlcommand("INSERT INTO Table2(Table1ID,col2) VALUES (@.MyID,@.MyVal)",conn)
cmd2.parameters.add("@.MyID",sqldbtype.integer).value=MyID
cmd2.parameters.add("@.MyVal",sqldbtype.{whatever})
for loop here...
cmd2.parameter("@.MyVal").value={whatever}
cmd2.executenonquery
next
conn.close

Now wrap that in try/catch with a transaction and you are all set.|||

Motley-

Thanks for pointing me in the right direction! At first I wasn't sure exactly how to implement what you gave me with what I already have. After some trial and error and info fromthis site I was able to get my transaction to work exactly as I needed it to.

Thanks again for the help!

NESTED TRANSACTIONS!

In case of nested transactions, will the @.@.TRANCOUNT value be always 0
if the entire transaction is rolled back at the very end?
Thanks,
ArpanIf I understand the question correctly, you are wondering what the value of
@.@.TRANCOUNT will be when you issue a ROLLBACK TRANSACTION at some point in
the processing before a COMMIT. If this is the question, then @.@.TRANCOUNT's
value will be 0.
"Arpan" wrote:

> In case of nested transactions, will the @.@.TRANCOUNT value be always 0
> if the entire transaction is rolled back at the very end?
> Thanks,
> Arpan
>|||Thanks, Shahryar, for your response. I know that ROLLBACK at some point
of time before a COMMIT statement will set @.@.TRANCOUNT to 0 but will
@.@.TRANCOUNT's value ALWAYS be 0 at the END OF A TRANSACTION assuming
that the transaction isn't COMMITted at the end?
Thanks,
Regards,
Arpan

Nested transactions question.

Hope I am posting in the right forum. If I understand correctly, a ROLLBACK TRAN statement rolls all transactions (if they are nested) back to the original BEGIN TRAN.

I have a situation when an SP uses a transaction, performs a series of operations inside that transaction, including a call to a different SP, which uses a distributed transaction. If the transaction inside the child SP fails and needs to be rolled back, I get a warning message saying that the tran count on the way out is less than that on the way in. No problem, since it's not fatal. But the problem manifests when I attempt to use the SQL Agent to schedule a job to run the parent SP. It fails on that warning message, interpreting it as an error.

I was thinking of disabling the distributed transaction inside the child SP, and just have the transaction in the parent SP, which, again if I understand correctly, should be escalated to a distributed transaction once the child SP is called. The child SP will raise an error (if it's a real error) and then the trnasaction in the parent SP will handle the rollback of everything.

The reason for this elaborate setup is that I need to cycle through a cursor (yes, I know, sloppy, can't see an alternative) in the parent SP, and each iteration begins and commits (or rolls back) a transaction.

Wil this work? Can anyone suggest a better way?

will this help.

select name
into #tb
from master..systypes
go
select * from #tb
go
declare cc cursor forward_only
for select name from #tb

declare @.name sysname
open cc
fetch next from cc into @.name

while @.@.fetch_status=0
begin
begin tran
delete #tb
where name=@.name and name!='sysname'
if @.@.error=0
commit tran
else
rollback tran

fetch next from cc into @.name
end
close cc
deallocate cc
go
select * from #tb
select @.@.trancount
go

if object_id('tempdb..#tb') is not null
drop table #tb
go

|||Hmmm.... Not sure I understand. This is more or less the code I have. Except for where you have "DELETE #tb WHERE..." I have "EXEC spMyStoredProc @.myInputParam". and inside "spMyStoredProc" there is a distributed tran. So the @.@.TRANCOUNT at the beginning of spMyStoredProc is equal to 1. But inside the SP, let's say the transaction fails for whatever reason, and a ROLLBACK is called. The @.@.TRANCOUNT is set to 0 by the ROLLBACK statement. And now @.@.TRANCOUNT at the end of the SP is less than it was at the beginning. That generates a warning message. But apparently the SQL Agent that handles the Job execution treats it as an error. Or the error that caused the ROLLBACK was raised - I am not sure. In any case - Job ends. But I need it to continue. If I just run the code from QueryAnalyzer - it's ok. It generates warnings, but runs through all the cycles. How can I avoid being bounced out of a SQL Job?|||

(Hope I am posting in the right forum. If I understand correctly, a ROLLBACK TRAN statement rolls all transactions (if they are nested) back to the original BEGIN TRAN.)

That is actually not correct if you use Transaction Save Points which you can roll back as needed after service pack 3a of SQL Server 2000. Now to your question add Transaction save points to any Transaction you don't want rolled back with the error code. Run some tests, transaction is a unit of work but the vendors like Microsoft added Save Points which enable nested Transaction without a roll back to one, with Save Points if you fail at number 100 you will have 99 completed. Run a search for Save Points in the BOL. Hope this helps.

|||

Thanks for pointing out the SavePoints. I did research tehm, but I am not sure if this applies here. I guess I am not explaining the issue clearly enough. I DO want the transactions, both of them, rolled back if an error is encountered in either. However, I have a scenario where the parent transaction is in a cursor (just like that code sample above shows). Each cursor iteration begins and ends a transaction. Each of those transactions calls an SP with another (child) transaction. If anything breaks, I want the transactions to roll back, then I want to go on to the next cursor iteration. If I execute the code in QueryAnalyzer - it works fine. If I schedule the execution via an SQL Job - the Job abends on the first error, rolls back the transaction but does NOT go on to the next cursor iteration. My guess is, this is because the warning that is raised by the child SP (standard non-fatal warning that tran count at the beginning of the SP does not match that at the end) is treated as a fatal error by the SQL Agent, and it terminates the Job.

I could be wrong, and it could be the actuall error that caused the transaction rollback in the first place is being raised, and the Agent may be reacting to that. Whatever the case - My Job stops the cursor execution and exits with an error. Which is something I would like to avoid.

|||

If you can run it and the Agent is not running it, that maybe permissions related when you run it is it running under your admin level permissions but when the Agent runs it, it is running under the Agent's permissions so it will not continue after an error in transactions. You can enable xp_cmdshell in the Surface area configuration tool then create a proxy account for the Agent with your admin level permissions. Run a search for Agent proxy account in the BOL, xp_cmdshell is finally documented but disabled by default so you need to enable it in the Surface area configuration tool. If the permission does not solve the problem then you need to separate your transactions into blocks and connect them with sp_executesql or exec statements. Hope this helps.

|||Thanks. My agent is running with admin permissions already, though :-( Final question - my child SP is written in such a way that I can pass it a flag that governs whether or not I want to use transactions (did that for testing). If I disable the child transaction (its a distributed tran), will a rollback on the parent also roll back the changes made by the sub-called SP? I think that the parent transaction will be automatically escalated to distriburted tran in this case, but I am not sure. I can make it into a distributed transaction manually, if needs be. From what I can see - this would be my only option unless I want to duplicate a lot of code.|||

(will a rollback on the parent also roll back the changes made by the sub-called SP?)

No if each is separated by save points and if you are not using MSDTC maybe you should look into it.

|||

They are not separated by save points.

Main SP code:

Code Snippet

OPEN curCustomersToCancel
FETCH NEXT FROM curCustomersToCancel INTO @.CustID
WHILE @.@.FETCH_STATUS = 0
BEGIN
BEGIN DISTRIBUTED TRAN

EXEC @.ReturnCde = spCancelServices @.CustID
If @.ReturnCde <> 0
Begin
IF(@.@.TRANCOUNT > 0) ROLLBACK TRAN

Select @.SpErrDesc = @.retmsg
PRINT '******* @.SpErrDesc = ' + @.SpErrDesc
BREAK
End

Else

Begin

IF(@.@.TRANCOUNT > 0) COMMIT TRAN

End

FETCH NEXT FROM curCustomersToCancel INTO @.CustID

END
CLOSE curCustomersToCancel
DEALLOCATE curCustomersToCancel

The code in spCancelServices has no transactions - it's just a series of SQL statements. Will this properly roll back all modifications made inside spCancelServices in case of an error?

|||

In the link below download the file SQLServer2005_US_ALL on the left side and in there check samples for chapter 13 and 14 in the notepad files and the PDF note that one trigger in chapter 13 can do what you want. And you did not include @.@.Error in your code, if I remember correctly Fetch is an implicit transaction per ANSI SQL. The author Dusan Petkovic got bad book title but he covered SQL Server transaction better than most writers. Hope this helps.

http://www.mhprofessional.com/product.php?cat=112&isbn=0072260939

Nested Transactions - what for?

Hi
Can someone explain what nested transactions can be used for? I know about
the outer an inner transaction but you still either commit or rollback all
transactions.
In Oracle you can start autonomous transaction within another transaction
and any commit or rollback statement affects its own transaction.
So - I am not looking at explanation how to create nested transactions but
what could be a practical use of them?I think that this is largely a leftover from the original Sybase design.
As you know, there are no nested transactions in SQL Server, semantically speaking.
Why did they put in the syntax then? I guess you had to be in that Sybase design meeting to be able
to answer that question. My guess is that it allow for code modules to call each other. One stored
proc programmer want to transaction-protect the code. The proc he is writing will call another proc
which is also transaction-protected. Stuff like that...
Note that savepoint is another matter. This gives the ability to perform partial rollbacks. This is
IMO much more useful then doing partial commits. I find the thought of a partial commit a bit scary,
but perhaps this is because the feature isn't available in SQL Server.
One scenario I would consider it useful, though, is when you want to log actions even if you
rollback the transaction. In SQL Server 2000, we can often achieve the same functionality logging to
a table variable (which survives a ROLLBACK).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Witold" <witoldi@.shaw.ca> wrote in message news:bWo8d.211357$%S.80172@.pd7tw2no...
> Hi
> Can someone explain what nested transactions can be used for? I know about
> the outer an inner transaction but you still either commit or rollback all
> transactions.
> In Oracle you can start autonomous transaction within another transaction
> and any commit or rollback statement affects its own transaction.
> So - I am not looking at explanation how to create nested transactions but
> what could be a practical use of them?
>

Nested Transactions - what for?

Hi
Can someone explain what nested transactions can be used for? I know about
the outer an inner transaction but you still either commit or rollback all
transactions.
In Oracle you can start autonomous transaction within another transaction
and any commit or rollback statement affects its own transaction.
So - I am not looking at explanation how to create nested transactions but
what could be a practical use of them?
I think that this is largely a leftover from the original Sybase design.
As you know, there are no nested transactions in SQL Server, semantically speaking.
Why did they put in the syntax then? I guess you had to be in that Sybase design meeting to be able
to answer that question. My guess is that it allow for code modules to call each other. One stored
proc programmer want to transaction-protect the code. The proc he is writing will call another proc
which is also transaction-protected. Stuff like that...
Note that savepoint is another matter. This gives the ability to perform partial rollbacks. This is
IMO much more useful then doing partial commits. I find the thought of a partial commit a bit scary,
but perhaps this is because the feature isn't available in SQL Server.
One scenario I would consider it useful, though, is when you want to log actions even if you
rollback the transaction. In SQL Server 2000, we can often achieve the same functionality logging to
a table variable (which survives a ROLLBACK).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Witold" <witoldi@.shaw.ca> wrote in message news:bWo8d.211357$%S.80172@.pd7tw2no...
> Hi
> Can someone explain what nested transactions can be used for? I know about
> the outer an inner transaction but you still either commit or rollback all
> transactions.
> In Oracle you can start autonomous transaction within another transaction
> and any commit or rollback statement affects its own transaction.
> So - I am not looking at explanation how to create nested transactions but
> what could be a practical use of them?
>
sql

Nested transactions

I am writing a program using VC++ 6.0 and SQL 2000 and I am trying to use nested transactions. I have 1 outer transaction and the 2 inner transactions are in sepetrate function calls inside the outer transaction. I have something like this:

BEGIN TRANSACTION;

if (!functioncall1()) // commit if function suceeds, otherwise rollback
{
Rollback Transaction;
return;
}

if (!functioncall2()) // commit if function suceeds, otherwise rollback
{
Rollback Transaction;
return;
}

COMMIT TRANSACTION ;

Both functions contain a complete transaction inside the function call. If either function fails however, I want to do a rollback of the entire transaction. This is not happening though. If functioncall1 suceeds and the transaction in that function commits, then if I do a rollback during functioncall2, the transaction in functioncall1 is not rolled back. This seems to be directly opposite of the SQL help for transaction. Am I missing something obvious here?This gets rather complicated to explain, but I'll give it a shot.

SQL transactions don't nest in the strict (relational algebra) sense of the word. When you "nest" SQL transactions, they form something more like a procedure call stack where the COMMIT behaves like a return and the ROLLBACK behaves something like throwing an execption. The first rollback to come along basically wipes you back to step 1, before the first BEGIN TRANSACTION occured.

There were reasons for this behavior, once upon a time. There is still a reasonable logical arguments for maintaining the behavior, even though it goes so badly against the mental model used by third generation programming tools (like VB, VC, C#, etc).

The simplest solution I see to your problem is to avoid nesting, and use a try/throw/catch model to allow the application side logic to match the database side.

-PatP|||I have tried using the nested transactions and I can get them to work in the SQL query analyzer, but when I try to do the same thing in the application, the rollback will undo the work done in the inner most transaction, but not to the outer transaction.|||Ah... If that is the case, please post the Transact-SQL for what you want to do. It will be much easier to help you translate the Transact-SQL to C than to guess how it is different from what you've posted. What you posted won't behave the way you want it to, because SQL transactions don't work that way.

-PatP|||When I call function 1, the function completes and the transaction is completed. Function 2 doesn't finish and a rollback occurs inside the function, but the changes that were done in function 1 never get undone.

The SQL that I can get to work in the query analyzer is:

BEGIN Transaction Test1
Begin Transaction Test2
Insert into TABLE2
Commit Transaction Test2

BEGIN Transaction Test3
Insert into TABLE3
Commit Transaction
Rollback Transaction

Here is the C++ code that I can't get to work:

void main ( )
{
ExecuteSQL(_T("BEGIN TRANSACTION "));
if (!Function1( ) ) //If this function fails, then rollback
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
return 0;
}

ExecuteSQL(_T("BEGIN TRANSACTION "));
if (!Function2( ) ) //If this function fails, then rollback
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
return 0;
}

// commit transaction and return success
ExecuteSQL(_T("COMMIT TRANSACTION "));
return 1;

////////////////////////////////////////////////////////////////////////////////////
int Function1( )
{

sqlStatement.Format(_T("INSERT INTO table1( VARIABLE LIST) FROM TABLE1");

try
{
ExecuteSQL(_T("BEGIN TRANSACTION "));
nRowCount = ExecuteSQL(sqlStatement);
}
catch (CException *e)
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
return 0;
}

if (nRowCount != 1)
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
return 0;
}

sqlStatement.Format(_T("DELETE FROM TABLE2 ");

try
{
nRowCount = ExecuteSQL(sqlStatement);
}
catch (CException *e)
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
e->Delete();
return 0;
}


// commit transaction and return success
ExecuteSQL(_T("COMMIT TRANSACTION "));
return 1;
}

////////////////////////////////////////////////////////////////////////////////////
int Function2( )
{
// Clear any previous errors
ResetError();

// Start a transaction
try
{
ExecuteSQL(_T("BEGIN TRANSACTION "));
}
catch (CException *e)
{
return 0;
}
CString sqlStatement;
sqlStatement.Format( _T("DELETE FROM TABLE2 ");

try
{
ExecuteSQL(sqlStatement);
}
catch (CException *e)
{
e->Delete();
ExecuteSQL(_T("ROLLBACK TRANSACTION"));
return 0;
}
}
sqlStatement.Format( _T( "INSERT INTO TABLE3( )"),
TRY
{
ExecuteSQL(sqlStatement);
}
CATCH( CException *e)
{
ExecuteSQL(_T("ROLLBACK TRANSACTION"));
return 0;
}
END_CATCH

// commit the changes
TRY
{
ExecuteSQL( _T("COMMIT TRANSACTION"));
}
CATCH( CException, e)
{
return 0;
}
END_CATCH

return 1;
}

Nested Transactions

Can anyone verify for me whether SQL Server CE 2.0 does or does not support nested Transactions when using the SQLServerCe Data Provider? The SQL Server CE Books Online documentation definitely states that SQL Server CE supports nested Transactions, but the example provided uses ADOCE Data Provider. The error message that I get when trying to begin a new Transaction with an existing Transaction still uncommitted on the same SqlCeConnection is "SQL Server CE does not support parallel transactions". Is it not possible to nest Transactions with SQLServerCE Data Provider?

SQL Server CE doesn't support nested transactions. Meaning you can't start a transaction B inside transaction A. Can you please point us the help link that you came across?

Thanks

Raja

|||

SQL Server CE 2.0 Books Online -

under the topic "Using Transactions", the second bullet point under that topic states:

"In SQL Server CE, transactions can be nested up to five levels deep."

under the topic "Transactions", the second bullet point states:

"When a maximum of five levels of transaction nesting have been reached, StartTransaction returns an error indicating that no further transactions can be started. "

However, under the topic "Data Provider for SQL Server CE", subheading "Provider Limitations" I see that the third bullet point states:

"No support for nested or parallel transactions. "

So my understanding is that the whether the SQL CE database engine supports transactions or not (I'm still uncertain of this), the SqlServerCe Data Provider explicitly does not. I am curious to know if this situation remains true in SQL Server 2005 Mobile Edition. If I can use nested Transactions with SqlServerCe Data Provider in 2005 Mobile Edition, my best course may be to upgrade my database.

thanks,

Max

|||

Thanks for the information. I didn't know that.

SQL Mobile doesn't support nested transactions either through oledb provider or ADO. You can refer to the following links:

http://msdn2.microsoft.com/en-gb/library/ms174571.aspx

http://msdn2.microsoft.com/en-us/library/ms174044.aspx

Thanks

Raja

Nested Transactions

Hello! Sorry if I choose wrong forum for this post.
I have next scenario:

Transaction1

Transaction2

Commit Transaction2
Transaction3

Commit Transaction3

Commit Transaction1 I wanna implement it in C# code (.NET 1.1, MS SQL 2000):

IDbConnection connection = new OleDbConnection(connectionString);

IDbTransaction transaction = null;

connection.Open();

/* NOTE: I can't use something like this:

* transaction outter = connection.BeginTransaction();

* transacrion inner = connection.BeginTransaction();

* // Here I'm getting an error: OleDB doesn't support parallel transactions,

* // though I wanna create nested one.

*/

// So, I decided to turn implicit transactions mode on in hope it should help:

IDbCommand bt = connection.CreateCommand();

bt.CommandText = " SET IMPLICIT_TRANSACTIONS ON; BEGIN TRANSACTION;";

bt.ExecuteNonQuery();

transaction = connection.BeginTransaction();

IDbCommand command = connection.CreateCommand();

command.Transaction = transaction;

command.CommandType = CommandType.Text;

command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description');";

command.ExecuteNonQuery();

command.CommandText = "SELECT @.@.TRANCOUNT;";

int transCount = (int)command.ExecuteScalar(); // It's equal to 2 here, seems to be OK.

transaction.Commit();

// Let's start the second "nested" transaction

IDbTransaction transaction1 = connection.BeginTransaction();

IDbCommand command1 = connection.CreateCommand();

command1.Transaction = transaction1;

command1.CommandType = CommandType.Text;

command1.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description');";

command1.ExecuteNonQuery();

command1.CommandText = " SELECT @.@.TRANCOUNT; ";

transCount = (int)command1.ExecuteScalar(); // WOW! Now it's already equal to 1 here.

transaction1.Commit();

// Well, here I wanna close outter transaction, but... I'll get exception: There is nothing to commit here

bt = connection.CreateCommand();

bt.CommandText = "Commit TRANSACTION";

bt.ExecuteNonQuery();

Well, I know that SQL Server has no support for nested transactions. Nesting of transactions only increments @.@.TRANCOUNT and it is the final commit that has control over the outcome of the entire transaction. And I can't use the new TransactionScope class in .NET Framework 2.0 which has promotable transactions concept.

Please help me: How can I implement required operations?

You could try using a ServicedComponent and using COM+ functionalities to perform that type of transactions. I don't know if it will work well with OleDB - you just have to try it.|||

Thank you Miguelb for reply. I found solution much simpler :). If somebody find it helpful this is it:

public static void InitiateTransactionsChain(string connectionString)
{
using (IDbConnection connection = new OleDbConnection(connectionString))
{
IDbCommand mostOutter = null;
connection.Open();
try
{
connection.Open();

mostOutter = connection.CreateCommand();
mostOutter.CommandText = "SET IMPLICIT_TRANSACTIONS ON;"; // There is no BEGIN TRANSACTION here
mostOutter.ExecuteNonQuery();
ExecuteNestedTrans(connection, 100, "help me"); // see below
ExecuteNestedTrans(connection, 101, "hope it is OK"); // see below
// If any exception occurs previous transactions will be rolled back!
// ExecuteNestedTrans(connection, 102, null); // see below
mostOutter = connection.CreateCommand();
mostOutter.CommandText = "Commit TRANSACTION";
mostOutter.ExecuteNonQuery();
}
catch (Exception ex)
{
// if something goes wrong, we can easy roll back everything :
if (mostOutter == null)
return;
mostOutter = connection.CreateCommand();
mostOutter.CommandText = "ROLLBACK TRANSACTION";
mostOutter.ExecuteScalar(); // That's all...
}
}
}
// Here is ExecuteNestedTrans:
public static void ExecuteNestedTrans(IDbConnection connection, int value, string description)
{
IDbCommand fakeTransaction = null;
// Let's cheat here: Increase the counter (@.@.TRANCOUNT):
fakeTransaction = connection.CreateCommand();
fakeTransaction.CommandText = "BEGIN TRANSACTION;";
fakeTransaction.ExecuteNonQuery();

IDbTransaction innerTransaction = null;
try
{
innerTransaction = connection.BeginTransaction();
IDbCommand command = connection.CreateCommand();
// Somewhere here transcount somehow will be decremented by 1
// Strange, isn't it?
command.Transaction = innerTransaction;
command.CommandType = CommandType.Text;
command.CommandText = String.Format("Insert into Region (RegionID, RegionDescription) VALUES ({0}, '{1}');", value, description);
command.ExecuteNonQuery();
innerTransaction.Commit();
}
catch (Exception exInner)
{
if (innerTransaction != null)
{
innerTransaction.Rollback();
throw exInner;
}
}
// NOTE: There is no need to commit fakeTransaction.
// It will be commited by something somewhere deep in .NET
// That is why code below is commented
//fakeTransaction = connection.CreateCommand();
//fakeTransaction.CommandText = "COMMIT TRANSACTION;";
//fakeTransaction.ExecuteNonQuery();
if (description == null)
throw new Exception("He-he!");
}

That's all :). Thanks for the time You spent for me.

Nested Transactions

Hi All!
I am looking to rollback a nested transaction.
Is this possible? I thought it was, but I keep getting errors.
For example, when I run the following code ..... I get the following result
---
create table XX (data varchar(20))
begin tran One
insert into XX (data) values ('Tran1')
begin tran Two
insert into XX (data) values ('Tran2')
rollback tran Two
insert into XX (data) values ('Tran1')
Commit tran One
go
select * from XX
print @.@.trancount
RESULT ****************************************
********
Msg 6401, Level 16, State 1, Line 8
Cannot roll back Two. No transaction or savepoint of that name was found.
data
--
Tran1
Tran2
Tran1
The result set I want to get is this...
data
--
Tran1
Tran1
How do I roll back a transaction -- inside of another transaction -- and sti
ll keep all of the outer transaction's statements executing?Use SAVE TRAN in you "inner transaction" instead of BEGIN TRAN. That gives y
ou a savepoint to roll
back to.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:uIP6f6FSGHA.4976@.TK2MSFTNGP11.phx.gbl...
Hi All!
I am looking to rollback a nested transaction.
Is this possible? I thought it was, but I keep getting errors.
For example, when I run the following code ..... I get the following result
---
create table XX (data varchar(20))
begin tran One
insert into XX (data) values ('Tran1')
begin tran Two
insert into XX (data) values ('Tran2')
rollback tran Two
insert into XX (data) values ('Tran1')
Commit tran One
go
select * from XX
print @.@.trancount
RESULT ****************************************
********
Msg 6401, Level 16, State 1, Line 8
Cannot roll back Two. No transaction or savepoint of that name was found.
data
--
Tran1
Tran2
Tran1
The result set I want to get is this...
data
--
Tran1
Tran1
How do I roll back a transaction -- inside of another transaction -- and sti
ll keep all of the outer
transaction's statements executing?|||You can't. From SQL BOL:
"Naming multiple transactions in a series of nested transactions with a
transaction name has little effect on the transaction. Only the first
(outermost) transaction name is registered with the system. A rollback
to any other name (other than a valid savepoint name) generates an
error. None of the statements executed before the rollback are in fact
rolled back at the time this error occurs. The statements are rolled
back only when the outer transaction is rolled back."|||Hi rmg66
Rollback can only be used to roll back to the outermost begin tran, or to a
savepoint. Note that there really is not concept of nesting transactions in
SQL Server. Please read about transaction control in the Books Online.
begin tran One -- the label here is useless
insert into XX (data) values ('Tran1')
SAVE tran Two -- this does not start a new transaction, it only marks a spot
in the log that we can roll back to
insert into XX (data) values ('Tran2')
rollback tran Two -- roll back to named savepoint
insert into XX (data) values ('Tran1')
Commit tran One -- the label here is useless
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:uIP6f6FSGHA.497
6@.TK2MSFTNGP11.phx.gbl...
Hi All!
I am looking to rollback a nested transaction.
Is this possible? I thought it was, but I keep getting errors.
For example, when I run the following code ..... I get the following result
---
create table XX (data varchar(20))
begin tran One
insert into XX (data) values ('Tran1')
begin tran Two
insert into XX (data) values ('Tran2')
rollback tran Two
insert into XX (data) values ('Tran1')
Commit tran One
go
select * from XX
print @.@.trancount
RESULT ****************************************
********
Msg 6401, Level 16, State 1, Line 8
Cannot roll back Two. No transaction or savepoint of that name was found.
data
--
Tran1
Tran2
Tran1
The result set I want to get is this...
data
--
Tran1
Tran1
How do I roll back a transaction -- inside of another transaction -- and sti
ll keep all of the outer transaction's statements executing?

Nested Transaction: how to commit the outer SP even if the inner ones fails.

Since some days I'm facing problems with the nested transactions and I read the they are not fully supported in sql server 2005 so I'd need an help from a more experienced SS programming.

My outer SP must live inside a transaction. It calles an another SP but IT MUST NOT ROLLBACK in case the INNER SP fails.

My nested SP, let's call it INNERSP is quite complex because it involves INSERT-UPDATE-DELETE, mathematic calculation and calls other SP as well.

Of course I want to make the INNERSP error-proof and I'd like to wrap it inside a try..catch structure.

If my INNERSP is:

BEGIN TRY

Insert ... --this insert cause an error that is trapped in the BEGIN CATCH

BEGIN TRANSACTION
COMMIT

END TRY

BEGIN CATCH
ROLLBACK TRANSACTION

END CATCH

The Rollback statement roll back also the transaction of the caller, but for me it is not acceptable.

I was investigating on the SAVE TRANSACTION statement but if I use it the @.@.TRANCOUNT is not decremented and my OUTERSP ends with a @.@.TRANCOUNT that is not zero, but from what I know "Nothing is actually committed until @.@.TRANCOUNT = 0"

Any helps is more that welcome!!
Thank you

Marina B.

Try putting your begin transaction statement before the work that you want inside of the transaction.|||

Interesting problem. It appears that even if an error in the called procedure is caught, the calling procedure's transaction still knows about it somehow:

if object_id('x') is not null drop table x

go

create table x(x1 int)

go

if object_id('px') is not null drop proc px

go

create proc px as

begin try

insert x values ('x')

end try

begin catch

print 'caught px'

end catch

go

if object_id('py') is not null drop proc py

go

create proc py as

begin tran

begin try

exec px

print @.@.error

insert x values (2)

commit

end try

begin catch

print 'caught py'

print error_message()

rollback

end catch

go

exec py

select * from x

go

/*

caught px

0

caught py

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

*/

|||

Not surprisingly, this behavior is my design. More importantly, this only applies to "fatal" errors. Per BOL:

"A transaction enters an uncommittable state inside a TRY block when an error occurs that would otherwise have ended the transaction. For example, most errors from a data definition language (DDL) statement (such as CREATE TABLE), or most errors that occur when SET XACT_ABORT is set to ON, terminate the transaction outside a TRY block but make a transaction uncommittable inside a TRY block."

This script is changed from the one above: the error that occurs in px is not a "fatal" error (the insert of "x" into an integer column is considered a syntax error and is fatal), and after it is caught, the py procedure does complete the transaction, resulting in a new row being inserted into table x:

set nocount on

go

if object_id('x') is not null drop table x

go

create table x(x1 int primary key)

go

if object_id('px') is not null drop proc px

go

create proc px as

begin try

insert x values (1)

end try

begin catch

print 'caught px'

print error_message()

print xact_state()

end catch

go

if object_id('py') is not null drop proc py

go

create proc py as

begin tran

begin try

insert x values (1)

exec px

print @.@.error

commit

end try

begin catch

print 'caught py'

print error_message()

rollback

end catch

go

exec py

select * from x

go

/*

caught px

Violation of PRIMARY KEY constraint 'PK__x__177C9889'. Cannot insert duplicate key in object 'dbo.x'.

1

0

*/

|||

SQL Server does not support autonomous transactions. Only way is to write an extended SP/SQLCLR SP to make a loopback connection to the database and call the inner SP without enlisting in same transaction. This technique of course has lot of disadvantages and side-effects. For one, you could create a distributed deadlock that is undetectable by SQL Server. You might end up blocking your own transaction consuming valuable resources on the server and so on.

Another technique is to use a table variable to store the results from the inner SP. Since table variables do not participate in user transactions, any subsequent COMMIT or ROLLBACK will not affect the data in the table variable. You can then retrieve the rows from the table variable after the transaction or in the CATCH block successfully. This will work from SQL Server 2000 onwards except there is no TRY...CATCH there.

|||

Hello everybody,

thankx for the answers .

By the way I found another solution that solve my problem but because SQL server programming is not my main skill I would like to know what to you think about it.

select @.@.trancount, 'Entering'

begin transaction

begin try

-- Detect if the procedure was called from an active transaction and save that for later use.In the procedure, @.TranCounter = 0 means there was no active transaction and the procedure started one.

-- @.TranCounter > 0 means an active transaction was started before the procedure was called.

DECLARE @.TranCounter INT;

DECLARE @.i INT;

SET @.TranCounter = @.@.TRANCOUNT;

select @.TranCounter,'Entering s.p. 1st level from broker'

IF @.TranCounter > 0

BEGIN

-- Procedure called when there is an active transaction.Create a savepoint to be able to roll back only the work done in the procedure if there is an error.

SAVE TRANSACTION ProcedureSave;

select @.@.trancount, 'Save transaction SP 1st level'

END

ELSE

BEGIN

-- Procedure must start its own transaction.

BEGIN TRANSACTION;

select @.@.trancount, 'New transaction created on the s.p. 1st level from broker'

END

BEGIN TRY

set @.i = 1/0; -- THIS IS THE INSTRUCTION THAT CAUSE AN ERROR IN THE FIRST LEVEL S.P.

--this is an another nested SP

DECLARE @.TranCounterSP2 INT;

SET @.TranCounterSP2 = @.@.TRANCOUNT;

select @.TranCounterSP2,'Entering s.p. 2nd level from s.p. 1st level'

IF @.TranCounterSP2 > 0

BEGIN

-- Procedure called when there is an active transaction.Create a savepoint to be able to roll back only the work done in the procedure if there is an error.

SAVE TRANSACTION ProcedureSave2;

select @.@.trancount, 'Save transaction SP 2nd level'

END

ELSE

BEGIN

-- Procedure must start its own transaction.

BEGIN TRANSACTION;

select @.@.trancount, 'New transaction created on the s.p. 2nd level from sp 1st level'

END

BEGIN TRY

set @.i = 1/0; -- THIS IS THE INSTRUCTION THAT CAUSE AN ERROR IN THE SECOND LEVEL S.P.

IF @.TranCounterSP2 = 0

BEGIN

COMMIT TRANSACTION

END

select @.@.trancount, 'S.p. 2nd level was committed'

END TRY

BEGIN CATCH

select @.@.trancount, 'SP 2nd level transaction on going to be rolled back rolled back'

IF @.TranCounterSP2 = 0

-- Transaction started in procedure. Roll back complete transaction - included the outer one.

ROLLBACK TRANSACTION;

ELSE

IF XACT_STATE() <> -1

ROLLBACK TRANSACTION ProcedureSave2;select @.@.trancount, 'SP 2nd level transaction was rolled back'

END CATCH

--this is the end of an another nested SP

-- Get here if no errors; must commit any transaction started in the procedure, but not commit a transaction started before the transaction was called.

IF @.TranCounter = 0

-- @.TranCounter = 0 means no transaction was started before the procedure was called. The procedure must commit the transaction it started.

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

select @.@.trancount, 'SP 1st level transaction on going to be rolled back rolled back'

-- An error occurred; must determine which type of rollback will roll back only the work done in the procedure.

IF @.TranCounter = 0

-- Transaction started in procedure. Roll back complete transaction (INCLUDED the outer TRANSACTION).

ROLLBACK TRANSACTION;

ELSE

-- Transaction started before procedure called, do not roll back modifications made before the procedure was called.

IF XACT_STATE() <> -1

-- If the transaction is still valid, just roll back to the savepoint set at the start of the stored procedure.

ROLLBACK TRANSACTION ProcedureSave;

select @.@.trancount, 'SP 1st level transaction was rolled back'

END CATCH

commit transaction -- for the outer transaction

select @.@.trancount, 'Outer transaction was committed'

end try

begin catch

if @.@.trancount>0

begin

rollback transaction

select @.@.trancount, 'Outer transaction was rolled back'

end

end catch

select @.@.trancount, 'Outer'

If I try this script everything works as I want, somebody can also confirm me that the transaction are also properly define and I'm not going to execute code in the wrong one?

Thank you

Marina B.

Nested Stored Procs w/Transactions

I have some nested stored procedures where one sp calls another, etc. I nee
d
this wrapped in a transaction so that if an error occurs on any one sp
(either the calling sp or the one that is called) it will fail.
I'm continuall getting this error: Transaction count after EXECUTE
indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
Previous count = 2, current count = 3." So I've been playing around with
where to put the Begin Tran, Committ,
Rollback, etc.
Here's some pseudo code:
CREATE PROCEDURE [dbo].[spFILE_PROCESS]
AS
--Perform some queries, etc, then:
Exec spInsert_Customer
Exec spInsert_Trans
----
Where do I place Begin Tran/ committ, etc? I want both spInsert_Customer
and spInsert_Trans to be their own transaction as I call these sps by
themselves
elsewhere in my application.Hi,
have you tried this:
create procedure dbo.spFile_Process
as
set implicit_transactions off
--do something here without transaction
begin tran OuterTran
exec spInsert_Customer
exec spInsert_Trans
commit OuterTran
both nested procedures should have the same construction:
create procedure spInsert_Customer
as
set implicit_transactions off
begin tran TranA
...
commit TranA
create procedure spInsert_Trans
as
set implicit_transactions off
begin tran TranB
...
commit TranB
in case of error you should rollback the transation you are within
(decreasing @.@.trancount) and possible outer transactions. you have to
specify name of the transaction when rolling back, otherwise you will roll
back all transactions you're in.
HTH
Peter|||Alternately you could check for the existance of a transaction at the
begining of each procedure and only open a new transaction within the
procedure if one does not exist:
IF @.@.Trancount != 0
set @.Dotran = 0
...
If @.Dotran = 1
begin tran
...
-- on error
if @.dotran = 1
rollback transaction
return @.error
-- on success
if @.dotran = 1
comit tran
return 0
"Rogas69" wrote:

> Hi,
> have you tried this:
> create procedure dbo.spFile_Process
> as
> set implicit_transactions off
> --do something here without transaction
> begin tran OuterTran
> exec spInsert_Customer
> exec spInsert_Trans
> commit OuterTran
> both nested procedures should have the same construction:
> create procedure spInsert_Customer
> as
> set implicit_transactions off
> begin tran TranA
> ...
> commit TranA
> create procedure spInsert_Trans
> as
> set implicit_transactions off
> begin tran TranB
> ...
> commit TranB
> in case of error you should rollback the transation you are within
> (decreasing @.@.trancount) and possible outer transactions. you have to
> specify name of the transaction when rolling back, otherwise you will roll
> back all transactions you're in.
> HTH
> Peter
>
>|||Aren't you forgetting :
if @.@.Trancount = 0
set@.DoTran = 1
Anyway...
I've gone ahead and added the following whenever the committ tran, begin
tran, or rollback tran appear in my stored procs:
IF @.@.TRANCOUNT > 0 AND @.@.ERROR <> 0 BEGIN
ROLLBACK TRAN
RETURN
END
IF @.@.TRANCOUNT > 0 AND @.@.ERROR = 0 BEGIN
COMMIT TRAN
END
Now I'm longer getting the error message, but the Transaction is not being
rolled back either.
"Tony Sellars" wrote:
> Alternately you could check for the existance of a transaction at the
> begining of each procedure and only open a new transaction within the
> procedure if one does not exist:
> IF @.@.Trancount != 0
> set @.Dotran = 0
> ...
> If @.Dotran = 1
> begin tran
> ...
> -- on error
> if @.dotran = 1
> rollback transaction
> return @.error
> -- on success
> if @.dotran = 1
> comit tran
> return 0
>
> "Rogas69" wrote:
>

Nested Distributed Transactions?

Hi All,

Is it possible to have a Distributed Transaction running within another distributed transaction and so on.

I have 3 servers(Servers A, B, and C). Server A has a linked server to Server B and Server B has a linked server to Server C. I have two triggers. One that resides on Server A and Updates Server B and Another on Server B that updates Server C. Trigger on Server A invokes a distributed transaction with Server B to Update it which in turn fires the trigger on Server B which invokes another distributed transaction with Server C.

Now is this possible?? If not how can I get Around this?? I can not just a distributed transaction on the trigger which resides on server A as the one on Server B can fire on its own and not always because of Server A.

Is it possible to have nested distributed transactions like Begin/Ends??

Thanks
AnthonyShould be OK.

Every trigger runs within a transaction and this should be automatically upgraded to a distributed transaction for cross server operations so you shouldn't have to do any coding aprt from the set xact_abort on.

Nested distributed transaction

Hello everybody-

I am trying to understand how to make distributed transactions in MS SQL 2005.

For example, I got two databases A and B and a client, connected to one of them - A. From that client I want to initiate transaction to B, using connection to A.

This functionality is available in Oracle using database links. With all new changes, does it exist in MS SQL 2005?

Thanks,

Alex.

For stating distributed transactions you could (for example):

1. Use statement BEGIN DISTRIBUTED TRANSACTION T-SQL statement

2. If you use .NET you could use TransactionScope class

|||

This is good. Then, in the BEGIN DISTRIBUTED TRAN... I need to explicitely say the Remote Server name to identify the target table. Do I register it with DTS or just as on the client alias?

Sorry for that questions - I am the Oracle specialist and trying to apply the same framework...

I am also interested if the distributed transactions work properly in the replicated environment (peer-to-peer). I mean, if I want to update a record on a remote site, but don't want this transaction to be replicated back to me - is that possible?

Thanks,

Alex.

|||

For call OtherServer.OtherDB.OtherSchema.OtherTable you need to add linked server for you SQL Server.

You could to do it at SQL Server Management Studio or by stored procedure sp_addlinkedserver

|||

Okay. But what about this part, this is quite important for my application:

I am also interested if the distributed transactions work properly in the replicated environment (peer-to-peer). I mean, if I want to update a record on a remote site, but don't want this transaction to be replicated back to me - is that possible?

Thanks again,

Alex.

|||It depended from type of your replication. If you use transactional replication, you could try to configure replication filters for ignoring, but it isn't simple task. In common case, for replication distributed and local changes don't have any difference.|||

I suppose I am planning to use peer-to-peer replication - where an object/record can be updated at any site. There I have a situation, where I want to update local record, which will be replicated to all other sites and - in the same transaction - the same record at a specific remote site - and this one I do not want to be replicated.

As I said, I can easily do it in Oracle for Multi-Master replication, but don't see it in the MSSQL2005.

Alex.

Nested distributed transaction

Hello everybody-

I am trying to understand how to make distributed transactions in MS SQL 2005.

For example, I got two databases A and B and a client, connected to one of them - A. From that client I want to initiate transaction to B, using connection to A.

This functionality is available in Oracle using database links. With all new changes, does it exist in MS SQL 2005?

Thanks,

Alex.

For stating distributed transactions you could (for example):

1. Use statement BEGIN DISTRIBUTED TRANSACTION T-SQL statement

2. If you use .NET you could use TransactionScope class

|||

This is good. Then, in the BEGIN DISTRIBUTED TRAN... I need to explicitely say the Remote Server name to identify the target table. Do I register it with DTS or just as on the client alias?

Sorry for that questions - I am the Oracle specialist and trying to apply the same framework...

I am also interested if the distributed transactions work properly in the replicated environment (peer-to-peer). I mean, if I want to update a record on a remote site, but don't want this transaction to be replicated back to me - is that possible?

Thanks,

Alex.

|||

For call OtherServer.OtherDB.OtherSchema.OtherTable you need to add linked server for you SQL Server.

You could to do it at SQL Server Management Studio or by stored procedure sp_addlinkedserver

|||

Okay. But what about this part, this is quite important for my application:

I am also interested if the distributed transactions work properly in the replicated environment (peer-to-peer). I mean, if I want to update a record on a remote site, but don't want this transaction to be replicated back to me - is that possible?

Thanks again,

Alex.

|||It depended from type of your replication. If you use transactional replication, you could try to configure replication filters for ignoring, but it isn't simple task. In common case, for replication distributed and local changes don't have any difference.|||

I suppose I am planning to use peer-to-peer replication - where an object/record can be updated at any site. There I have a situation, where I want to update local record, which will be replicated to all other sites and - in the same transaction - the same record at a specific remote site - and this one I do not want to be replicated.

As I said, I can easily do it in Oracle for Multi-Master replication, but don't see it in the MSSQL2005.

Alex.

Nested Database Transactions in Forms

This should be a fairly simple question. It's based on this error message:

"Transaction count after EXECUTEindicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.Previous count = 1, current count = 0."

I get this when executing a stored procedure upon processing a form. This error happens when I intentionally provide input to the stored procedure that I know should cause it to error out. I catch the exception, and it contains the error message, but it also contains the above message added on to it, which I don't want.

I won't post the entire stored procedure. But I'll list a digest of it (Just those lines that are significant). Assume that what's included is what happens when I provide bad input:

BEGIN

BEGIN TRY
BEGIN TRANSACTION
RAISERROR('The item selected does not exist in the database.', 16, 1);
COMMIT -- This won't execute when the RAISERROR breaks out to the CATCH block
END TRY

BEGIN CATCH
ROLLBACK
DECLARE @.ErrorSeverity INT, @.ErrorMessage NVARCHAR(4000)
SET @.ErrorSeverity = ERROR_SEVERITY()
SET @.ErrorMessage = ERROR_MESSAGE()
RAISERROR(@.ErrorMessage, @.ErrorSeverity, 1)
END CATCH

END

Okay, so that works fine. The problem is when I execute this with an SqlCommand object, on which I've opened a transaction. I won't include the entire setup of the data (with the parameters, since those seem fine), but I'll give my code that opens the connection and executes the query:

con.Open();
SqlTransaction transaction = con.BeginTransaction();
command.Transaction = transaction;

try
{
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
}
finally
{
con.Close();
}

I'm calling the stored procedure listed above (which has its own transaction), using a SqlCommand object on which I've opened a transaction. When there is no error it works fine. But when I give the stored procedure bad data, it gives me that message about the transaction count.

Is there something I need to do in either my SQL or my C# to handle this? The entire message found in the Exception's Message is a concatenation of the message in my RAISERROR, along with the transaction count message I quoted at the beginning.

Thanks,

-Dan

In playing around with it, I've come to learn that the Exception.Message is the concatenation of any messages the exception holds. So, my CATCH block could contain this instead:

Label.Text = String.Format("Error: {0}", ex.Errors[0].Message);

That only gives the first error, which is the one I want (the one specified in RAISERROR). I just don't know if this is what I should do. Is that transaction count message something I should be concerned about, or will that always happen? Should I just use the first message in the exception?

Thanks.

Nest transactions in SQLServer

hi guys:
i am doing a hard work of migrating programs from Oracle to SQL-Server.
i encounte a problem about transaction which hardly hurt my heart (forgive
my poor english first ... :-) )
you know,there is such a usage in oracle:
SAVEPOINT xxx
doing things1...
doing things2...
if error in doing things2 then
ROLLBACKTOSAVEPOINT xx
end if;
so, if things 2 failed, the data altered by things1 is guaranteed to be
rollback.
but when i change it to fit sql-server, i was happily wrote such codes:
BEGIN TRANS... //must
...
...
BEGIN TRANS... //SAVEPOINT xxx
doing things1...
doing things2...
if error in doing things2 then
ROLLBACK TRANS... //ROLLBACKTOSAVEPOINT xx
end if;
..
...
COMMIT TRANS.
yes, i got a error: 'Cannot start more transactions on this session' (oh,
these codes are under ado in delphi)
i find the answer in msdn
CAUSE
By design, OLE DB Provider for SQL Server does not allow nested
transactions.
http://support.microsoft.com/defaul...kb;en-us;316872
but,i think, such nest transcations (or same other trick equal to oracle
save point) is very needed in general business process.
how to solve this problem?
my best wishes.Hi
Have you looked up SAVE TRANSACTION in BOL?
John
"MaHahaXixi" wrote:

> hi guys:
> i am doing a hard work of migrating programs from Oracle to SQL-Server.
> i encounte a problem about transaction which hardly hurt my heart (forgi
ve
> my poor english first ... :-) )
> you know,there is such a usage in oracle:
> SAVEPOINT xxx
> doing things1...
> doing things2...
> if error in doing things2 then
> ROLLBACKTOSAVEPOINT xx
> end if;
> so, if things 2 failed, the data altered by things1 is guaranteed to be
> rollback.
> but when i change it to fit sql-server, i was happily wrote such codes:
> BEGIN TRANS... //must
> ...
> ...
> BEGIN TRANS... //SAVEPOINT xxx
> doing things1...
> doing things2...
> if error in doing things2 then
> ROLLBACK TRANS... //ROLLBACKTOSAVEPOINT xx
> end if;
> ...
> ...
>
> COMMIT TRANS.
> yes, i got a error: 'Cannot start more transactions on this session' (oh,
> these codes are under ado in delphi)
> i find the answer in msdn
> CAUSE
> By design, OLE DB Provider for SQL Server does not allow nested
> transactions.
> http://support.microsoft.com/defaul...kb;en-us;316872
>
> but,i think, such nest transcations (or same other trick equal to oracle
> save point) is very needed in general business process.
> how to solve this problem?
> my best wishes.
>
>|||hi john:
thank u for ur response.
what do u mean by BOL' sorroy, i am just a greenhand.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:E702B8AB-3AD4-4AE9-BA9A-95D6AE336046@.microsoft.com...
> Hi
> Have you looked up SAVE TRANSACTION in BOL?
> John
> "MaHahaXixi" wrote:
>
SQL-Server.
(forgive
be
codes:
(oh,|||oh... yes i got the answer
there are
save trans {savepoint}
rollack trans {savepoint}
in t-sql too.
thanks all the same
"MaHahaXixi" <enjoy_linux@.hotmail.com> wrote in message
news:d3fh2p$vuo$1@.mail.cn99.com...
> hi guys:
> i am doing a hard work of migrating programs from Oracle to SQL-Server.
> i encounte a problem about transaction which hardly hurt my heart
(forgive
> my poor english first ... :-) )
> you know,there is such a usage in oracle:
> SAVEPOINT xxx
> doing things1...
> doing things2...
> if error in doing things2 then
> ROLLBACKTOSAVEPOINT xx
> end if;
> so, if things 2 failed, the data altered by things1 is guaranteed to be
> rollback.
> but when i change it to fit sql-server, i was happily wrote such codes:
> BEGIN TRANS... //must
> ...
> ...
> BEGIN TRANS... //SAVEPOINT xxx
> doing things1...
> doing things2...
> if error in doing things2 then
> ROLLBACK TRANS... //ROLLBACKTOSAVEPOINT xx
> end if;
> ...
> ...
>
> COMMIT TRANS.
> yes, i got a error: 'Cannot start more transactions on this session' (oh,
> these codes are under ado in delphi)
> i find the answer in msdn
> CAUSE
> By design, OLE DB Provider for SQL Server does not allow nested
> transactions.
> http://support.microsoft.com/defaul...kb;en-us;316872
>
> but,i think, such nest transcations (or same other trick equal to oracle
> save point) is very needed in general business process.
> how to solve this problem?
> my best wishes.
>
>|||thank you john, i got it .yes, save transactions
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:E702B8AB-3AD4-4AE9-BA9A-95D6AE336046@.microsoft.com...
> Hi
> Have you looked up SAVE TRANSACTION in BOL?
> John
> "MaHahaXixi" wrote:
>
SQL-Server.
(forgive
be
codes:
(oh,|||BOL = SQL Server Books Online, found in the SQL Server program group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MaHahaXixi" <enjoy_linux@.hotmail.com> wrote in message news:d3ftsf$137e$1@.mail.cn99.com...

> hi john:
> thank u for ur response.
> what do u mean by BOL' sorroy, i am just a greenhand.
> "John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
> news:E702B8AB-3AD4-4AE9-BA9A-95D6AE336046@.microsoft.com...
> SQL-Server.
> (forgive
> be
> codes:
> (oh,
>|||Hi
You may find these articles useful:
http://www.microsoft.com/resources/...r />
0761.mspx
http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
John
MaHahaXixi wrote:
> oh... yes i got the answer
> there are
> save trans {savepoint}
> rollack trans {savepoint}
> in t-sql too.
> thanks all the same
> "MaHahaXixi" <enjoy_linux@.hotmail.com> wrote in message
> news:d3fh2p$vuo$1@.mail.cn99.com...
SQL-Server.
> (forgive
to be
codes:
session' (oh,
oracle|||thanks a lot :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23uTmv2zPFHA.3928@.TK2MSFTNGP09.phx.gbl...
> BOL = SQL Server Books Online, found in the SQL Server program group.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MaHahaXixi" <enjoy_linux@.hotmail.com> wrote in message
news:d3ftsf$137e$1@.mail.cn99.com...
to
oracle
>|||thank you! very useful to me!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1113307105.002786.136380@.f14g2000cwb.googlegroups.com...
> Hi
> You may find these articles useful:
>
http://www.microsoft.com/resources/...art2/c0761.mspx

> http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
> John
> MaHahaXixi wrote:
> SQL-Server.
> to be
> codes:
> session' (oh,
> oracle
>