April 27, 2005 at 9:15 am
This one has me stumped. I am trying to insert a record in a table with a timestamp column. I know this is generated at the time of insert or update, correct. I am thinking that I should be able to leave this out of my insert statement. The error states that data will be truncated. If I place it in my statement it states that I cannot insert a non-null value. The strange thing to me is that I can copy a row from SEM and paste it in the table. Can someone clue me in as to what SEM is doing differently than Query analyzer. The insert is nothing special so I am not including it in this posting.
Thanks in advance.
BK
April 27, 2005 at 9:22 am
Maybe this will help :
GO
create table #t
(
Col1 varchar(10) not null,
TS timestamp not null
)
GO
Insert into #t (Col1) values ('test')
Insert into #t (Col1) values ('test2')
Insert into #t (Col1) values ('test3')
Select * from #t
DROP TABLE #t
April 27, 2005 at 9:26 am
Thanks Remi, I must have something wrong with my query.
BK
April 27, 2005 at 9:30 am
If you can't work it out, post your query and we'll help you.
April 28, 2005 at 1:07 am
I don't think there is an issue with timstamp column. check the other fileds
My Blog:
April 28, 2005 at 6:48 am
Would one of you sql gurus please explain the difference between using a timestamp datatype and a datetime datatype - eg: I have often used a datetime datatype with a default of getdate() and it works the same as if I were using a timestamp datatype!
**ASCII stupid question, get a stupid ANSI !!!**
April 28, 2005 at 6:55 am
1 - timestamp is not a date (can't use date functions on this)
2 - timestamp is maintained by sqlserver on the insert and each update whereas the datetime must be maintained with a trigger.
3 - You can't change the value of a timestamp manually.
anything else????
April 28, 2005 at 7:11 am
Timestamp in SQL Server is not equivalent to the ANSI type timestamp. Timestamp in SQL Server can also be called by it's synonym, rowversion. In fact you should only use rowversion since a future version of SQL Server might change the behaviour of the timestamp datatype to correspond to the ANSI timestamp type.
April 28, 2005 at 7:52 am
Chris/Remi - I have several questions:
1) when I go to specify a datatype - I do not see "rowversion" in the dropdown.
2) BOL specifically says "To record the times data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers to automatically update the values when any modification takes place" -
could you please give me examples of when & why we would use timestamp/rowversion datatypes ?!
3) I was experimenting with timestamp & I had the same problem that the original poster had.
4) lastly - how do you view this field ?!
**ASCII stupid question, get a stupid ANSI !!!**
April 28, 2005 at 8:17 am
1 - Not sure, check books online
2 - Just create a datetime column, then create a trigger that will update that column everytime the row is modified.
3 - It can be usefull to keep a rowversion information. Let's say you have multiple users that can modify the same data. User1 opens a form and start editing the client's info. But at the same time User2 starts editing the same information. User2 hits save before User1. Sql server changes the timestamp value. When user1 would try to save the data, I would have the software check the server's timestamp vs the front end timestamp : if they don't match, block the update or ask if they want to erase the new data.
4 - Can't really view it as it is binary and it's a bunch of meaningless numbers...
April 28, 2005 at 8:21 am
1. rowversion is a SQL Server 2000 synonym for timestamp. Right now, they are the same, but in later versions of SQL Server, if you have DDL scripts that create / alter tables, using timestamp will cause problems. You must use the CREATE TABLE statement in a script (not EM) to use rowversion.
2. timestamp (or rowversion), although it can be displayed as a date using CONVERT or CAST, is meaningless as a date/time. Use a column defined as datetime (or smalldatetime) and update it in your INSERT and UPDATE triggers. You use a timestamp field so that changes to a row can be detected, particularly in an environment where many updates are occurring. It allows the client application to detect that a row has been changed since it was originally SELECTed (that is, client A retrieves the row, modifies it, then wants to save the changes. But in the mean time, client B has changed the same row).
3. When inserting or updating a table that contains a timestamp column, do NOT reference that column in your INSERT or UPDATE statements. It is handle by SQL Server automatically.
4. You can view it as a hexadecimal value (timestamp is Binary(8)), or convert it to bigint, or even datetime. However, the actual value doesn't a specific meaning.
SELECT timestamp, CONVERT(datetime, timestamp), CONVERT(bigint, timestamp)
FROM yourTable
April 28, 2005 at 8:47 am
You use a timestamp field so that changes to a row can be detected......
What is the advantage of this as opposed to using - say - a rowlock ?!?!
**ASCII stupid question, get a stupid ANSI !!!**
April 28, 2005 at 9:13 am
Even though it is not good that EM does not show rowversion, it still does not make it not possible to use it. I think you can enter rowversion yourself, or better yet, use Query Analyzer and write the DDL yourself.
See my article Dealing with changing data for some info on how to use rowversion and what other alternatives there are.
April 28, 2005 at 9:15 am
The difference is of course that the row is not locked for others to view. When you are going to update it with new data you check (or rather let ADO/SQL Server check for you) if the row has changed since you read it. If it has you do not update it and inform the user that the data he based his updates on have changed and he will need to start over.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply