December 28, 2004 at 12:49 pm
I am implementing multiple filegroups for a new database. One task for the implementation is the transfer of data between the old database and the newly created multiple filegroup database.
My concern for data transfer is regarding timestamp columns. There are few columns in the database with timestamp datatype. If I transfer data keeping the datatype as timestamp, new values are generated in the destination database. The only way, I can transfer literal values into the destination database is by converting the datatype from timestamp (no nulls allowed) to binary (8). But in this approach, I cannot reconvert the datatype back to timestamp with the 'Alter Column' command. The error thrown is:
Server: Msg 4927, Level 16, State 1, Line 1
Cannot alter column 'check_insert' to be data type timestamp.
Is there any way to insert literal values into a timestamp column without changing the datatype. If changing datatype is the only solution, then is there a way for me to reconvert the value into 'timestamp'?
regards,
Larry
December 29, 2004 at 8:40 am
Larry
Timestamp data is not used in the same way as most other data. You do not need to bring over the literal data. Timestamp data is used when implementing an optimistic concurrency control data access pattern. The data is used at runtime and the actual timestamp value does not matter. When a client retrieves data to be updated, it reads the data in the timestamp column into a local variable. When the client is ready to write the changed data, it compares the data in the local variable to that of the timestamp column, if the value is the same, the update continues, if not, the client should let the user know the data was changed before allowing a write. This update all depends on how the developer of the application controls the update in his/her code.
In the SQL BOL, search on “concurrency near timestamp”, this will allow you to get more information on how this timestamp is used. Microsoft has admitted the name is a misnomer in that the data in a timestamp column has nothing to do with a date or time. The timestamp is synonymous to rowversion and Microsoft will be changing to rowversion in a later version of SQL server.
Regards
Kevin Wagner
SR DBA, UBS
Kevin (dot) wagner (at) winsit (dot) com
Kevin Wagner
winsit.com
January 3, 2005 at 8:17 pm
change you data transform script to exclude the timestamp column then new values will be created in the destination database. this will work only if you don't want to keep the exactly same timestamp values(what's the reason you want the exact values?).
There is no way you can have the same values in the destination table without changing the data type of the timestamp column.
January 4, 2005 at 12:37 am
Hi,
If I'm not mistaken, one reason (in addition to those mentioned above) for keeping timestamp values could be to know (on database level, not application level) the time of the last transaction on a particular record (either when created or when last updated)
EM
January 4, 2005 at 2:29 am
That is not correct. The timestamp datatype doesn't contain any information pertaining to the dimensions of time as we mere mortals view it. Timestamp is simply a random value without meaning, it's only usage is to compare with other timestamps for equality or difference, nothing else.
/Kenneth
January 5, 2005 at 4:18 am
Hi,
You're right Kenneth, as regarding SQL Server, and maybe i was too nonspecific, but in Oracle and DB2 for example the ACTUAL TIME of (insert/update) for the record is stored in the timestamp column.
Now you can tell me that this is an SQL Server forum, but as i said, i was too nonspecific.
Thank you.
EM
January 5, 2005 at 7:38 am
Hello Again
As I said in my earlier post...
Microsoft has admitted the name is a misnomer in that the data in a timestamp column has nothing to do with a date or time. The timestamp is synonymous to rowversion and Microsoft will be changing to rowversion in a later version of SQL server.
Kevin Wagner
winsit.com
July 10, 2006 at 12:38 pm
It appears that if you copy data into a new table by using the selct * into ... syntax than it keeps the original timestamps.
I am trying to find information on how to restore a few rows into a production table, ie I restored a DB backup to a new name, want to copy a handfull of rows from the restored database to the production DB/table exactly as they were before they were accidenlty deleted. It appears with the timestamp column in this table, that I am sunk. It is impossible to return the table to the way it was before the deletes unless I restore the whole table. This is not practicle in my situation.
July 11, 2006 at 12:00 am
If you can explain why you need to restore exactly the same values in timestamp column and what use of it you mean to have we'll probably think about solution of your problem.
As for me it's absolutely useless exercise. Any value in timestamp column is right.
_____________
Code for TallyGenerator
July 11, 2006 at 6:23 am
I had a purchased application that production data disappeared from a table with no known cause. We believe it to have been a bug. Vendar has been contacted, and they say they don't know what happened - just restore it and if it happens again call. I was trying to restore exactly as it was because I do not know what the field is used for by the application. I felt it was safer to have the data restored as it was. In any case, I decided to restore with new timestamps. There didn't appear to be a way around it and it seemed likely not ot hurt anything to do this.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply