Retrieving updates made within a transaction?

Hi All,

This seems like a tricky question to me. I have a Stored Procedure
that encapsulates a number of updates to various tables within a
transaction.

However, at a later part of the transaction I need to be able to
select records changed by an update statement made earlier within the
same stored proc (and within the same transaction) and need for that
select to reflect the changed values.

My understanding, however, is that the records aren't actually changed
by the update statement until the transaction is committed, and
therefore my later select statement won't return the expected records
because the update is being held until the transaction is committed.

Is this accurate? And, if so, is there a reasonable workaround that
still leaves me able to rollback the entire transaction if I strike a
problem somewhere along the way?

So, a pseudo code example would be:

BEGIN TRANSACTION

UPDATE mytable SET myid = @.yourid WHERE myid = @.id

SELECT * FROM mytable where myid = @.id

COMMIT TRANSACTION

In this above example, would the select statement return the records
that have a myid value of @.id as before the update as after the
update?

Many, many thanks in advance!

Much warmth,

MurrayOn Thu, 01 Apr 2004 16:26:01 GMT, M Wells wrote:

(snip)
>My understanding, however, is that the records aren't actually changed
>by the update statement until the transaction is committed, and
>therefore my later select statement won't return the expected records
>because the update is being held until the transaction is committed.
>Is this accurate?

No. The updates are made. If you rollback the transaction later, the
changes will be undone; the log file is used for this. During the
transaction, you'll get to see the changed data.

Other users (or even you yourself on another connection!) won't see
the changed data until the transaction is committed (*). They won't
see the old data either. The affected rows are locked as soon as they
are hit by an update and this lock will remain until the transaction
is either committed or rolled back.

(*) Exception - you can set a transaction's isolation level to "Read
uncommitted" (aka dirty read). This will cause select statements to
defy any locks and just read the "dirty" data - "dirty", since the
transaction may still be rolled back in which case the reading
transaction has read data that never really existed.

>So, a pseudo code example would be:
>
>BEGIN TRANSACTION
>UPDATE mytable SET myid = @.yourid WHERE myid = @.id
>SELECT * FROM mytable where myid = @.id
>COMMIT TRANSACTION
>In this above example, would the select statement return the records
>that have a myid value of @.id as before the update as after the
>update?

It won't return any rows at all, since the update has just changed to
myid value from @.id to @.yourid.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 01 Apr 2004 21:28:22 +0200, Hugo Kornelis
<hugo@.pe_NO_rFact.in_SPAM_fo> wrote:

>>
>>BEGIN TRANSACTION
>>
>>UPDATE mytable SET myid = @.yourid WHERE myid = @.id
>>
>>SELECT * FROM mytable where myid = @.id
>>
>>COMMIT TRANSACTION
>>
>>In this above example, would the select statement return the records
>>that have a myid value of @.id as before the update as after the
>>update?
>It won't return any rows at all, since the update has just changed to
>myid value from @.id to @.yourid.

Hi Hugo,

Thanks for your help! And, laugh, just shows I shouldn't write pseudo
sql before my first cup of coffee.

Thanks again!

Much warmth,

Murray

Retrieving Most recent transaction date

Hi,

I have a fact table containing the transaction code and date on a daily basis. I need to find out for a particular account the most recent date when a particular transaction 'abc' was received. I need to find out most recent dates for other such transactions as well to be displayed on one single report. Basically I would need something like:

ABC Date - Most recent date when 'ABC' transaction was sent

XYZ Date - Most recent date when 'XYZ' transaction was sent. ........

I thought of using the filter available on the cube browser, but if I use filter and use the '=' operator I can only specify one transaction code. But I need dates for different transaction codes.

Any thoughts on this would be greatly appreciated.

Thanks.

Here's a sample Adventure Works query, which returns the last order date for each Promotion listed:

>>

With

Member [Measures].[LastDate] as

Tail(NonEmpty([Date].[Date].[Date].Members,

{[Measures].[Order Quantity]})).Item(0).MemberValue

select

{[Measures].[Order Quantity],

[Measures].[LastDate]} on 0,

Non Empty [Promotion].[Promotion].[Promotion].Members on 1

from [Adventure Works]

-

Order Quantity LastDate
No Discount 238,806 7/31/2004
Volume Discount 11 to 14 18,181 6/30/2004
Volume Discount 15 to 24 10,713 6/1/2004
Volume Discount 25 to 40 2,321 6/1/2004
Volume Discount 41 to 60 85 4/1/2004
Mountain-100 Clearance Sale 456 6/1/2002
Sport Helmet Discount-2002 492 7/1/2002
Road-650 Overstock 304 8/1/2002
Sport Helmet Discount-2003 680 7/1/2003
Touring-3000 Promotion 1,581 9/28/2003
Touring-1000 Promotion 775 9/23/2003
Mountain-500 Silver Clearance Sale 382 6/1/2004

>>

retrieve information from before a transaction

I have a report and it retrieves information from an etl logging table which contains this info: etl_process_name, active_since_date, last_run_date.

When the packages are executing, the table cant be queried because it is in a transaction. Therefore the report keeps generating :s
The thing I want is that: If I query that table (with the report to show the logging table information) and the packages are executing, I want to see the values of the table before the transaction.

What I tried:
From sql management studio:

ALTER DATABASE MyDataBase
SET ALLOW_SNAPSHOT_ISOLATION ON
set transaction isolation level snapshot
go
SELECT etl_process, last_run, active_since FROM config.etl_settings

I did this while the packages were executing and it works but it won't work when I execute the same SELECT statement from the Dataset in the report. How is that possible? Does it have something to do with setting the database options for all users?

Additional info:
I'm database owner
The report solution has a shared datasource which refers to the same database

What is the error message are you getting?|||

Hmmm, I still don't know why it didn't work yesterday but today the report gets generated and shows the data from before a the transaction that is busy at the moment.

All I did was alter my database to edit the transaction isolation level:

ALTER DATABASE MyDataBase
SET ALLOW_SNAPSHOT_ISOLATION ON
set transaction isolation level snapshot
go

retrieve from db then write to text file

basically i am trying to create a program wherein after saving a new transaction to the sql database, the fields saved will be retrieved and then written to a text file.

i read a thread here which is similar to what i am trying to do but it was in xml format..

hope someone anwers me...i really need help!

thanks!

I'm not sure what the difference is between the thread you mentioned and what you want to do. I would expect that the challenging thing is to retrieve the fields that were just saved. Once you have the information, changing it to the appropriate format is potentially tedious, but not difficult.

Do you have a reference to the thread?