how to force a commit in a sp

I've a complex stored procedure, that makes a lot of insert, update,
delete and so on.

I would like to make some commits durint this sp, but of course they
are not "real" commit because who call the sp could decide for a
rollback.

But I know that this commit has to be real. In fact, the transaction
log grows really too much during the execution.

Is there a way to force a commit durint a sp ?

thank you very much!Alberto (iltrex@.libero.it) writes:
> I've a complex stored procedure, that makes a lot of insert, update,
> delete and so on.
> I would like to make some commits durint this sp, but of course they
> are not "real" commit because who call the sp could decide for a
> rollback.
> But I know that this commit has to be real. In fact, the transaction
> log grows really too much during the execution.
> Is there a way to force a commit durint a sp ?

WHILE @.@.trancount > 1
COMMIT TRANSACTION

But it would be a really bad thing to do. If the caller has started a
trasaction, he would get an error when you exit the procedure. (Unless
you are so deceivious that perform equally many BEGIN TRANSACTION.

A much better approach is to add to the beginning of the procedure:

IF @.@.trancount > 0
BEGIN
RAISERROR ('This procedure must not be called within a transaction',
16, 1)
RETURN 1
END

That assumes of course that there are no business requirements that
calls for your procedure being part of a transaction. If there is,
you will have to find other ways to address the transaction log growth.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> WHILE @.@.trancount > 1
> COMMIT TRANSACTION
> But it would be a really bad thing to do. If the caller has started a

I know. But the sp calculates data for a olap cube, and it does the
calculation in an incremental way (it can be interrupted at any time
without losing data). So you solution should be the one I'm looking
for. Now I'm going to try it!

thank you!|||Alberto (iltrex@.libero.it) writes:
>> WHILE @.@.trancount > 1
>> COMMIT TRANSACTION
>>
>> But it would be a really bad thing to do. If the caller has started a
> I know. But the sp calculates data for a olap cube, and it does the
> calculation in an incremental way (it can be interrupted at any time
> without losing data). So you solution should be the one I'm looking
> for. Now I'm going to try it!

Yeah, but the caller might have done something which cannot be
committed half-way. So I really recommend the other way:

IF @.@.trancount > 0
BEGIN
RAISERROR ('This procedure must not be called within a transaction',
16, 1)
RETURN 1
END

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql