Want To Save Part Of Transactions When Rollback

Jul 26, 2007

Hi All,
I have set of stored procedures under one transaction where as each SP saves the data in one table and any error occurs then it saves the error details in errorlog table in the same SP. Whenever error occurs, all my transactions including error logging also getting rolled back. I want to keep the error logging part alone when my transaction is getting rolled back.....
That is,
try
{
Transaction A
{
Storedprocedure A
StoredProcedure B
StoredProceudreC
commit Transaction A

}
}
catch()
{
rollback A
}


Where as,
My each SP might contains the statements like,
StoredProcedure A
{

insert values in table A
if ( @@Rowcount =0)
insert values in errorlog ('Error occurred in table A')
}


Now, if any error occurs in Transaction A, all my values stored in 'errorlog' also getting rolled back. Is that any way to stop rollback only for errorlog table in the entire transaction?
(Tried using triggers, that was also not helpfu
SavePoint will save unnecessary part also.. ).

Is that any way to track the error and maintain in errorlog?

ManyThanks in advance,
Jaya





View 4 Replies


ADVERTISEMENT

SQL Transactions RollBack...

Dec 17, 2004

Hello:

I am just starting to falmiliarize myself with SQL transactions... I just created an SQL transaction... The first statement gets a value, if the value equals "" then the second statement executes... So if the value <> "" Then the second statement wont execute... What would happen in this scenario if the .Rollback is triggered? Heres my code:

Try
conSqlConnect.Open()
objTransaction = conSqlConnect.BeginTransaction
cmdSelect.Transaction = objTransaction
cmdInsert.Transaction = objTransaction
dtrdatareader = cmdSelect.ExecuteReader()
While dtrdatareader.Read()
varCheckNumber1 = dtrdatareader("Status")
End While
dtrdatareader.Close()
If varCheckNumber1 = "" Then
cmdInsert.ExecuteNonQuery()
End If
objTransaction.Commit
Catch
objTransaction.RollBack
Return "00"
Finally
If conSQLConnect.State = ConnectionState.Open Then
conSqlConnect.Close()
End If
End Try


Any Ideas?

View 5 Replies View Related

How To Rollback Transactions

Apr 20, 2007

hi everybody,

i have 4 flat files from a source folder which updates four different tables, this has to be done parallely,on success of this transaction the files have to be moved to another folder.

my problem comes here,now if there is any problem in moving any file to another folder,that particular transaction has to be rolledback without affecting others.i tried setting the transaction property of the control flow,but it rollbacks all the transaction..

please help me on this

View 4 Replies View Related

Doubt In Rollback Transactions

Mar 11, 2004

may be an idiotic question

I have created a table test1 with primary key as given below. I have written a procedure to insert rows. is the rollback transaction given under is correct?. (OR) shall i give the rollback only once at the end?

need more explanation on the rollback transaction.



