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


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

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

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

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

Sp-doubt

May 3, 2008

 frenz:
i have the following procedure:while running


EMP_SP_EmpDetails_Display 'FirstName', 'ASC'

i got the error like this: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

plz can any one solve this problem.




My procedure:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER procedure [dbo].[EMP_SP_EmpDetails_Display]
-- Add the parameters for the stored procedure here
(@colum as varchar(50),@order as varchar(50))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @sqlexec varchar(1500)


-- Insert statements for procedure here
set @sqlexec=''
set @sqlexec=@sqlexec + 'SELECT EL.UserName,EL.Password, E.EmpID, E.FirstName, E.LastName, E.DesignationID, E.DepartmentID, E.Sex '
set @sqlexec=@sqlexec + 'E.DOB, E.Age, E.Address, E.Email, H.Hobby,'
set @sqlexec=@sqlexec + 'ContactNo=Case When E.phone is not null Then E.phone '
set @sqlexec=@sqlexec + 'When E.phone is null Then E.Mobile End, '
set @sqlexec=@sqlexec + 'E.DateOfjoining, D.Designation, DE.Department '
set @sqlexec=@sqlexec + 'From dbo.EMP_Tbl_Employee E '
set @sqlexec=@sqlexec + 'Inner Join dbo.EMP_Tbl_Designation D On E.DesignationID=D.DesignationID '
set @sqlexec=@sqlexec + 'Inner Join dbo.EMP_Tbl_Department DE On E.DepartmentID=DE.DepartmentID '
set @sqlexec=@sqlexec + 'Inner Join dbo.EMP_Tbl_EmployeeHobbies EH On E.EmpID=EH.EmpID '
set @sqlexec=@sqlexec + 'Inner Join dbo.EMP_Tbl_Hobby H On E.Sno=H.Sno '
set @sqlexec=@sqlexec + 'Inner Join dbo.EMP_Tbl_EmployeeLogin EL On E.Sno=EL.Sno order by '+@colum+' '+@order+''

exec(@sqlexec)

END

View 1 Replies View Related

So Doubt

Nov 4, 2003

i have some doubt that if we want to put some data in the database, is two paragraph words, so is it i need to type this two paragraph word in the database?

how i can made a paragraph in database? is it possible?

View 5 Replies View Related

Doubt

Mar 2, 2005

Hi there

I have a question here. I need to import the excel template into sql server data base tables. I did worked on the regular importing of excel spread sheets to import into the appropriate data base columns. I am not getting idea with the eacel template. My main goal is to import the excel template to sql tables and then reproduce the same template for the user in the browser. If any body can help i would really appreciate.....

View 6 Replies View Related

A Doubt

May 19, 2008

hi all

I have heard that neither SQL Server 2000 nor SQL Server 7.0 supports adding the IDENTITY property to an existing column by using a single T-SQL command .Is it true?

ok
tanx in advance

View 2 Replies View Related

Sql Doubt

Dec 12, 2005

ramesh writes "i was using oracle database in last 2 years.
presently my concern using sqlserver 2000.
so i have some doubt in sqlserver 2000.
pls answer me.

1.Give some tips T-SQL with Examples.
2.how to call .sql files in query analyser."

View 4 Replies View Related

Doubt With LSN

Jan 25, 2007

Hello,

When hourly transcation log backups are taken , is any log sequence number(LSN) generated .

I want to know if this is right or wrong.I
do the T-log backups until 10.00 pm and I do the complete DB backup at 12.00 AM. So the complete DB backup contains the DB and the T-log from 10.00 pm till 11.59 pm.

So, is my understanding right.

Thanks

View 2 Replies View Related

Doubt

Jun 5, 2007

Hi,
I have a table in my database. Now I will import the data into table from excel. Now I have Firstname,Lastname,email,phone from excel.There is a chance some email,phone can be null. Now I have to identify and update the null values with values from tables from another database which has a master table containing all values.

How to write a script?

View 4 Replies View Related

Doubt Is SQL

Mar 28, 2008

Hi guys I am new to SQL Pls help me on this...

In sql server 2005 we have ROW_NUMBER() as inbuilt function...
But i am using sql server 2000... instead of ROW_NUMBER () what is the functionality in SQL SERVER 2000. can anyone help me out in this pls...

Thnaks in advance!!!!

Thanks and Regards,
Samuel P

View 2 Replies View Related

Doubt

Jul 20, 2005

hi,How to search a word in the sqltable?.ie suppose the field name has data type nvarchar(200). From there i enter the persional address of that persion. now i want to know the information whoes are coming from particular city?. The city value can enter in the address field. how we can make search?...with regards,ks.kumar****************************************** This message was posted via http://www.sqlmonster.com** Report spam or abuse by clicking the following URL:* http://www.sqlmonster.com/Uwe/Abuse...753175428d29c27*****************************************

View 1 Replies View Related

Doubt

Jul 28, 2007

Hi,

How to retrive the health insurance claim cms form in fastest way using sql server 2005

View 1 Replies View Related

SQL Query Doubt

Jun 26, 2007

Hello:
I had a Sql query doubt. I have two tables. One is table with profile data but in this table instead of actually values it stores codes. The value of each code is stored in another table. Something like this
Profile Table
ProfileID    Title  First Name  Last Name       State
1             122          xxx          yyy                 333
So as you can see title and state both are code which are further stored in another table which is something like this
Code_ID         Code_Value
122                   Director
124                   International
333                  AZ
Now I want to replace all these codes with values. Something like this
ProfileID    Title               First Name  Last Name       State
1             Director           xxx              yyy                 AZ
I tried Case statement but it fails. I am not sure how to do this. I have hundred thousands of records to doing programmaticall slows the process. I would appreciate your help or ideas.

View 2 Replies View Related

Doubt On Recovery

Aug 9, 1999

When I try to restore a database on one server from another server, by choosing the ‘From device’ option, I am not able to see any mapped drives. So I always have to copy the backup file to my local drive before performing the restore. Is there any other way to do it?
For example, Server1 contains a database db1. A full backup, differential backup and log backups of db1 are stored in ,
D:mssql7ackupfulldb1_full.bak
D:mssql7ackupdiffdb1_diff.bak
D:mssql7ackuplogdb1_log.bak
The backup devices have been created using sp_addumpdevice.
Server2 contains a database db1.
If I need to restore a copy of db1 on server1 onto db1 of server2, I first copy
D:mssql7ackupfulldb1_full.bak
D:mssql7ackupdiffdb1_diff.bak
D:mssql7ackuplogdb1_log.bak
to a local drive on server2.
I then restore using the ‘from device’ option.
SQL Server uses NT Domain account with admin. Privileges, log on as a service rt. Etc.

Thankyou.
Praveena

View 1 Replies View Related

Doubt On Recovery

Aug 10, 1999

When I try to restore a database on one server from another server, by choosing the ‘From device’ option, I am not able to see any mapped drives. So I always have to copy the backup file to my local drive before performing the restore. Is there any other way to do it using Enterprise Manager?
For example, Server1 contains a database db1. A full backup, differential backup and log backups of db1 are stored in ,
D:mssql7ackupfulldb1_full.bak
D:mssql7ackupdiffdb1_diff.bak
D:mssql7ackuplogdb1_log.bak
The backup devices have been created using sp_addumpdevice.
Server2 contains a database db1.
If I need to restore a copy of db1 on server1 onto db1 of server2, I first copy
D:mssql7ackupfulldb1_full.bak
D:mssql7ackupdiffdb1_diff.bak
D:mssql7ackuplogdb1_log.bak
to a local drive on server2.
I then restore using the ‘from device’ option.
SQL Server uses NT Domain account with admin. Privileges, log on as a service rt. Etc.

Thankyou.
Praveena

View 1 Replies View Related

Sql Server Doubt

Aug 13, 2004

Hi all
I am using osql utility in sql server 2000 to convert data in database to excel sheet. My datas contain both english and chinese . While converting to excel sheet english datas are comming but the datas in chinese is comming like (?) this. Does any body has any idea to solve this problem. is there any way to set font in osql utility.
Note: I tried giving chinese font in excel still it is not working.
Its really urgent pls reply
thanks

View 2 Replies View Related

Installation Doubt

Apr 3, 2008

can we install a named instance first with out default instance?

The default recovery model in sql server is full or simple?

View 1 Replies View Related

Replication Doubt

Apr 13, 2008

Hello Could any one clear my doubts

1. Why it is necessary to have a primary key for a table in order to publish.
2. After setting up transactional replication can we add another table at the publisher side.If it is possible how to replicate that table.

View 2 Replies View Related

Foreign Key Doubt

Apr 15, 2008

hi al,
We normally use foreign key in a table, as a reference to a primary key in another table.
But by definition foreign key can assume null value.so my question is when we are using foreign key as null in a table.Clarification with an example would be great..
thanks in advance..

View 1 Replies View Related

Small Doubt

May 29, 2008

in command promt how to come out c drive to d drive

View 2 Replies View Related

Doubt In GROUP BY...

Jun 12, 2008

Hi All,

select MatchID, ClubID, Max(Distance) as 'MaxDist'
From dbo.TrendHALF_SECOND_POSITIONS
Where MatchID in (8,12)
Group By MatchID, ClubID

How to get the PlayerID who has max(distance),
If i do Group By MatchID, ClubID,PlayerID i can get the playerID,
But I need PlayerID, but PlayerID should not include in Group By clause

How can i achieve it..

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

View 5 Replies View Related

Query Doubt!!

Jun 13, 2008

Hi,

I just execute the following statement

Delete top 1 from <table name>
it is giving the error "incorrect syntax near 1"


But this statement is working fine .

Delete top (1) from <table name>

What is the differance between these statements..?

Can any can explian me..


Thanks
Lakshmi.S

View 3 Replies View Related

Query Doubt

May 28, 2007

hi folks,

i want the difference of 15 mints i.e 24.15000 but it is giving 24.45000

select (datediff(mi,'2007-04-28 14:30:14.347','2007-04-29 14:45:14.347')/60.0)

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 8 Replies View Related







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