Nested Rollback/Commits Question

Hello all
I have a question regarding Rollbacks and Committs when
you are nesting stored procedures. My problem is that i
continue to get the following error.
Transaction count after EXECUTE indicates that a COMMIT
or ROLLBACK TRANSACTION statement is missing. Previous
count = 0, current count = 1
I am not sure what i am doing wrong. Any help would be
appriciated. Here is a sample SP that i may be nesting
in a larger SP.
---
CREATE PROCEDURE usp_Update_Client_Biography
@.Client_id as integer,
@.Biography as varchar(1000)
AS
SET NOCOUNT ON
DECLARE @.intErrorCode integer
SELECT @.intErrorCode = @.@.Error
IF @.intErrorCode = 0
BEGIN TRANSACTION
IF @.intErrorCode = 0
BEGIN
UPDATE Client_Biography
SET biography = @.Biography
WHERE biz_association_id = @.Client_id
SELECT @.intErrorCode = @.@.Error
END
IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
RETURN @.intErrorCode
THANKS
BAPerhaps @.@.TRANCOUNT is 1 when you enter the procedure? If you exit the proc with some other
trancount then when entering, you get such error. And remember that ROLLBACK exits the transaction
(@.@.TRANCOUNT to 0) and not only reduces the @.@.TRANCOUNT.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message news:065201c38109$f1f0e150$a001280a@.phx.gbl...
> Hello all
> I have a question regarding Rollbacks and Committs when
> you are nesting stored procedures. My problem is that i
> continue to get the following error.
> Transaction count after EXECUTE indicates that a COMMIT
> or ROLLBACK TRANSACTION statement is missing. Previous
> count = 0, current count = 1
> I am not sure what i am doing wrong. Any help would be
> appriciated. Here is a sample SP that i may be nesting
> in a larger SP.
> ---
> CREATE PROCEDURE usp_Update_Client_Biography
> @.Client_id as integer,
> @.Biography as varchar(1000)
> AS
> SET NOCOUNT ON
> DECLARE @.intErrorCode integer
> SELECT @.intErrorCode = @.@.Error
> IF @.intErrorCode = 0
> BEGIN TRANSACTION
> IF @.intErrorCode = 0
> BEGIN
> UPDATE Client_Biography
> SET biography = @.Biography
> WHERE biz_association_id = @.Client_id
> SELECT @.intErrorCode = @.@.Error
> END
>
> IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
> COMMIT TRANSACTION
> ELSE
> ROLLBACK TRANSACTION
>
> RETURN @.intErrorCode
>
> THANKS
> BA|||When you rollback a transaction it will rollback all the transactions that
it is nested in. So you won't have any open transaction by the time you get
to your errorhandler. You can catch this by changing your code (at the end)
to:
IF @.@.TRANCOUNT > 0
IF @.intErrorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
You might want to have a look at the SET XACT_ABORT option. If you have it
on any error will cause the transaction to roll back and prevent any further
code in the batch to execute. The disadvantage of this of course is that you
can't do any custom error handling, the advantage is that you don't have to
do any custom error handling.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message
news:065201c38109$f1f0e150$a001280a@.phx.gbl...
> Hello all
> I have a question regarding Rollbacks and Committs when
> you are nesting stored procedures. My problem is that i
> continue to get the following error.
> Transaction count after EXECUTE indicates that a COMMIT
> or ROLLBACK TRANSACTION statement is missing. Previous
> count = 0, current count = 1
> I am not sure what i am doing wrong. Any help would be
> appriciated. Here is a sample SP that i may be nesting
> in a larger SP.
> ---
> CREATE PROCEDURE usp_Update_Client_Biography
> @.Client_id as integer,
> @.Biography as varchar(1000)
> AS
> SET NOCOUNT ON
> DECLARE @.intErrorCode integer
> SELECT @.intErrorCode = @.@.Error
> IF @.intErrorCode = 0
> BEGIN TRANSACTION
> IF @.intErrorCode = 0
> BEGIN
> UPDATE Client_Biography
> SET biography = @.Biography
> WHERE biz_association_id = @.Client_id
> SELECT @.intErrorCode = @.@.Error
> END
>
> IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
> COMMIT TRANSACTION
> ELSE
> ROLLBACK TRANSACTION
>
> RETURN @.intErrorCode
>
> THANKS
> BA|||This is a multi-part message in MIME format.
--=_NextPart_000_009C_01C380EE.56F75F50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
declare
@.trancount int
set @.trancount =3D @.@.trancount
if @.trancount =3D 0
begin tran MyTran
else
save tran MyTran
-- do the work, check errors
if @.@.ERROR =3D 0
begin
if @.trancount =3D 0
commit tran
end
else
rollback tran MyTran
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"BA Baracus" <JCoxEUP@.hotmail.com> wrote in message =news:06ea01c3810f$02b36670$a001280a@.phx.gbl...
Thanks for the help!
Is there any way you can show me some T-SQL that will show me your suggestion? If i understand you correctly, i should check the transaction count before beginning the transaction? This has been an ongoing issue with me and i am very appriciative of your help!
thanks BA
>--Original Message--
>Perhaps @.@.TRANCOUNT is 1 when you enter the procedure? If you exit the proc with some other
>trancount then when entering, you get such error. And remember that ROLLBACK exits the transaction
>(@.@.TRANCOUNT to 0) and not only reduces the @.@.TRANCOUNT.
>-- >Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=3Ddjq&as=20
ugroup=3Dmicrosoft.public.sqlserver
>
>"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message news:065201c38109$f1f0e150$a001280a@.phx.gbl...
>> Hello all
>> I have a question regarding Rollbacks and Committs when
>> you are nesting stored procedures. My problem is that i
>> continue to get the following error.
>> Transaction count after EXECUTE indicates that a COMMIT
>> or ROLLBACK TRANSACTION statement is missing. Previous
>> count =3D 0, current count =3D 1
>> I am not sure what i am doing wrong. Any help would be
>> appriciated. Here is a sample SP that i may be nesting
>> in a larger SP.
>> ---
>> CREATE PROCEDURE usp_Update_Client_Biography
>> @.Client_id as integer,
>> @.Biography as varchar(1000)
>> AS
>> SET NOCOUNT ON
>> DECLARE @.intErrorCode integer
>> SELECT @.intErrorCode =3D @.@.Error
>> IF @.intErrorCode =3D 0
>> BEGIN TRANSACTION
>> IF @.intErrorCode =3D 0
>> BEGIN
>> UPDATE Client_Biography
>> SET biography =3D @.Biography
>> WHERE biz_association_id =3D @.Client_id
>> SELECT @.intErrorCode =3D @.@.Error
>> END
>>
>> IF @.intErrorCode =3D 0 AND @.@.TRANCOUNT > 0
>> COMMIT TRANSACTION
>> ELSE
>> ROLLBACK TRANSACTION
>>
>> RETURN @.intErrorCode
>>
>> THANKS
>> BA
>
>.
>
--=_NextPart_000_009C_01C380EE.56F75F50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try:
declare
@.trancount int
set @.trancount =3D =@.@.trancount
if @.trancount =3D =0
begin tran MyTran
else
save tran MyTran
-- do the work, check =errors
if @.@.ERROR =3D 0
begin
if =@.trancount =3D 0
= commit tran
end
else
rollback =tran MyTran
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"BA Baracus" wrote in =message news:06ea01c3810f$02=b36670$a001280a@.phx.gbl...Thanks for the help!Is there any way you can show me some T-SQL that =will show me your suggestion? If i understand you correctly, i =should check the transaction count before beginning the transaction? =This has been an ongoing issue with me and i am very appriciative of your help!thanks BA >--Original Message-->Perhaps @.@.TRANCOUNT is 1 when you enter the =procedure? If you exit the proc with some other>trancount then when =entering, you get such error. And remember that ROLLBACK exits the transaction>(@.@.TRANCOUNT to 0) and not only reduces the @.@.TRANCOUNT.>>-- >Tibor Karaszi, SQL Server MVP>Archive at: http://groups.google.com/groups?oi=3Ddjq&as">http://groups.go=ogle.com/groups?oi=3Ddjq&as ugroup=3Dmicrosoft.public.sqlserver>>>"B.A. =Baracus" =wrote in message news:065201c38109$f1=f0e150$a001280a@.phx.gbl...> Hello all>> I have a question regarding Rollbacks =and Committs when> you are nesting stored procedures. My =problem is that i> continue to get the following error.>> Transaction count after EXECUTE indicates =that a COMMIT> or ROLLBACK TRANSACTION statement is missing. Previous> count =3D 0, current count =3D =1>> I am not sure what i am doing wrong. Any help would be> appriciated. Here is a sample SP that i may be nesting> =in a larger SP.>> --->>= CREATE PROCEDURE usp_Update_Client_Biography> @.Client_id as integer,> @.Biography as varchar(1000)>> =AS>> SET NOCOUNT ON>> =DECLARE @.intErrorCode integer> SELECT @.intErrorCode =3D @.@.Error>> IF @.intErrorCode =3D 0> BEGIN =TRANSACTION>> IF @.intErrorCode =3D 0> BEGIN> UPDATE Client_Biography> SET biography =3D @.Biography> WHERE biz_association_id =3D @.Client_id>> SELECT @.intErrorCode =3D =@.@.Error> END>>> IF @.intErrorCode =3D 0 AND =@.@.TRANCOUNT > 0> COMMIT TRANSACTION> ELSE> =ROLLBACK TRANSACTION>>> RETURN @.intErrorCode>>> THANKS> BA>>>.>

