SSMA_TimeStamp field never gets updated

[ions]

Hello MS Access Expert,

I used the SMMA migration tool today. When I insert or update records (both in MS Access or SQL Server) the SSMA_TimeStamp field is never modified. I was curious if it will ever have a data value at some point. If so, when exactly?

Thank you

 

[Pat Hartman]

I think the field doesn't actually contain a date and time. I'm not sure what it contains but it is something unique. The point of this field is to make it easier for the database engine to know if a record has been changed by another task since it was fetched.

 

[AccessBlaster]

It's been a long time but I either recreated the field within SSMS or I changed the field type.

 

[tvanstiphout]

A timestamp / rowversion column is an 8-byte array. You can write code to convert it to something humanly readable, but mostly it works behind the scenes. The value gets updated automatically by SQL Server when the data in the row changes. It can then be used for a concurrency check.

 

[ions]

Thank you for both of your responses.

 

[ions]

Thank you again for both of your responses. I have a follow up question to the SSMA_TimeStamp field.

If I have an updateable View using the SalesOrder and SalesOrderDetail tables should I include the SSMA_TimeStamp field from one of the tables in the view? If so, from which Table?

Thank you

 

[tvanstiphout]

Typically not. The field value is not updated client-side.

 

[Isaac]

I've never used SSMA, preferring always to kind of do my own, but if the goal is to record when records inserted, can't you just use a datetime sql server datatype and set the computed default to current_timestamp

 

[AccessBlaster]

I've never used SSMA, preferring always to kind of do my own, but if the goal is to record when records inserted, can't you just use a datetime sql server datatype and set the computed default to current_timestamp

Most of the time I would export from Access directly into SQL server and use SSMS for housekeeping duties like reconnecting relationships on the BE and updating data type conversions.

I always found the migration tool in SSMS a pain.

 

[Isaac]

If it's something like I imagine -- making too many decisions for you instead of forcing the dev to see that those decisions exist and make them themselves, then that's kinda why I avoided it. I did write some cool code once that looped through every table in an Access database and generated T-SQL DDL that could be directly ran to create all the tables (according to my own preferences of what sql datatype to use from which Access types), and also took into account the max length to rightsize sql columns, etc - boy I loved that, but like so many jobs, I had no way to save it or take it home with me

 

[GPGeorge]

SSMA has been enhanced quite a bit in recent versions. Pre-migration reports, for example, are very informative about what will be moved and how, but more importantly most of the potential problems are flagged for your attention.

Check out this video from last fall.

 

[Isaac]

Interesting, good to know

 

[spaLOGICng]

Hello MS Access Expert,

I used the SMMA migration tool today. When I insert or update records (both in MS Access or SQL Server) the SSMA_TimeStamp field is never modified. I was curious if it will ever have a data value at some point. If so, when exactly?

Thank you

When you view the records, are you looking at them from Access or SSMS? Access does not understand the TIMESTAMP Data Type. It will show as empty from Access.


It is not a Timestamp, as in Date and Time, but is used for Row Versioning. If you have used SSMA to migrate your Data, which I believe you have, you discovered that the Column is created automatically.

Technically it has no purpose to anything we do with data. It is just a function of row versioning and is slated for deprecation. Any time a record is updated, it is technically deleted and inserted. This is evident when working with Table Triggers. I would be too concerned with it. As I said it is to be deprecated. I think its wide use will force it to be supported.

Using it does tend to speed up updates from some applications and I have noticed this with Access with very large tables.

There are times that I have not seen any data in the Column in SSMS, but I never worried about it because it never affected developmental work,

 

[GPGeorge]

Spoiler:

When you view the records, are you looking at them from Access or SSMS? Access does not understand the TIMESTAMP Data Type. It will show as empty from Access.


It is not a Timestamp, as in Date and Time, but is used for Row Versioning. If you have used SSMA to migrate your Data, which I believe you have, you discovered that the Column is created automatically.

Technically it has no purpose to anything we do with data. It is just a function of row versioning and is slated for deprecation.

Spoiler:

Any time a record is updated, it is technically deleted and inserted. This is evident when working with Table Triggers. I would be too concerned with it. As I said it is to be deprecated. I think its wide use will force it to be supported.

Using it does tend to speed up updates from some applications and I have noticed this with Access with very large tables.

There are times that I have not seen any data in the Column in SSMS, but I never worried about it because it never affected developmental work,

The term "TimeStamp" is supposed to be deprecated in favor of "RowVersion", but the function itself remains. The Learn article on Rowversion does appear to be ambiguous, though.

The timestamp syntax is deprecated. This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. [emphasis added]

It refers to the syntax in the first sentence, but then refers to the feature in the next. I take this to mean only the name will be removed, not the underlying Row Version function.

As Tom vS pointed out, its function is to provide a way to quickly identify whether a record has been modified in any way, without having to check each field in the table for variance.

 

