asked on

TimeStamp values appearing as NULL

Hello and thanks for taking time to read this.

I've got a table with 1500 rows in it. With enterprise manager I added a [TimeStamp] column. The values for this column all appeared as NULL. I've tried making an update to the rows to get the TimeStamp value to change from null to an actual timestamp, but they stay NULL. Also, If I insert a new row the value for that row's timestamp is NULL.

Apparently I don't understand how TimeStamp works. Can someone please clue me on what's going on? Thanks for your time.

Scott

obahat

Timestamp is a binary column that is typically used for various internal SQL server needs. If you need to capture the date and time, please use DATETIME OR SMALLDATETIME data type instead.

You can learn more about TIMESTAMP variables in books online (simply look at Query Analyzer help, under timestamp).
Basically, the value in the timestamp column is (typically) assigned automatically by SQL server.

SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sneidig

ASKER

Thanks, for answering, I do actually need the TimeStamp column for a reason. It's to implement incremental full-text indexing.

I figured out a way to address part of the problem by exporting the data, then deleting all the rows, and then importing the data. That creates not null values for that column in each row.

However when I go to insert a new row I get this message in query analyzer:
Cannot insert the value NULL into column 'Timestamp', table 'Adobe_Photographers_Directory.dbo.pd_photographers'; column does not allow nulls. INSERT fails.
The statement has been terminated.

That's where I'm at so far.

ASKER CERTIFIED SOLUTION

obahat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sneidig

ASKER

Thanks for your help guys. It turns out that between me and the person I'm working on this, we didn't add the column correctly in enterprise manager. (i'm embarresed and blushing if you can't see). Instead of just typing in the name [TimeStamp] in design view, you also have to select TimeStamp as the datatype from the drop down menu. It's a little confusing because as soon as you name the column [TimeStamp], the datatype automaticly gets set to binary, so we assumed that was the correct datatype.

I'm going to split up the points since you guys rushed to help. Also, I just have to say RaFrancisco is THE MAN or WOMAN on this web site.

Scott

sneidig

ASKER

I'll add that obahat offers some interesting information about the binary datatype. Thank you.

rafrancisco

sneidig, thanks for the compliments. There are a lot of great experts in this Topic Area and I am just playing catch up with them. Glad that I was able to help.