---

--=_NextPart_000_009C_01C380EE.56F75F50--|||Hi.
I'm currently reading SQL Server 2000 Stored Proc 7 XML Programing,
2nd ed. By Dejan Sunderic - ISBN 0-07-222896-2
So far it has been an excellent book IMHO though I'm only on chapter 9
I have just read the chapter dealing with errorcodes & transactions,
using the information in the book I've got a template that I've
started to use for all my stored procs which might be of some use to
you.
What I would like is if any Guru out there can comment on the template
and let me know before I go to far with it if its as good as what the
book seems to be saying it is.
Thanks & HTH.
Al
/*
**
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
** Function : usp__Template
** Language : T-SQL
** Description : This is a template for all stored procs
** :
** Input : None
** Returns : None
**
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
** Ver Date Description of modification
**
---
** 1.0 date here text here
**
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
*/
CREATE PROCEDURE [dbo].[usp__Template]
-- @.intInValuesHere INT ,
-- @.intInValuesMoreHere INT ,
-- @.intOutputParam INT OUTPUT
AS
SET NOCOUNT ON
DECLARE @.intErrorCode INT , -- to store the errorcodes
@.intTranCountOnEntry INT , -- Store transaction count
@.intAnotherVariable INT
SELECT @.intErrorCode = @.@.Error ,
@.intTranCountOnEntry = @.@.TranCount ,
@.intAnotherVariable = 0
if @.@.TranCount = 0
BEGIN TRANSACTION
-- T-SQL code here
SELECT @.intErrorCode = @.@.Error
IF @.intErrorCode > 0 GOTO ERROR_HANDLER
-- more T-SQL code here
SELECT @.intErrorCode = @.@.Error
IF @.intErrorCode > 0 GOTO ERROR_HANDLER
IF @.@.TranCount > @.intTranCountOnEntry
COMMIT TRANSACTION
RETURN 0
/****************************************
******* ERROR HANDLER CODE BELOW ********
*****************************************/
ERROR_HANDLER:
IF @.@.TranCount > @.intTranCountOnEntry
ROLLBACK TRANSACTION
RAISERROR ('Some Error Message here...',16,1)
RETURN @.intErrorCode
GO
On Mon, 22 Sep 2003 06:03:41 -0700, "B.A. Baracus"
<JCoxEUP@.hotmail.com> wrote:
>Hello all
>I have a question regarding Rollbacks and Committs when
>you are nesting stored procedures. My problem is that i
>continue to get the following error.
>Transaction count after EXECUTE indicates that a COMMIT
>or ROLLBACK TRANSACTION statement is missing. Previous
>count = 0, current count = 1
>I am not sure what i am doing wrong. Any help would be
>appriciated. Here is a sample SP that i may be nesting
>in a larger SP.
>---
>CREATE PROCEDURE usp_Update_Client_Biography
> @.Client_id as integer,
> @.Biography as varchar(1000)
>AS
>SET NOCOUNT ON
>DECLARE @.intErrorCode integer
>SELECT @.intErrorCode = @.@.Error
>IF @.intErrorCode = 0
> BEGIN TRANSACTION
>IF @.intErrorCode = 0
> BEGIN
> UPDATE Client_Biography
> SET biography = @.Biography
> WHERE biz_association_id = @.Client_id
> SELECT @.intErrorCode = @.@.Error
> END
>
>IF @.intErrorCode = 0 AND @.@.TRANCOUNT > 0
> COMMIT TRANSACTION
>ELSE
> ROLLBACK TRANSACTION
>
>RETURN @.intErrorCode
>
>THANKS
>BA

