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 the database

I am NOT talking about one transaction. I need to go back
a day or more. Either 7 or 2000. Not a stand by server
either.
Is it possible? Please answer.Aziz wrote:
> I am NOT talking about one transaction. I need to go back
> a day or more. Either 7 or 2000. Not a stand by server
> either.
> Is it possible? Please answer.
You can restore a backup and then restore the transaction log to a point
in time. Is that what you are looking for? You'll need a database
backup, plus and transaction logs for this to happen.
--
David G.|||Possible, if you have a backups of the database, that were taken at the
point, to which you want to rollback to.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Aziz" <goaziz@.yahoo.com> wrote in message
news:92fc01c496b9$080bbe40$a501280a@.phx.gbl...
I am NOT talking about one transaction. I need to go back
a day or more. Either 7 or 2000. Not a stand by server
either.
Is it possible? Please answer.|||No. I can not use backupp to restore. Because it is too
big. It takes more than 6 hours to restore.
>--Original Message--
>Possible, if you have a backups of the database, that
were taken at the
>point, to which you want to rollback to.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"Aziz" <goaziz@.yahoo.com> wrote in message
>news:92fc01c496b9$080bbe40$a501280a@.phx.gbl...
>I am NOT talking about one transaction. I need to go back
>a day or more. Either 7 or 2000. Not a stand by server
>either.
>Is it possible? Please answer.
>
>.
>|||There is not functionality in SQL Server where you can tell the product to go back a certain amount
of time. Restore is your option.
However, you can look at the log reading tools available, see my links page for where to find some
of them.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aziz Karim" <anonymous@.discussions.microsoft.com> wrote in message
news:99d601c49749$8f959c10$a601280a@.phx.gbl...
> No. I can not use backupp to restore. Because it is too
> big. It takes more than 6 hours to restore.
>
>>--Original Message--
>>Possible, if you have a backups of the database, that
> were taken at the
>>point, to which you want to rollback to.
>>--
>>HTH,
>>Vyas, MVP (SQL Server)
>>http://vyaskn.tripod.com/
>>
>>"Aziz" <goaziz@.yahoo.com> wrote in message
>>news:92fc01c496b9$080bbe40$a501280a@.phx.gbl...
>>I am NOT talking about one transaction. I need to go back
>>a day or more. Either 7 or 2000. Not a stand by server
>>either.
>>Is it possible? Please answer.
>>
>>.|||There is an option built into Lumigent's Log Explorer
to "back out" transactions.
This may accomplish what you need, assuming you haven't
truncated your log.
Matthew Bando
Matthew.Bando@.remove CSCTGI.com
>--Original Message--
>There is not functionality in SQL Server where you can
tell the product to go back a certain amount
>of time. Restore is your option.
>However, you can look at the log reading tools
available, see my links page for where to find some
>of them.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Aziz Karim" <anonymous@.discussions.microsoft.com> wrote
in message
>news:99d601c49749$8f959c10$a601280a@.phx.gbl...
>> No. I can not use backupp to restore. Because it is too
>> big. It takes more than 6 hours to restore.
>>
>>--Original Message--
>>Possible, if you have a backups of the database, that
>> were taken at the
>>point, to which you want to rollback to.
>>--
>>HTH,
>>Vyas, MVP (SQL Server)
>>http://vyaskn.tripod.com/
>>
>>"Aziz" <goaziz@.yahoo.com> wrote in message
>>news:92fc01c496b9$080bbe40$a501280a@.phx.gbl...
>>I am NOT talking about one transaction. I need to go
back
>>a day or more. Either 7 or 2000. Not a stand by server
>>either.
>>Is it possible? Please answer.
>>
>>.
>
>.
>

How to rollback the database