[spaLOGICng]

The term "TimeStamp" is supposed to be deprecated in favor of "RowVersion", but the function itself remains. The Learn article on Rowversion does appear to be ambiguous, though.

The timestamp syntax is deprecated. This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. [emphasis added]

It refers to the syntax in the first sentence, but then refers to the feature in the next. I take this to mean only the name will be removed, not the underlying Row Version function.

As Tom vS pointed out, its function is to provide a way to quickly identify whether a record has been modified in any way, without having to check each field in the table for variance.

Hi George,


The Timestamp Column might be deprecated but is still in use. You can create a Timestamp Column, but you cannot apply a RowVersion Type. While it may by synonymous, only the Timestamp Data Type can be applied.

Yes, as Tom pointed out, but I feel we basically said the same thing in different ways.

We, as developers, will never refer to it, but it is still in use today, as I write this. One of the reasons I still tend to use it is because it has an apparent increase in performance when requesting record and updating records. I am able to appreciate this more on very large tables when being updated.

 

[GPGeorge]

That is a direct quote from the Microsoft document itself. The one I linked to in my post. It literally says "the SYNTAX is deprecated" and will be removed in a future version of SQL Server. I went on to add that it's ambiguous, which this discussion seems to confirm.

It is very confusing, as a matter of fact, that Timestamp is supposed to be the deprecated term, but the GUI in SSMS doesn't allow you to use Rowversion in Design view of a table; it only supports Timestamp, at least in the local SS instance I have and in the SQL Azure database I checked.

On the other hand, this Alter Table statement does add a new field using the Rowversion syntax.

Alter Table tblTest
Add  Access_TS Rowversion

However that new field then appears in the table as Timestamp.

 

[spaLOGICng]

That is a direct quote from the Microsoft document itself. The one I linked to in my post. It literally says "the SYNTAX is deprecated" and will be removed in a future version of SQL Server. I went on to add that it's ambiguous, which this discussion seems to confirm.

It is very confusing, as a matter of fact, that Timestamp is supposed to be the deprecated term, but the GUI in SSMS doesn't allow you to use Rowversion in Design view of a table; it only supports Timestamp, at least in the local SS instance I have and in the SQL Azure database I checked.

On the other hand, this Alter Table statement does add a new field using the Rowversion syntax.

Alter Table tblTest
Add  Access_TS Rowversion

However that new field then appears in the table as Timestamp.

Ah, ok, now I understand the ambiguity.

And when i stated that it cannot be applied as a data type, I meant from the SSMS Table Designer. When you do add it from a Query Window, as you have illustrated, it will display Timestamp on the Column in the Designer.

ROWVERSION pre-dated Timestamp and that is the synonymity, for compatibility for future versions. ROWVERSION was added in v2000 and TIMESTAMP in 2008. ROWVERSION is deprecated and TIMESTAMP is its replacement, I think since v2005. It was recommended to use ROWVERSION over TIMESTAMP through 2008, but since then any time a ROWVERSION column was added in later versions, it is converted to TIMESTAMP.

 

[GPGeorge]

"ROWVERSION is deprecated and TIMESTAMP is its replacement, I think since v2005."

Can you provide the link documenting that Rowversion is the one deprecated? It's counter to everything I've read on the topic.
For example:

rowversion (Transact-SQL) - SQL Server

rowversion (Transact-SQL)

learn.microsoft.com

rowversion (Transact-SQL)​

• Article
• 02/01/2023

And in the body of that article:

Note

The timestamp syntax is deprecated. This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

 

[spaLOGICng]

"ROWVERSION is deprecated and TIMESTAMP is its replacement, I think since v2005."

Can you provide the link documenting that Rowversion is the one deprecated? It's counter to everything I've read on the topic.
For example:

rowversion (Transact-SQL) - SQL Server

rowversion (Transact-SQL)

learn.microsoft.com

rowversion (Transact-SQL)​

• Article
• 02/01/2023

And in the body of that article:

I have been reading that the datatype would be deprecated as early as 2009. The deprecation as you have indicated is in syntax, as you have indicated, not the underlying mechanism. Even though they advise to stop using it altogether, I cannot see MS stopping support in the mechanism. the impact upon current running databases would be too laborious, and I think MS would catch a lot of flak behind if it was to not be supported in future versions.

Notwithstanding, in syntax, both can be used to this day. where ROWVERSION is converted to TIMESTAMP.

Everything we are talking about here is semantics. I will believe it when I see it. It might never happen unto 2050 and I will be dead and gone. I will also continue (enjoying) driving ICE vehicles until that day, as well.

 

[Pat Hartman]

SSMA has been enhanced quite a bit in recent versions.

I really don't like that it doesn't transfer the hidden indexes Access adds for foreign keys. If that is deliberate, they should warn us. If it is a bug, they should fix it.