Need urgent help with pemission problem

Hi all ,

I posted an early post regarding that with no reply , its seem strange that
nobody know that issue coase it looks majur issue .

The problem is when creating a db and the a user for that db only from enterprise manager , when that user log into with enterprise manager also
he can see the content of the master db (also the other default sql2000 system dbs) .

Any way to avoide that issue ?

Any help will be great .Short answer: No.

Longer answer: You can hide the system databases in enterprise manager, but all users will need at least guest access to the master database. You can safely remove the guest user from the msdb database, however. Removing guest from master will result in enterprise manager breaking for anyone who is not in the sysadmin group, I believe.|||Hi ,

Thanks for the answare .

Let me get it , there is no way to protect the master db from regular users
what is point in that ? everyone can see everything , even guest .

This is realy odd thing . im sure microsoft did it for a resone but it doesnt make sence anyhow .

Thanks ,|||McCrowley is right. The Sr. DBA did this at my last company did this and I was trying to work though it in my head. I know you can not remove the guest account in master. Maybe sp_denylogin on their NT accounts. Are you using Windows Authentication?|||Why do your regular users have the Enterprise Manager installed anyways? Let me guess, they are writing ad hoc queries in the QA too. Barf. I was just assuming you were trying to lock out your developers.|||Its for hosting perpose , so you open a user a db asign a user , if he wants
do admin his db its more then fine but why he must see the master db contents , its system content .

Dont get this oproche .

P-s : its mix mode , we decide to open a sql user only(not os)
didint notice the deny login option but deny read will prevent the login all together .

Thanks ,|||sp_denylogin is a system stored procedure.|||Its not NT authentication but sql only , is the stored procidure will be possible also , if its only for system users then its a good couse to change the logins now befor going into production , how can i test the stored procidure you mentioned , how does it work , i come from mysql generaly .

Thanks ,|||I think this one is for NT auth only.|||You may want to start here, for now.

http://www.microsoft.com/sql/techinfo/administration/2000/security/securingsqlserver.asp

I would not worry too much over people being able to see the master database, so long as they can not write to it (i.e. have sysadmin rights)|||Almost everything that mentioned in the arcticle is implimented but the mix mode .

Ill try to make some test without it(NT only athentication)

Can you post a demo sytax for the stored procidure you mentioned

will be great .

Many thanks for all your help .|||Notices another odd thing , by changing the mix to NT only it wont allow
logins from operating systems that not login the sql domain .

Very strange all those issues , may be fixed in sql2005 , LOL|||Another wird thing , by changing the sql service from the default localsystem to just a simple user it wont start the service , i did it exactly like micrososft suggested (from enterprise manager) too much crap with this system .|||Almost everything that mentioned in the arcticle is implimented but the mix mode .

Ill try to make some test without it(NT only athentication)

Can you post a demo sytax for the stored procidure you mentioned

will be great .

Many thanks for all your help .

Try SQL books online in the SQL Server Programs folder. Busy today. sorry.|||Notices another odd thing , by changing the mix to NT only it wont allow
logins from operating systems that not login the sql domain .

Very strange all those issues , may be fixed in sql2005 , LOL

this by design and kind of implied by the term NT authentication. it is intended to be the most secure configuration.|||Another wird thing , by changing the sql service from the default localsystem to just a simple user it wont start the service , i did it exactly like micrososft suggested (from enterprise manager) too much crap with this system .

this is a permissions issue. i believe the user must be an admin account on the machine and needs sysadmin rights in SQL Server.

it is not too much crap. it is just a lot too learn. i am no Microsoft apoloigist, but once you get going with it, most of it makes since in the bigger scheme. I have been developing with it for 4 years and admining for about year and I have been studying for the MCDBA most nights for 3 months and I still only know what I am doing a little more than 1/2 the time.|||I have 2 sql2000 books wich i read , no mention to this issue whats os ever .

Also other issues for example microsoft ask not to asign the user admin right
the faq mention a regular user(not admin) and that enetrprise manager will take care of the rest , this is not the case couse the faq is obviusly wronge
that what i ment with too much crap , when system becomes too much complicated then the over all things will be as well complicated , the dbs are not faster . stable or secure then other more simple systems like mysql
people allways say that there is no compitment at all , all i see is complicated , cost lots of moeny , with not much benefits , not faster for sure (see some benchmarks) , we installed it couse few clients realy ensisted but all those issues realy frastrating , from the developer kind of view its a heven , the dba also have lots of options , but too much crap arroung it ,
not stable i mean , microsoft say this , the result that etc...
-|||it is a poor craftsman that blames his tools. forget your books. read books online.|||Hi ,

Not blaming anything , just saying its realy fratrating , Maxdb and sysbase are
not easy as well (not at all) maxdb is very hard to work with but thigs are much clearer , no intgration with os (otherwise its not secure) the core of the product need to be secure regardless of the os integration , also this issue , iv seen other dude that posted quite the same issue (with users can see the system dbs) , i managed to delete the guest account from most of the system dbs but what abuat the master db(the most important if im not mistaken) .

Thanks ,

Need Unicode question answered ASAP

I have a question regarding data types on Windows 2000 SVC Pk 4 & SQL Server
2000 Svc Pack 3a:
If I convert a char(20) Column to NChar, should I be using
Nchar (20) or Nchar(40)?
Thanks much.NCHAR(20). For Unicode data types nchar & nvarchar, the length refers to
the number of (2-byte) Unicode characters you can store, not the number
of bytes.
HTH,
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
Thread-Topic: Need Unicode question answered ASAP
thread-index: AcP66G4a/faD4EnrQkeJGb/105yR6Q==
X-Tomcat-NG: microsoft.public.sqlserver.server
From: "examnotes" <anonymous@.discussions.microsoft.com>
Subject: Need Unicode question answered ASAP
Date: Tue, 24 Feb 2004 07:11:08 -0800
Lines: 8
Message-ID: <D4B67A56-C3B4-4FC9-B1C0-AE6E88A01ADD@.microsoft.com>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.sqlserver.server
Path: cpmsftngxa06.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:330913
NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
X-Tomcat-NG: microsoft.public.sqlserver.server
I have a question regarding data types on Windows 2000 SVC Pk 4 & SQL
Server 2000 Svc Pack 3a:
If I convert a char(20) Column to NChar, should I be using
Nchar (20) or Nchar(40)?
Thanks much.