CREATE TABLE [TEST1] (
[COL1] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO


ALTER TABLE [TEST1] WITH NOCHECK ADD
CONSTRAINT [PK_TEST1] PRIMARY KEY CLUSTERED
(
[COL1]
) ON [PRIMARY]
GO


ALTER PROCEDURE T AS
BEGIN TRANSACTION
INSERT INTO TEST1 VALUES('A')
IF (@@ERROR <> 0) GOTO ERR
INSERT INTO TEST1 VALUES('B')
IF (@@ERROR <> 0) GOTO ERR
INSERT INTO TEST1 VALUES('B')
IF (@@ERROR <> 0) GOTO ERR
INSERT INTO TEST1 VALUES('C')
IF (@@ERROR <> 0) GOTO ERR
INSERT INTO TEST1 VALUES('D')
IF (@@ERROR <> 0) GOTO ERR
ERR:
IF (@@ERROR <> 0)
ROLLBACK TRANSACTION
else
COMMIT TRANSACTION

View 5 Replies View Related

Getting Notified On Rollback Transactions?

Jun 10, 2015

I am working on of the T-sql statement that do updates. This statement is running in the job. We set up the notification reached to operator when the job failed.
,
But I need whenever the transactions are rolled back, it has to notify to the team. Below are the steps in the job.

DECLARE @NextRunDate DATETIME = DATEADD(hh,2,CAST(CAST(DATEADD(day,1,GETUTCDATE()) as DATE) as DATETIME))
BEGIN TRY
BEGIN TRANSACTION
UPDATE [RECompanyTask]
SET NextRunDate = @NextRunDate
WHERE SetupOptions = 0 AND [Enabled] = 1

[Code] .....

View 3 Replies View Related

Rollback Of Transactions From Ldf File

Nov 28, 2007



greetings from germyn,

heres my question:

someone deleted data from our sql server in several tables. our backup is from yesterday evening and 150 people worked with the database for 7 hours today so it is not possible to restore the database from backup. is it possible to use the ldf to rollback the transactions deleted the data? Can someone give me an idea?

thx

Kaeppi

View 1 Replies View Related

When Do Transactions Commit/Rollback?

Sep 18, 2007

I have a series of questions about SSIS and transactions. The answers to these questions are probably so obvious that I can't see them, so please feel free to just point out what it is that I'm missing. My transaction-processing experience is very low-level, so I'm probably just not seeing how it's done at the high level of SSIS.

The first question is one that I may know the answer to, so please confirm:



Consider a package with TransactionOption set to Supported. It contains a single Execute SQL Task with TransactionOption set to Required. Is it true that if that Execute SQL Task succeeds, that the transaction commits, and that if the task fails, the transaction rolls back?

Consider another package with TransactionOption set to Supported. It contains a Sequence Container with TransactionOption set to Required. That container contains our same Execute SQL Task, but that is joined to a script task by a "success" precedence constraint. The script task simply returns Dts.Results.Failure. Is it the case that the transaction will roll back? That is, is it truly a simple failure result that would initiate the rollback?

If a DataFlow Task is the one that is set to Required, does that mean that every transactional operation within that task will commit in a single transaction? For instance, if I'm inserting five rows for each input record from a flat file, and if my flat file has 1000 records in it, will I see a single transaction with 5,000 rows?
Thanks for your patience!

View 5 Replies View Related

How To Use Commit,begin,rollback Transactions In Asp.net With C#

May 5, 2008

hi,
I have wriiten the code cn.Open();
SqlCommand CmdInsertAct1 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName1.Text.ToString() + "','" + txtActAmt1.Text.ToString() + "','" + ddlAct1.SelectedValue + "' )", cn);SqlCommand CmdInsertAct2 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName2.Text.ToString() + "','" + txtActAmt2.Text.ToString() + "','" + ddlAct2.SelectedItem.Value + "')", cn);
SqlCommand CmdInsertAct3 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName3.Text.ToString() + "','" + txtActAmt3.Text.ToString() + "','" + ddlAct3.SelectedItem.Value + "')", cn);SqlCommand CmdInsertAct4 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName4.Text.ToString() + "','" + txtActAmt4.Text.ToString() + "','" + ddlAct4.SelectedItem.Value + "')", cn);
SqlCommand CmdInsertAct5 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName5.Text.ToString() + "','" + txtActAmt5.Text.ToString() + "','" + ddlAct5.SelectedItem.Value + "')", cn);
CmdInsertAct1.ExecuteNonQuery();
CmdInsertAct2.ExecuteNonQuery();
CmdInsertAct3.ExecuteNonQuery();
CmdInsertAct4.ExecuteNonQuery();
CmdInsertAct5.ExecuteNonQuery();
cn.Close();....................................................................
in this code I want to put Commit,Begin,Rollback Transactions.Plz help me.send replies urgently.
 
 

View 3 Replies View Related

Rollback In A Trigger??? (explicit/autocommit Transactions)

Jun 21, 2006

I€™m using triggers for some more advanced integrity check. The problems is that the same trigger can be run from explicit transaction (this is when I start transaction from .NET) and as autocommit transaction ( very rare, only when we do some maintenance directly with SQL statements).

Currently if I want to rollback transaction from trigger I only issue RAISERROR statements, then .NET application catches this error and generates rollback. But the problem is if trigger is raised from some SQL statements outside .NET application (normally some maintenance work direct from SQL manager ) in that case error is generated but there is no rollback.

Is there any way to distinguish if transaction in trigger is explicit or autocommited, because for autocommited transaction I also need use ROLLBACK TRANSACTION?