I am NOT talking about one transaction. I need to go back
a day or more. Either 7 or 2000. Not a stand by server
either.
Is it possible? Please answer.
Aziz wrote:
> I am NOT talking about one transaction. I need to go back
> a day or more. Either 7 or 2000. Not a stand by server
> either.
> Is it possible? Please answer.
You can restore a backup and then restore the transaction log to a point
in time. Is that what you are looking for? You'll need a database
backup, plus and transaction logs for this to happen.
David G.
|||Possible, if you have a backups of the database, that were taken at the
point, to which you want to rollback to.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Aziz" <goaziz@.yahoo.com> wrote in message
news:92fc01c496b9$080bbe40$a501280a@.phx.gbl...
I am NOT talking about one transaction. I need to go back
a day or more. Either 7 or 2000. Not a stand by server
either.
Is it possible? Please answer.
|||No. I can not use backupp to restore. Because it is too
big. It takes more than 6 hours to restore.

>--Original Message--
>Possible, if you have a backups of the database, that
were taken at the
>point, to which you want to rollback to.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"Aziz" <goaziz@.yahoo.com> wrote in message
>news:92fc01c496b9$080bbe40$a501280a@.phx.gbl...
>I am NOT talking about one transaction. I need to go back
>a day or more. Either 7 or 2000. Not a stand by server
>either.
>Is it possible? Please answer.
>
>.
>
|||There is not functionality in SQL Server where you can tell the product to go back a certain amount
of time. Restore is your option.
However, you can look at the log reading tools available, see my links page for where to find some
of them.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aziz Karim" <anonymous@.discussions.microsoft.com> wrote in message
news:99d601c49749$8f959c10$a601280a@.phx.gbl...[vbcol=seagreen]
> No. I can not use backupp to restore. Because it is too
> big. It takes more than 6 hours to restore.
>
> were taken at the
|||There is an option built into Lumigent's Log Explorer
to "back out" transactions.
This may accomplish what you need, assuming you haven't
truncated your log.
Matthew Bando
Matthew.Bando@.remove CSCTGI.com

>--Original Message--
>There is not functionality in SQL Server where you can
tell the product to go back a certain amount
>of time. Restore is your option.
>However, you can look at the log reading tools
available, see my links page for where to find some
>of them.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Aziz Karim" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:99d601c49749$8f959c10$a601280a@.phx.gbl...
back
>
>.
>

How to Rollback Committed Update Transaction

Is there any way to Rollback Committed Update statement in SQL Server

You could get one of the third party transaction log tools, such as Lumnigent's Log Explorer, and undo the transaction.

SQL Server itself does not have that capability. Once committed, done.

How to rollback a transaction in Data Access Layer

Hi,

I am having a application in which from the front end i am saving details of three different things

i.Enquiry Details

ii.Parts Details

iii.Machine details

i am saving the Enquiry detail in a data table,Parts Details in a data table and machine detail in a data table and finally i am adding the three data tables into a single data set and passing the data set to data access layer there i have three insert command one for each data table in my case the enquiry data table will be saved first and then the next two details will be saved and i am saving the details in three different tables in the database, my problem is some times the enquiry details will save to the database and while saving the Parts details there may be some exception and i will throw an exception in that case the enquiry details will be saved and the remaining two details are not saved(Which are also part of the same Transaction).I wanted to know about how to call the transaction function in case of Data Access Layer.

Do you want to store the data independtly. Even the tail 2 tables failed you want to successfully commit the first insert?

Pls explain the last statement?

|||

You can use a user transaction which can be opend through Sqlconnection.begintransaction()

See the MSDN for more details:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.begintransaction.aspx

The transaction is tied to the connection but can be commited separately.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for Reply,

The answers for your questions are below

1.Do you want to store the data independtly?

yes i am storing the data in three different tables.

2.Even the tail 2 tables failed you want to successfully commit the first insert?

No i want to roll back the entire transaction.

How to Restrict all SQL Databases Size

Hello -

I have over 100 MSSQL Databases on my SQL SERVER. How do I restrict
all the MSSQL databases and its transaction logs to 100 MB.

Can someone help me with any script which will do that.

Thanks,

Rubal Jain
www.Rubal.netHi

This will depend on how/what these databases are and what you want to set
the sizes to.
A start could be the script created by:

EXEC master..sp_MSForEachdb 'USE ? SELECT ''ALTER DATABASE '' + db_name() +
'' MODIFY FILE ( name= '' + RTRIM(name) + '', MAXSIZE=200)'' FROM sysfiles
WHERE status & 0x40 <> 0x40 '

John

"Rubal Jain" <rubaljain@.yahoo.com> wrote in message
news:7a30b199.0407150445.56b2a480@.posting.google.c om...
> Hello -
> I have over 100 MSSQL Databases on my SQL SERVER. How do I restrict
> all the MSSQL databases and its transaction logs to 100 MB.
> Can someone help me with any script which will do that.
> Thanks,
> Rubal Jain
> www.Rubal.net

How to restore Transaction log

Hello everyone,
I have a problem which is driving me crazy. I can't figure it out how I'm
going to solve this problem but I appreciate any help in this regards.
I'm configuring a log shipping server and it's almost done. The only problem
I'm having is restoring logs from the primary server. the primary server is
appending logs in a log file. If I use the RESTORE HEADERONLY command to
read the file I can see lot's of backup sets like FILE 1, FILE 2, FILE
3....Now I know how to use the restore command but my problem is using TSQL
how can I restore this file in one go...I mean I don't have to restore
backup sets one by one...
Thanx.
Muneeb.You cannot make a single RESTORE command do several RESTORE operations. You
need to do several RESTORE executions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Muneeb Ahmed" <muneeb.ahmed@.mhs.com> wrote in message
news:eBKYz4UDEHA.2564@.TK2MSFTNGP11.phx.gbl...
> Hello everyone,
> I have a problem which is driving me crazy. I can't figure it out how I'm
> going to solve this problem but I appreciate any help in this regards.
> I'm configuring a log shipping server and it's almost done. The only
problem
> I'm having is restoring logs from the primary server. the primary server
is
> appending logs in a log file. If I use the RESTORE HEADERONLY command to
> read the file I can see lot's of backup sets like FILE 1, FILE 2, FILE
> 3....Now I know how to use the restore command but my problem is using
TSQL
> how can I restore this file in one go...I mean I don't have to restore
> backup sets one by one...
> Thanx.
> Muneeb.
>

How to Restore SQL2K Full bkup then tran logs?

I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
backup, then the transaction log backups to 2005. Am getting error below w/
full in standby - How to resolve? Thanks.
RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
REPLACE,
MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
Msg 3180, Level 16, State 1, Line 2
This backup cannot be restored using WITH STANDBY because a database upgrade
is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.Chris wrote:
> I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
> backup, then the transaction log backups to 2005. Am getting error below w/
> full in standby - How to resolve? Thanks.
> RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
> REPLACE,
> MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
> MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
>
> Msg 3180, Level 16, State 1, Line 2
> This backup cannot be restored using WITH STANDBY because a database upgrade
> is needed. Reissue the RESTORE without WITH STANDBY.
> Msg 3013, Level 16, State 1, Line 2
> RESTORE DATABASE is terminating abnormally.
Try WITH NORECOVERY instead of WITH STANDBY
Tracy McKibben
MCDBA
http://www.realsqlguy.com

How to Restore SQL2K Full bkup then tran logs?

I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
backup, then the transaction log backups to 2005. Am getting error below w/
full in standby - How to resolve? Thanks.
RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
REPLACE,
MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
Msg 3180, Level 16, State 1, Line 2
This backup cannot be restored using WITH STANDBY because a database upgrade
is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.Chris wrote:
> I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
> backup, then the transaction log backups to 2005. Am getting error below w
/
> full in standby - How to resolve? Thanks.
> RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt
',
> REPLACE,
> MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
> MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
>
> Msg 3180, Level 16, State 1, Line 2
> This backup cannot be restored using WITH STANDBY because a database upgra
de
> is needed. Reissue the RESTORE without WITH STANDBY.
> Msg 3013, Level 16, State 1, Line 2
> RESTORE DATABASE is terminating abnormally.
Try WITH NORECOVERY instead of WITH STANDBY
Tracy McKibben
MCDBA
http://www.realsqlguy.com