May 4, 2009 at 4:33 am
Hi all,
I have an Issue can any one help me pls.
I want the data of a table before the beginning of a transaction.
Let me explain clearly.
I have a table let's say Employee.
CREATE TABLE Employee
(
Id INT,
[Name] NVARCHAR(100)
)
I inserted data into it like
INSERT INTO Employee
SELECT 1,'A',
UNION
SELECT 2,'B',
UNION
SELECT 3,'D'
I'll Update the table in a transaction in a session
BEGIN TRAN
UPDATE Employee
SET [Name] = 'C'
WHERE Id = 3
BEFORE Committing the Transaction I'll open a new session(window in SSMS)
and I will try to select the data from Employee.
here I want a query which returns the data which was there before the transaction began.
don't say use WITH(NOLOCK) or WITH(READPAST) query hints.
because WITH(NOLOCK) will bring updated data,and WITH(READPAST)
Will bring data except the rows are locked in transaction i.e. row of
Id = 3
Please help me...
May 4, 2009 at 4:38 am
use trigger on that table, for more info see SQL BOL.
May 4, 2009 at 4:45 am
Look for ISOLATION LEVELS in Books Online..
Setting the isolation level to "READ COMMITTED" should avoid the dirty reads (not sure on this).
--Ramesh
May 4, 2009 at 4:48 am
This can not be done using SQL Server 2000. If it is important for you and you can work with SQL Server 2005 or 2008, then read in BOL about snapshot isolation level.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 4, 2009 at 4:50 am
In read committed isolation level(its the default) uncommitted transactions will not be returned.
In read uncommitted(least isolation level) you can get the dirty reads,uncommitted data.
Since you did'nt mention any isolation level in your query,you will not get the updated rows.
you'll get the updated rows if you mention read uncommitted.
select * from employee(with readuncommitted)
May 4, 2009 at 4:50 am
Yes Mr.Adi I am using SqlServer2008
May 4, 2009 at 5:01 am
ashokdasari (5/4/2009)
Yes Mr.Adi I am using SqlServer2008
In that case you should post you question in the correct forum (this forum is about SQL Server 2000 and SQL Server 7). Since you using SQL Server 2008, you can modify your database to enable row versioning and work with one of the snapshot isolation levels. Notice that this is a very big change, that will modify the way that the database and applications work, so be sure to read about it in BOL and understand what is going to be modified. I’d start by reading the topic “Understanding Row Versioning-Based Isolation Levels”.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 4, 2009 at 5:06 am
ok Adi and I'll go through the Isolation Levels.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply