Can this be done??

Hi Sirs

Is it possible to create a transaction which inserts data into a table in
such a way that all the inserted rows are NOT visible to other processes
accessing the same table until the transaction commits the data??

Thanks in advance

Regards Morten

Morten
Tue Apr 27 09:51:17 CDT 2004

Let me rephrase.

Actually what I meant was

Is it possible for a transaction to only select the rows inserted by the
transaction itself ie. all other rows in the table created by other
transaction are not returned in a SELECT statement.

Thanks in advance

Regards Morten

"Morten Overgaard" <mov@oticon.dk> wrote in message
news:%233LALWGLEHA.988@TK2MSFTNGP11.phx.gbl...
> Hi Sirs
>
> Is it possible to create a transaction which inserts data into a table in
> such a way that all the inserted rows are NOT visible to other processes
> accessing the same table until the transaction commits the data??
>
> Thanks in advance
>
> Regards Morten
>
>

William
Tue Apr 27 10:20:21 CDT 2004

I still don't understand. Is the Transaction only wrapped around Select
statement? If not, what else is it doing? Can you show the flow of events?

What is it you don't want seen?
"Morten Overgaard" <mov@oticon.dk> wrote in message
news:#je0ZcGLEHA.1484@tk2msftngp13.phx.gbl...
> Let me rephrase.
>
> Actually what I meant was
>
> Is it possible for a transaction to only select the rows inserted by the
> transaction itself ie. all other rows in the table created by other
> transaction are not returned in a SELECT statement.
>
> Thanks in advance
>
> Regards Morten
>
> "Morten Overgaard" <mov@oticon.dk> wrote in message
> news:%233LALWGLEHA.988@TK2MSFTNGP11.phx.gbl...
> > Hi Sirs
> >
> > Is it possible to create a transaction which inserts data into a table
in
> > such a way that all the inserted rows are NOT visible to other processes
> > accessing the same table until the transaction commits the data??
> >
> > Thanks in advance
> >
> > Regards Morten
> >
> >
>
>

v-kevy
Tue Apr 27 21:41:32 CDT 2004

Thanks for Bill's quick response!

Hi Morten,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to create a SELECT command
that will only return the records inserted within the transaction which
hasn't been committed. If there is any misunderstanding, please feel free
to let me know.

As far as I know, this is impossible to be achieved in transaction. Because
the SQL server database engine commits and rolls back transactions by
checking the transaction log. However, the transaction log cannot be
accessed by SELECT statement.

For workaround, I think you can insert the new records into a DataSet and
use DataSet.GetChanges to get the newly inserted rows. For more information
about DataSet.GetChanges method, please check the following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfSystemDataDataSetClassGetChangesTopic.asp

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Akhil
Tue Apr 27 22:54:36 CDT 2004

Hi Kevin / Morten
I think it is possible.
What you have to do is Just Create a insert Trigger on the table in
which you are inserting the record and in the Trigger just write the
statement
Select * from Inserted

as Inserted table contains the newly inserted rows and fires one time of a
insert.
I thing this should work.


regds.....
Akhil




"Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> wrote in message
news:TtTwbpMLEHA.2520@cpmsftngxa10.phx.gbl...
> Thanks for Bill's quick response!
>
> Hi Morten,
>
> First of all, I would like to confirm my understanding of your issue. From
> your description, I understand that you need to create a SELECT command
> that will only return the records inserted within the transaction which
> hasn't been committed. If there is any misunderstanding, please feel free
> to let me know.
>
> As far as I know, this is impossible to be achieved in transaction.
Because
> the SQL server database engine commits and rolls back transactions by
> checking the transaction log. However, the transaction log cannot be
> accessed by SELECT statement.
>
> For workaround, I think you can insert the new records into a DataSet and
> use DataSet.GetChanges to get the newly inserted rows. For more
information
> about DataSet.GetChanges method, please check the following link:
>
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
> frlrfSystemDataDataSetClassGetChangesTopic.asp
>
> HTH. If anything is unclear, please feel free to reply to the post.
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>

CT
Wed Apr 28 03:21:08 CDT 2004

Not quite, as the updated rows haven't been commited yet.

--
Carsten Thomsen
Enterprise Development with VS .NET, UML, and MSF
http://www.apress.com/book/bookDisplay.html?bID=105
"Akhil Bansal" <akhil_bansal20@hotmail.com> wrote in message
news:Ok3qHSNLEHA.1612@TK2MSFTNGP12.phx.gbl...
> Hi Kevin / Morten
> I think it is possible.
> What you have to do is Just Create a insert Trigger on the table in
> which you are inserting the record and in the Trigger just write the
> statement
> Select * from Inserted
>
> as Inserted table contains the newly inserted rows and fires one time of a
> insert.
> I thing this should work.
>
>
> regds.....
> Akhil
>
>
>
>
> "Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> wrote in message
> news:TtTwbpMLEHA.2520@cpmsftngxa10.phx.gbl...
>> Thanks for Bill's quick response!
>>
>> Hi Morten,
>>
>> First of all, I would like to confirm my understanding of your issue.
>> From
>> your description, I understand that you need to create a SELECT command
>> that will only return the records inserted within the transaction which
>> hasn't been committed. If there is any misunderstanding, please feel free
>> to let me know.
>>
>> As far as I know, this is impossible to be achieved in transaction.
> Because
>> the SQL server database engine commits and rolls back transactions by
>> checking the transaction log. However, the transaction log cannot be
>> accessed by SELECT statement.
>>
>> For workaround, I think you can insert the new records into a DataSet and
>> use DataSet.GetChanges to get the newly inserted rows. For more
> information
>> about DataSet.GetChanges method, please check the following link:
>>
>>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
>> frlrfSystemDataDataSetClassGetChangesTopic.asp
>>
>> HTH. If anything is unclear, please feel free to reply to the post.
>>
>> Kevin Yu
>> =======
>> "This posting is provided "AS IS" with no warranties, and confers no
>> rights."
>>
>
>

Morten
Wed Apr 28 08:02:18 CDT 2004

You understood it correctly.
Thanks for your response

Regards Morten
"Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> wrote in message
news:TtTwbpMLEHA.2520@cpmsftngxa10.phx.gbl...
> Thanks for Bill's quick response!
>
> Hi Morten,
>
> First of all, I would like to confirm my understanding of your issue. From
> your description, I understand that you need to create a SELECT command
> that will only return the records inserted within the transaction which
> hasn't been committed. If there is any misunderstanding, please feel free
> to let me know.
>
> As far as I know, this is impossible to be achieved in transaction.
Because
> the SQL server database engine commits and rolls back transactions by
> checking the transaction log. However, the transaction log cannot be
> accessed by SELECT statement.
>
> For workaround, I think you can insert the new records into a DataSet and
> use DataSet.GetChanges to get the newly inserted rows. For more
information
> about DataSet.GetChanges method, please check the following link:
>
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
> frlrfSystemDataDataSetClassGetChangesTopic.asp
>
> HTH. If anything is unclear, please feel free to reply to the post.
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>

Morten
Wed Apr 28 08:03:06 CDT 2004

Thanks for all you answers - it's really appreciated

Regards Morten
"Morten Overgaard" <mov@oticon.dk> wrote in message
news:%233LALWGLEHA.988@TK2MSFTNGP11.phx.gbl...
> Hi Sirs
>
> Is it possible to create a transaction which inserts data into a table in
> such a way that all the inserted rows are NOT visible to other processes
> accessing the same table until the transaction commits the data??
>
> Thanks in advance
>
> Regards Morten
>
>

v-kevy
Thu Apr 29 02:04:24 CDT 2004

Hi Morten,

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."