I€™m using SQL 2005!

Best regards
edvin

View 6 Replies View Related

Nonqualified Transactions Are Being Rolled Back. Estimated Rollback Completion: 100%.

Apr 30, 2007

Hi all,



Sometimes when I do "alter database ABCD set partner failover" I get the following message: Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.



In 99 percent of the cases after such message the first attempt to use an open connection would also raise an error such as "Exception: A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"



After the first error all subsequent queries would run perfectly.



What am I missing?



Avi

View 4 Replies View Related

TRANSACTIONS In SSIS (error: The ROLLBACK TRANSACTION Request Has No Corresponding BEGIN TRANSACTION.

Nov 14, 2006

I'm receiving the below error when trying to implement Execute SQL Task.

"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." This error also happens on COMMIT as well and there is a preceding Execute SQL Task with BEGIN TRANSACTION tranname WITH MARK 'tran'

I know I can change the transaction option property from "supported" to "required" however I want to mark the transaction. I was copying the way Import/Export Wizard does it however I'm unable to figure out why it works and why mine doesn't work.

Anyone know of the reason?

View 1 Replies View Related

Web Part Deserialization Error When Trying To Change Report Viewer Web Part Programmatically.

Oct 29, 2007



I have SSRS 2005 SP2 configured to work in Sharepoint integration. Everything works fine except that I am not able to programmatically change any property of report viewer web part (instance) that I have added on on home page of my sharepoint site.
I can do same thing via sharepoint UI but not through program. When my programs runs it fetches all web parts been added on home page, then I need to iterate through each one and find report viewer web part.
While iterating, as soon as I arrive to report viewer web part it is named as "Error web part" with error message as
"Windows SharePoint Services cannot deserialize the Web Part. Check the format of the properties and try again"

If someone has a solution, please respond at your earlist.

Thanks

Shankar

View 1 Replies View Related

Split A Decimal Number Into The Integer Part And The Fraction Part

Dec 7, 2007

I have a table with a column named measurement decimal(18,1).  If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return  2.5.  So if the value after the decimal point is 0, I only want the stored proc to return the integer portion.  Is there a sql function that I can use to determine what the fraction part of the decimal value is?  In c#, I can use
dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.

View 3 Replies View Related

SQL Server 2005 - Save Tran Save Point Name Case Sensitive?

Feb 11, 2006

Hello:I didn't find any documentation that notes save point names are casesensitive, but I guess they are...Stored Proc to reproduce:/* START CODE SNIPPET */If Exists (Select * From sysobjects Where Type = 'P' and Name ='TestSaveTran')Drop Procedure dbo.TestSaveTranGoCreate Procedure dbo.TestSaveTranAsBeginDeclare@tranCount int--Transaction HandlingSelect @tranCount = @@TRANCOUNTIf (@tranCount=0)Begin Tran localtranElseSave Tran localtranBegin Try--Simulate Error While ProcessingRAISERROR('Something bad happened', 16, 1)/*If this proc started transaction then commit it,otherwise return and let caller handle transaction*/IF (@tranCount=0)Commit Tran localtranEnd TryBegin Catch--Rollback to save pointRollback Tran LOCALTRAN --<< NOTE case change--Log Error--Reraise ErrorEnd CatchEndGo--Execute Stored ProcExec dbo.TestSaveTran/*Should receive the following message:Cannot roll back LOCALTRAN. No transaction or savepoint of that namewas found.*//* END CODE SNIPPET */What is really strange, if there is a transaction open, then no erroris thrown. So if you execute as so:/* START CODE SNIPPET */Begin Tran--Execute Stored ProcExec dbo.TestSaveTran/* END CODE SNIPPET */There is no "Cannot roll back LOCALTRAN...." message.Questions:1-)Can someone confirm save point names are case sensitve and this isnot happening because of a server setting?2-)Is this a logic error that I am not seeing in the example codeabove?We have changed our code to store the save point name in a variable,which will hopefully mitigate this "problem".Thx.

View 4 Replies View Related

SQL 2012 :: Function With 2nd Part Working On Results 1st Part

Jan 28, 2015

I have made the following Scalar-valued function:

CREATE FUNCTION [dbo].[TimeCalc]
(
@OriginalTime AS INTEGER
, @TenthsOrHundredths AS INTEGER -- code 2: 1/10, code 4: 1/100
)
RETURNS NVARCHAR(8)

[Code] ....

What it does is convert numbers to times

E.g.: 81230 gets divided by 10 (times in seconds: 8123). This 1 1 full minute, and the remainder = 2123 making it 1.21.23 mins)

So far so good (function works perfectly)

My question: sometimes times are in 1/100 (like above sample), sometimes in 1/10.

This means that, e.g. with a time like 3.23.40 the last zero must be deleted.

My thoughts are to use the results from the Return Case part, and as the code = 4: leave it as it is,

is the code 2 the use LEFT(... result Return Case ..., Len(..result Return Case.. - 1))

There are 5 codes: 0 1 2 3 and 4

View 9 Replies View Related

Changing Connection Transactions To Database Transactions

May 22, 2005

Hi there,
I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following:
<code>Try   'execute commands   myTransaction.Commit()Catch ex As Exception   Response.Write(ex.Message)   myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction?
Any help is greatly appreciated

View 3 Replies View Related

How Can I Change The Default Save-As/Save Directory

Jun 26, 2007

I am new to sql sever management studio express, but a long time query analyzer user. This is a very basic question.



I want to change the default directory in sql server management studio express so that when I go to save a query, it is already pointed to the correct one. Where do I change that?



Thanks,

Nanci





View 2 Replies View Related

What Different Are There Between ConnectionString (Part 1) And ConnectionString (Part 2) In Web.config

Apr 12, 2006

What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config
The CCWW is my PC's name, normally I can connect to the database ASPNETDB.MDF correctly either Part 1 or Part 2 in a web page,After I open Database Explorer panel and browse ASPNETDB.MDF, I can't connect to database using Part 2 when I open a webpage in Microsoft Visual Web Developer 2005 Express Edition,but I can correctly open a webpage using Part 1 after I open Database Explorer panel.
What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config?
I guess  while I use Part 1 to connect, maybe it will be cancel exclusive method of the database  ASPNETDB.MDF first, but when I connect to database using Part 2, maybe two programms both Part 2 and Database Explorer visit ASPNETDB.MDF at the same time!
 
--------------------------------------Part 1------------------------------------------------------------------------<add name="MyConnect" connectionString="Data Source=.SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|ASPNETDB.MDF"          providerName="System.Data.SqlClient" />  --------------------------------------Part 1------------------------------------------------------------------------
--------------------------------------Part 2------------------------------------------------------------------------<add name="MyConnect"  connectionString="Data Source=CCWWSQLEXPRESS;Initial Catalog=ASPNETDB.MDF;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|ASPNETDB.MDF"     providerName="System.Data.SqlClient" />--------------------------------------Part 2------------------------------------------------------------------------

View 2 Replies View Related

How To Rollback SQL

Aug 12, 2007

Hello experts, I made a huge mistake on a production server, ran an update without the WHERE clause. UPDATE myTableSET Field1 = 'SOmetext',Field2 = 'sometext2'Is there a way to rollback? I read something a while ago about transaction logs.SOS!Thanks 

View 4 Replies View Related

Rollback

Sep 27, 2007

HI,
 Suppose I execute any transaction say inserted some rows.
I want  to delete my previous transaction using rollback.
What is the proper syntax for that.
In Query Window:
Insert ---------- executed
rollback.
it was asking for name of the transaction. How to give that.
Help me out
Thanks
Bye
 
 
 

View 1 Replies View Related

Why Not Rollback?

Nov 12, 2003

This is my stored procedure. Problem is that a rollback does not happen when there is an error ? How can this be, am starting and ending the transaction?

CREATE Procedure opretKunde
(
@cvr int,
@url varchar(200),
@firmaNavn varchar(50),
@adresse varchar(150),
@postnr int,
@fax int,
@tlf int,
@email varchar(200),
@bruger varchar(20),
@kode varchar(32)
)
AS

BEGIN TRANSACTION

INSERT INTO login
(
brugernavn,
kodeord,
type
)
VALUES
(
@bruger,
@kode,
'k'
)

INSERT INTO kunde
(
cvr,
url,
navn,
fax,
tlf,
email,
brugernavn
)

VALUES
(
@cvr,
@url,
@firmaNavn,
@fax,
@tlf,
@email,
@bruger
)

INSERT INTO adresse
(

type,
cvr,
adresse,
postnr
)
VALUES
(

'b',
@cvr,
@adresse,
@postnr
)

COMMIT

GO

View 1 Replies View Related

Rollback

Aug 10, 2000

Hi,

Can I rollback a database if I have transaction logs?

For example, I want to rollback the database without all the transactions that occurs within the last 10 days.

Thank you.

View 3 Replies View Related

Rollback

May 18, 2000

I tried killing a rollback several times, but it would not disapear.
I came back the next day, it is still there.

Anyone knows how to get rid of a rollback?

Thanks in advance.

View 2 Replies View Related

Rollback

May 5, 2000

Hi:

I have rollback on one of the users. I tried killing
her process through Enterprise Manager and Query Analyzer,
but I still can see her process on the screen. However, her
login is grayed out after I execute the kill query.

I have encountered this problem several times, and the only way
I can get clear the rollback is by rebooting the server.

Does anyone have a sugeestion on how to clear the rollback
without rebooting.

Please advise if you have any suggestion.

Thanks in advance.

View 2 Replies View Related

DTS ROLLBACK

Apr 5, 2000

Hi All
A question on DTS ROLLBACK CAPABILITY.
Is it possible to setup a DTS pkg to roll back the loaded files if a step fails?
If a step fail records inserted by Data Pump to a table should be removed.
Is this possible? If so how can we achieve this?

Any advice is appreciated.

Thanks

View 5 Replies View Related

Rollback

Dec 10, 2003

when i give rollback in my QA it gives an error saying (something like this)
- no begin transaction specified

In oracle it works fine why?

View 6 Replies View Related

Rollback

Jan 15, 2004

how to rollback(undo) record was i deleted???

pls tell me...

View 1 Replies View Related

Rollback

May 22, 2004

Hello every body,
I would like to know the procedure, that how to rollback my transaction in sql server 2000

View 5 Replies View Related

ROLLBACK

Jun 29, 2006

Hi,

i wanna know something, i inserted some rows of data to a table, but i wanna undo those insertion. How can i do it?

thanks

View 9 Replies View Related

Is Rollback Possible?

Aug 29, 2006

hi friends,

is it possible to roll back a transaction here? i heard that it will be possible only if we wrote the code only in certain block.please tell me the block.


vinod

View 5 Replies View Related

Rollback

Oct 17, 2007

The code inserts into table TABLES. TABLES has a primary key.
If the insert statement has no error it should insert 'ROWS INSERTED' to table CONTROL and update table DATETRACKING else insert 'ROWS NOT INSERTED' to table CONTROL and do not update table DATETRACKING.
1- I will like to improve my code if it is possible.
2- Can I use a rollback?

DECLARE @ROWCOUNT INT
DECLARE @ERROR INT
DECLARE @LASTDT DATETIME

SET @LASTDT =GETDATE()
BEGIN TRAN
insert tables
select 4

SELECT @ROWCOUNT = @@ROWCOUNT, @ERROR = @@ERROR
IF (@ERROR <> 0)
BEGIN
INSERT CONTROL
SELECT 'ROWS NOT INSERTED', @ROWCOUNT
END
ELSE
BEGIN
INSERT CONTROL
SELECT 'ROWS INSERTED', @ROWCOUNT
END
COMMIT TRAN

IF (@ROWCOUNT > 0)
BEGIN
UPDATE DATETRACKING
SET LASTDATEDL = GETDATE()
END




http://www.sqlserverstudy.com

View 4 Replies View Related

Rollback

Jan 28, 2008

Hi All,

Can anyone please tell how to rollback a transaction or a command.

TIA

RKNAIR

View 1 Replies View Related

Rollback SP4

May 12, 2008



Hi all

I have SQL server 2000 with SP4,
1.Some of the reason,I want roll back SP4 and I want replace SP3.
2.Is it true SP4 slow down the Quey performance compare with SP3.

any suggestion appreciated.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved