15

I rencently used the SQL Server Migration Assistant to import a database into SQL Server 2005. I noticed that a number of tables that were imported have been ammended with a new column called SSMA_timestamp.

Can anyone tell me what this is for and how it would be used?

Jon Seigel
  • 12,251
  • 8
  • 58
  • 92
Chris
  • 6,272
  • 9
  • 35
  • 57

4 Answers4

26

The added SSMA_timestamp columns are not only used during migration. They actually help avoid errors when Access updates records in tables linked to SQL Server. So if you are still using an Access front end linked to the migrated SQL Server database, it would be best to not drop the SSMA_timestamp columns.

From the MSDN article Optimizing Microsoft Office Access Applications Linked to SQL Server:

Supporting Concurrency Checks

Probably the leading cause of updatability problems in Office Access–linked tables is that Office Access is unable to verify whether data on the server matches what was last retrieved by the dynaset being updated. If Office Access cannot perform this verification, it assumes that the server row has been modified or deleted by another user and it aborts the update.

There are several types of data that Office Access is unable to check reliably for matching values. These include large object types, such as text, ntext, image, and the varchar(max), nvarchar(max), and varbinary(max) types introduced in SQL Server 2005. In addition, floating-point numeric types, such as real and float, are subject to rounding issues that can make comparisons imprecise, resulting in cancelled updates when the values haven't really changed. Office Access also has trouble updating tables containing bit columns that do not have a default value and that contain null values.

A quick and easy way to remedy these problems is to add a timestamp column to the table on SQL Server. The data in a timestamp column is completely unrelated to the date or time. Instead, it is a binary value that is guaranteed to be unique across the database and to increase automatically every time a new value is assigned to any column in the table. The ANSI standard term for this type of column is rowversion. This term is supported in SQL Server.

Office Access automatically detects when a table contains this type of column and uses it in the WHERE clause of all UPDATE and DELETE statements affecting that table. This is more efficient than verifying that all the other columns still have the same values they had when the dynaset was last refreshed.

The SQL Server Migration Assistant for Office Access automatically adds a column named SSMA_TimeStamp to any tables containing data types that could affect updatability.

Community
  • 1
  • 1
Simon Elms
  • 17,832
  • 21
  • 87
  • 103
  • 1
    Instead of posting links to some forum on some site that may disappear tomorrow, why not post the actual relevant part of the information here, so this answer will be useful to people regardless of what happens on those other sites? – Aaron Bertrand Oct 17 '12 at 22:01
  • 3
    @Aaron: Good point. I've edited my reply as per your suggestion. Thanks. – Simon Elms Oct 17 '12 at 22:25
14

I think this is generated so that the Migration assistant can detect changes to the data during the migration.

Unless you are continuing to use Access as a front end to this specific database you have migrated to SQL Server (in which case see Simon's answer), I don't think they will be used for anything after migration is complete, so it should be safe to drop these new columns once you are sure everything is done.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Please note that even though this reply has been flagged as the answer it is not correct (see my reply below that links to a detailed explanation of what the SSMA_timestamp columns are used for). – Simon Elms Oct 17 '12 at 21:53
  • @SimonTewsi so my three-year old answer to this three-year old question isn't correct in the single case where you continue to use Access as a front end, which the OP has not specified he is doing, and you down-vote me? Good community spirit. – Aaron Bertrand Oct 17 '12 at 21:57
  • 3
    @Aaron: No, I wasn't downvoting you because your answer was correct in general, and incorrect in one specific instance. I was downvoting it because it was plain incorrect. The SSMS_timestamp column is not used for migration and it can't be safely dropped once the migration is complete in all cases. I think it's actually good community spirit to downvote incorrect answers to ensure people looking for answers (even those coming from Google 3 years later, like me) don't think incorrect information is correct. I don't make a habit of downvoting and I'm sorry if you're offended in this instance. – Simon Elms Oct 17 '12 at 22:18
  • 2
    @Aaron: I've removed my downvote based on your update. For me the important distinction is that dropping SSMA_timestamp columns is unsafe if continuing to use an Access front-end, but ok if Access is no longer going to be used. – Simon Elms Oct 17 '12 at 22:35
0
  <!-- Set project preference.
         Preference path/name/value can be found in preferences.prefs file stored in SSMA project directory.
         Preference path is the node name path starting from root to leaf node separating by "/". -->
    <set-project-preference preference-path="prefs/ssma-for-access/a2ss/conversion"
                                preference-name="timestamp-columns-opt"
                                preference-value="never" />
Andrew
  • 784
  • 9
  • 15
0

From SSMA GUI you can also click tools--> default project setting --> conversion --> Tables --> add timestamp columns --> set to Never

Andrew
  • 784
  • 9
  • 15