TImeStamp

Question

TImeStamp

usharani.t

Ten Centuries

Points: 1033
More actions
May 16, 2011 at 5:16 am

#250323

How to insert value for TIMESTAMP datatype?
When Am inserting value for TIMESTAMP i got Error like this
"Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column"

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Timestamp is a row version. It is all maintained automatically by the server.

If for some reason you need to keep an exact copy of the value in an audit table, then change the timestamp to binary(8) in the destination table and the insert will work.

... and if you want a date then use the datetime or date datatypes.

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 2

And, just to say it out loud... Like Remi said, the TIMESTAMP datatype is for row versioning. It has absolutely nothing to do with a date or time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Jeff Moden (5/16/2011)
And, just to say it out loud... Like Remi said, the TIMESTAMP datatype is for row versioning. It has absolutely nothing to do with a date or time.

Has it been reported as a bug on connect??? I don't really see how easy or hard a fix this could be but it would certainly be less confusing to people.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

ah .. but have you tried to use rowversion?

http://sqlblogcasts.com/blogs/grumpyolddba/archive/2010/09/30/rowversion-vs-timestamp-what-s-the-word-then-microsoft.aspx

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

colin.Leversuch-Roberts (5/16/2011)
ah .. but have you tried to use rowversion?
http://sqlblogcasts.com/blogs/grumpyolddba/archive/2010/09/30/rowversion-vs-timestamp-what-s-the-word-then-microsoft.aspx

:w00t::hehe::-D

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 6

colin.Leversuch-Roberts (5/16/2011)
ah .. but have you tried to use rowversion?
http://sqlblogcasts.com/blogs/grumpyolddba/archive/2010/09/30/rowversion-vs-timestamp-what-s-the-word-then-microsoft.aspx

Wow, that's...

I couldn't quite believe it, so I tried it myself and sure enough it converts to timestamp!

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

rowversion = timestamp, just different names for the same data type. One is just the alias of the other. Timestamp is deprecated, but SSMS still uses it (go figure), but SSMs uses way too much crap code and deprecated features in general. (it still uses TEXT in places)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

GilaMonster (5/16/2011)
rowversion = timestamp, just different names for the same data type. Timestamp is deprecated, but SSMS still uses it (go figure)

Does MS know about this too??? Is there a list of bugs like this I could get acquinted with??

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 9

GilaMonster (5/16/2011)
rowversion = timestamp, just different names for the same data type. One is just the alias of the other. Timestamp is deprecated, but SSMS still uses it (go figure), but SSMs uses way too much crap code and deprecated features in general. (it still uses TEXT in places)

There is a minor difference in how you declare the column, but other than that, sure. I'm just surprised that when you do a Create Table Script on a table created with rowversion, the Create Table now says timestamp.

Are there tools out there that can use and see it as rowversion?

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Ninja's_RGR'us (5/16/2011)
GilaMonster (5/16/2011)
rowversion = timestamp, just different names for the same data type. Timestamp is deprecated, but SSMS still uses it (go figure)
Does MS know about this too??? Is there a list of bugs like this I could get acquinted with??

There have been enough people whining about SSMS and it's crap code I'm sure they do.

Try connect (search it with google), maybe

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

GilaMonster (5/16/2011)
Ninja's_RGR'us (5/16/2011)
GilaMonster (5/16/2011)
rowversion = timestamp, just different names for the same data type. Timestamp is deprecated, but SSMS still uses it (go figure)
Does MS know about this too??? Is there a list of bugs like this I could get acquinted with??
There have been enough people whining about SSMS and it's crap code I'm sure they do.
Try connect (search it with google), maybe

Ok, don't have that much time to waste. I was hoping for a top 25 annoyance that ms is not fixing... or anything like that.

Tx anyways.