Can timestamp columns be updated?

Hi,
Is there any way timestamp columns can be updated in Microsoft SQL Server
2000 (SP3)?
-- Many thanks, OskarNot directly.
Linchi
"Oskar" wrote:

> Hi,
> Is there any way timestamp columns can be updated in Microsoft SQL Server
> 2000 (SP3)?
> -- Many thanks, Oskar
>|||update the row to itself, i.e. update MyTableName set Col1=Col1 where pk=pk
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:BB373431-C1DB-4A5D-B02D-18B688203F99@.microsoft.com...[vbcol=seagreen]
> Not directly.
> Linchi
> "Oskar" wrote:
>|||Thanks. Not sure if this helps though. I really wanted to update the
timestamp column with the value I choose not the one of the internal @.@.DBTS
counter.
Any ideas if there's a way of updating the value the internal @.@.DBTS
counter, besides updating a zillion of records? Linchi?
"Hilary Cotter" wrote:

> update the row to itself, i.e. update MyTableName set Col1=Col1 where pk=p
k
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:BB373431-C1DB-4A5D-B02D-18B688203F99@.microsoft.com...
>
>|||"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:C9211B6D-90B4-43B2-AD80-F068FB8ACF17@.microsoft.com...
> Thanks. Not sure if this helps though. I really wanted to update the
> timestamp column with the value I choose not the one of the internal
@.@.DBTS
> counter.
There is no way. Not really sure why you'd want to anyway. There's
probably better ways of doing what you want to do.

> Any ideas if there's a way of updating the value the internal @.@.DBTS
> counter, besides updating a zillion of records? Linchi?|||No, you can't directly update it. You can't do something like the following:
update myTable
set my_timestamp_column = ...
Linchi
"Oskar" wrote:
[vbcol=seagreen]
> Thanks. Not sure if this helps though. I really wanted to update the
> timestamp column with the value I choose not the one of the internal @.@.DBT
S
> counter.
> Any ideas if there's a way of updating the value the internal @.@.DBTS
> counter, besides updating a zillion of records? Linchi?
>
> "Hilary Cotter" wrote:
>

Can timestamp columns be updated?

Hi,
Is there any way timestamp columns can be updated in Microsoft SQL Server
2000 (SP3)?
-- Many thanks, OskarNot directly.
Linchi
"Oskar" wrote:
> Hi,
> Is there any way timestamp columns can be updated in Microsoft SQL Server
> 2000 (SP3)?
> -- Many thanks, Oskar
>|||update the row to itself, i.e. update MyTableName set Col1=Col1 where pk=pk
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:BB373431-C1DB-4A5D-B02D-18B688203F99@.microsoft.com...
> Not directly.
> Linchi
> "Oskar" wrote:
>> Hi,
>> Is there any way timestamp columns can be updated in Microsoft SQL Server
>> 2000 (SP3)?
>> -- Many thanks, Oskar|||Thanks. Not sure if this helps though. I really wanted to update the
timestamp column with the value I choose not the one of the internal @.@.DBTS
counter.
Any ideas if there's a way of updating the value the internal @.@.DBTS
counter, besides updating a zillion of records? Linchi?
"Hilary Cotter" wrote:
> update the row to itself, i.e. update MyTableName set Col1=Col1 where pk=pk
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:BB373431-C1DB-4A5D-B02D-18B688203F99@.microsoft.com...
> > Not directly.
> >
> > Linchi
> >
> > "Oskar" wrote:
> >
> >> Hi,
> >> Is there any way timestamp columns can be updated in Microsoft SQL Server
> >> 2000 (SP3)?
> >>
> >> -- Many thanks, Oskar
> >>
>
>|||"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:C9211B6D-90B4-43B2-AD80-F068FB8ACF17@.microsoft.com...
> Thanks. Not sure if this helps though. I really wanted to update the
> timestamp column with the value I choose not the one of the internal
@.@.DBTS
> counter.
There is no way. Not really sure why you'd want to anyway. There's
probably better ways of doing what you want to do.
> Any ideas if there's a way of updating the value the internal @.@.DBTS
> counter, besides updating a zillion of records? Linchi?|||No, you can't directly update it. You can't do something like the following:
update myTable
set my_timestamp_column = ...
Linchi
"Oskar" wrote:
> Thanks. Not sure if this helps though. I really wanted to update the
> timestamp column with the value I choose not the one of the internal @.@.DBTS
> counter.
> Any ideas if there's a way of updating the value the internal @.@.DBTS
> counter, besides updating a zillion of records? Linchi?
>
> "Hilary Cotter" wrote:
> > update the row to itself, i.e. update MyTableName set Col1=Col1 where pk=pk
> >
> > --
> > Hilary Cotter
> > Director of Text Mining and Database Strategy
> > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> >
> > This posting is my own and doesn't necessarily represent RelevantNoise's
> > positions, strategies or opinions.
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> > Looking for a FAQ on Indexing Services/SQL FTS
> > http://www.indexserverfaq.com
> >
> >
> >
> > "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> > news:BB373431-C1DB-4A5D-B02D-18B688203F99@.microsoft.com...
> > > Not directly.
> > >
> > > Linchi
> > >
> > > "Oskar" wrote:
> > >
> > >> Hi,
> > >> Is there any way timestamp columns can be updated in Microsoft SQL Server
> > >> 2000 (SP3)?
> > >>
> > >> -- Many thanks, Oskar
> > >>
> >
> >
> >

Can table1 in example below get updated by another process while the transaction is in pro

I am afraid that just after @.statusOfEmployee is retrieved from table1, but before table2 is updated, someone else (a second user) calls this same stored procedure and changes the @.statusOfEmployee value. This would create aninconsistentupdate of table2 by first user, since the update of table2 'might' not have gone ahead if the latest value of @.statusOF Employee was used. CAN SOMEONE PLEASE HELP ME WITH THIS SITUATION AND HOW I CAN BE SURE THAT ABOVE DOES NOT HAPPEN SINCE MULTIPLE USERS WILL BE HITTING THIS STORED PROCEDURE?

declare @.status int
begin tran
set @.status = (select statusOfEmployee from table1)
if @.@.ERROR = 0
begin
update table2
set destination = @.destination /* @.destination is an input parameter passed to the sp*/
where @.currentStatus = @.status
if @.ERROR = 0
commit tran
else
rollback tran
end
else
rollback tran

return

update table2
set destination = @.destination /* @.destination is an input parameter passed to the sp*/
where @.currentStatus = (select @.statusOfEmployee from table1)

This will only work if you have only 1 row in table1, but I'm guessing this isn't your real SP. If there can be more than 1, then you have other issues. And to answer your question, yes, it could have been updated between those statements.

|||

Sorryy. I meant a field there. I will edit it.

One question for you: If I used the original sp I mentioned in my post, then is there a danger of incosistent update as I have explained OR because the select is in a transaction, SQL Server will prevent any changes to tables being used in the transaction?

I was going to mark the ADO.Net code that calls this stored procedure as 'critical' in my C# code using lock(this) { }. That way only one user can execute this stored procedure at a time from the application, which eliminates any chances of inconsistent updates. ANYONE HAS ANY COMMENTS ON USING THIS APPROACH TO PREVENT INCOSISTENT UPDATES?

|||

I would think even with your approach, since the select and update are on different tables, there is nothing preventing another user from updating the table used in select statement. SQL Server willonlyprevent any user from updating 'table2' while this update statement is in progress.

|||

Yes, your original SP isn't multi-user safe. No, the one I gave you is. The difference is the type of locks that are requested and the duration for which they are held.

|||

So even though in your query a simple select is being executed on 'table1', SQL Server will place an exclusive lock on 'table1' row.

I thought that under default SQL Server 2000 locking (read committed), select statements will not place an exclusive lock on the row involved in select query. And if this is true, then including the 'select' within the 'update' will still allow someone else to update 'table1' before the update to 'tabl2' happens. Right or wrong?

|||

Because the select is happening within the confines of the UPDATE statement, the subquery will place a read-lock (sharing) on table1's row during the entire time the update is occuring. The update can not happen without this read-lock, and no other updates can happen to table1 while this statement has the read-lock in place.

The real issue that your prior SP had was that the read-lock on table 1 was released as soon as the SET @.var= was completed, which would allow someone to update table1 before table2 was updated. You could accomplish nearly the same thing with some of the locking hints, or changing the transaction isolation mode, but the SP won't execute as quickly as incorporating it into one statement like I did, and that means locks are being held longer than they need to be.

SET @.var=(SELECT ... WITH (HOLDLOCK) ...)

would have accomplished the same thing. If you add more steps to your transaction, then the read lock will be held until the completion of the transaction. With the combined UPDATE, the read lock is dropped when the UPDATE completes (The update lock on table2 is held until the end of the UPDATE if it's not in a transaction, or until the transaction is commited/rolled back...).

|||

Great explanation. It helped clarify an important point to me.

Thanks for that.

Can SQL Profiler Track a Field in a Table?

Greetings,
I have a field, "MyDate", that is being updated everytime a trigger
runs. MyDate is supposed to update with a getdate() value. However, i
look at the data, and i see some null values in there!
Is there a way I can use profiler to track when MyDate changes value,
and what value is updating MyDate?
I tried to have the trigger dump all MyDate values into a table, and i
have access to that data, but i still don't know WHY there are some NULL
values in there!
Thanks,
Don
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Profiler can track the execution of statements, but cannot be used to track
the actual value of variables and the like. If your trigger is supposed to
update the column, then I would suspect faulty trigger logic. Have you
considered disallowing null for the column? Another alternative is to
create a separate trigger, mark it to execute last, and do nothing but check
for NULL in the inserted/updated rows (with a corresponding
raiserror/rollback). This would at least allow you to figure out what is
causing the problem.
"don larry" <donlarry17@.hotmail.com> wrote in message
news:e$Dg$gcoEHA.868@.TK2MSFTNGP10.phx.gbl...
> Greetings,
> I have a field, "MyDate", that is being updated everytime a trigger
> runs. MyDate is supposed to update with a getdate() value. However, i
> look at the data, and i see some null values in there!
> Is there a way I can use profiler to track when MyDate changes value,
> and what value is updating MyDate?
> I tried to have the trigger dump all MyDate values into a table, and i
> have access to that data, but i still don't know WHY there are some NULL
> values in there!
> Thanks,
> Don
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Can SQL Profiler Track a Field in a Table?

Greetings,
I have a field, "MyDate", that is being updated everytime a trigger
runs. MyDate is supposed to update with a getdate() value. However, i
look at the data, and i see some null values in there!
Is there a way I can use profiler to track when MyDate changes value,
and what value is updating MyDate?
I tried to have the trigger dump all MyDate values into a table, and i
have access to that data, but i still don't know WHY there are some NULL
values in there!
Thanks,
Don
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Profiler can track the execution of statements, but cannot be used to track
the actual value of variables and the like. If your trigger is supposed to
update the column, then I would suspect faulty trigger logic. Have you
considered disallowing null for the column? Another alternative is to
create a separate trigger, mark it to execute last, and do nothing but check
for NULL in the inserted/updated rows (with a corresponding
raiserror/rollback). This would at least allow you to figure out what is
causing the problem.
"don larry" <donlarry17@.hotmail.com> wrote in message
news:e$Dg$gcoEHA.868@.TK2MSFTNGP10.phx.gbl...
> Greetings,
> I have a field, "MyDate", that is being updated everytime a trigger
> runs. MyDate is supposed to update with a getdate() value. However, i
> look at the data, and i see some null values in there!
> Is there a way I can use profiler to track when MyDate changes value,
> and what value is updating MyDate?
> I tried to have the trigger dump all MyDate values into a table, and i
> have access to that data, but i still don't know WHY there are some NULL
> values in there!
> Thanks,
> Don
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!