How to ROLLBACK TRANSACTION on client level

Suppose i connect to db (SQL Server) from the client (.NET) and call to
some store procedure, in this sp i start transaction (BEGIN TRANSACTION)
and before COMMIT or RALLBACK an error happen that imidietly stop the
execution of stored procedure, In the client i cacth this error but what
about an open transaction ?
HOW to rollback in the client?
Message posted via http://www.webservertalk.comYou can wrap your sp inside another one, get @.@.trancount before calling
second sp and compare after the call.
create procedure dbo.proc1
@.p1 int,
@.p2 datetime
as
set nocount on
declare @.error int
begin transaction
insert into t1 values(@.p1, @.p2)
set @.error = @.@.error
if @.error != 0
begin
rollback transaction
raiserror('whatever 1.', 16, 1)
return 1
end
insert into t2 values(@.p1)
set @.error = @.@.error
if @.error != 0
begin
rollback transaction
raiserror('whatever 2.', 16, 1)
return 1
end
else
commit transaction
return @.@.error
go
create procedure dbo.proc2
@.p1 int,
@.p2 datetime
as
set nocount on
declare @.tc int
declare @.rv int
declare @.error int
set @.tc = @.@.transcount
exec @.rv = dbo.proc1 @.p1, @.p2
set @.error = coalesce(nullif(@.rv, 0), @.@.error)
if @.tc != @.@.trancount
rollback transaction
return @.error
go
Call proc2 from your client app, instead calling proc1.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"E B via webservertalk.com" wrote:

> Suppose i connect to db (SQL Server) from the client (.NET) and call to
> some store procedure, in this sp i start transaction (BEGIN TRANSACTION)
> and before COMMIT or RALLBACK an error happen that imidietly stop the
> execution of stored procedure, In the client i cacth this error but what
> about an open transaction ?
> HOW to rollback in the client?
> --
> Message posted via http://www.webservertalk.com
>|||thanks. However i find somthing more intresting, in my app i'm using
ADO.NET so when i close a connection (conection to db) with ADO.NET method
close() it rolls back any pending transactions.
Message posted via http://www.webservertalk.com

How to rollback properly

Hi,
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in your
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server â' a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>

How to rollback properly

Hi,
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in you
r
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:

> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as thin
gs
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:

> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as thin
gs
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>

How to rollback properly

Hi,
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?
Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in your
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:

> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>
|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:

> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>

How to return specific Row in sql server

Hi,
Suppose I have 100 rows in my table, I want to retrieve say row no 5(I just want any thing on row no 5), I don't want to use identity column. Is there any other way by which I can retrieve specified row.
Thanks,
Sajidin relational databases, rows do not have a position -- there is no "5th row in the table" because the rows are not stored in sequence

the only way to get a specific row is to select it based on the value of one or more of its columns

if you sort the rows of the table into a specific sequence based on the values of one or more columns, then yes, you can get the 5th row

so, tell me what columns you want to sort the table on, and i'll show you how to get the 5th row in that sequence

rudy
http://r937.com/|||Originally posted by r937
in relational databases, rows do not have a position -- there is no "5th row in the table" because the rows are not stored in sequence

the only way to get a specific row is to select it based on the value of one or more of its columns

if you sort the rows of the table into a specific sequence based on the values of one or more columns, then yes, you can get the 5th row

so, tell me what columns you want to sort the table on, and i'll show you how to get the 5th row in that sequence

rudy
http://r937.com/

Thanks for answering my question.

Say suppose I have Name column and i want the 5th record on name column.

Thanks
Sajid|||Hi

U are asking how to select a row without having to specify anything unique about that row other than it's Ordinal position within the table.

Are U sure thats what U want

Are U Saying you want to select a row based on it's position within a table?

Ooops r937 U posted whilst I was Posting - I'll Post anyway

GW|||Originally posted by GWilliy
Hi

U are asking how to select a row without having to specify anything unique about that row other than it's Ordinal position within the table.

Are U sure thats what U want

Are U Saying you want to select a row based on it's position within a table?

Ooops r937 U posted whilst I was Posting - I'll Post anyway

GW

Yes I need the same.|||select top 1
from (
select top 5
Name
from yourtable
order
by Name
)
order
by Name desc

rudy|||Originally posted by r937
select top 1
from (
select top 5
Name
from yourtable
order
by Name
)
order
by Name desc

rudy

Thanks, It works.

If suppose i want to retrieve all columns, then do I need to specific each column.|||U could actualy do a sraight select into a cursor & then go straight to the Row

DECLARE @.a VarChar(20),@.b,@.c

DECLARE myCursor CURSOR SCROLL
FOR
SELECT a,b,c FROM myTable
OPEN myCursor
FETCH ABSOLUTE 5 INTO @.a,@.b,@.c
BEGIN
PRINT @.a
PRINT @.b
PRINT @.c
END
CLOSE myCursor
DEALLOCATE myCursor

example Code to give U an Idea - There are many options available when declaring Cursors etc.

May be more suitable for whatever Sajidrep is Doing

GW

How to retrieve Data from SQL Server?

Let us suppose that there are 900 rows in One table, and that table contains neither primary key, nor Identity column.

How can I retrieve all the roows from 201 to 250?(like the middle limits)

In oracle there is a property called RowID, but is there any such item in SQL server?

Nope. All tables should have a primary key. If there isn't one, make one. If you can't make one, then you have a data design issue you need to fix.