We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


RowStamp on 2 tables do not match

itascent asked
Medium Priority
Last Modified: 2008-01-09
I have a Maximo application that has a SQL backend and I restored a table that contained a rowstamp from a previous day. Now, none of the screens that reference that table will work correctly and say that another user is updating the table.

Here is what the makers of the software have to say. I ran update stats and rebuild indexes on all tables.

MAXIMO uses optimistic locking to ensure that two users cannot update the same record without being aware of each other's updates.  Each MAXIMO table has a column named ROWSTAMP which is set to a TIMESTAMP datatype on SQL Server or is populated with the next value of the MAXSEQ sequence by a trigger on Oracle.   This ensures that when a record is updated the value of ROWSTAMP changes.  
When MAXIMO updates a record, it references both the table's primary key(s) and the rowstamp value in the where clause.  Since the rowstamp value changes each time a record is updated, this mechanism ensures that an update will fail if the record has been modified since it was first queried.  If this occurs, MAXIMO sees that there were zero rows updated by the SQL statement and displays the "Record has been updated by another user" message.   The user must re-query the record in order for MAXIMO to get the updated value of rowstamp, as well as any other changes.

Since MAXIMO always interprets the status "Zero rows updated" to mean that the rowstamp has changed, you may see the "Record has been updated by another user" message when no other user is involved.
Watch Question

OK, I think this is unclear but substantially correct. Optimistic locking is what SQL Server does. It is not good enough, so Maximo essentially forces pessimistic locking through this mechanism. But this mechanism as described only affects current operations.  

So first, see if you can get MAximo on the phone. they will have some idea of how to proceed.

Short of that, you can run the profiler to see what the application is actually doing. My guess is that you will see what it is doing and why it is failing.

I am not familiar with what happens when you restore a timestamp column from a backup. When you say restore, do you mean SQL Restore or some manual process? If the latter, you would probably end up creating new timestamps . It is possible that even a SQL restore does this. Not a lot of people in SQL Land use timestamp, which is not readily readable as a date.

I think the profiler is going to be your best bet to figure this out


Good tip. I came up with this statement

update poline set service = 'Y' , receiptreqd = 'N'  where orgid = 'ASCENT'  and polineid = 2067533353  and rowstamp = null

It has something to do with rowstamp. If I do the following query:

SELECT     *
FROM         poline
WHERE     (polineid = '2067533353')

It shows me the line

If I run this update statement, it says zero rows affected:

update poline set service = 'Y' , receiptreqd = 'N'  where orgid = 'ASCENT'  and polineid = 2067533353  and rowstamp = null

If I run this line:

update poline set rowstamp = null  where orgid = 'ASCENT'  and polineid = 2067533353  

It updates that to null, i guess it says 1 row affected

I run the second line again and it doesn’t update, it says zero rows affected.

If I take off the rowstamp = null on the selection criteria, it works

update poline set service = 'Y' , receiptreqd = 'N'  where orgid = 'ASCENT'  and polineid = 2067533353  

But the application is written that way, I just messed up the rowstamps somehow, but how do I fix it if I can't set the rowstamp to null and what is the perm fix?

Hmm... my guess is that the application is getting a null where it should get a value, since i believe a timestamp is, by definition, never null.

this is really weird though - you should not have been able to update the rowstamp to null if it is actually a timestamp.  I get this message...

Msg 272, Level 16, State 1, Line 1
Cannot update a timestamp column.

are the values in the rowstamp column hex or datetimes?  

(I may head to bed shortly ... i will be back online by 8am tomorrow eastern US time)


It says <binary>

Reading below, I'm not sure why the rowstamp even matters on what it is. And why does it see that its not null.

MAXIMO creates a column called ROWSTAMP for Oracle and Microsoft SQL Server databases.

In Microsoft SQL Server, the ROWSTAMP column is defined as a timestamp column.  Whenever a row is inserted or updated, the ROWSTAMP column is modified internally by SQL Server.  Note that the timestamp value is a unique value, but it is not a true date/timestamp.

The ROWSTAMP is used to ensure that a user does not write over a record which has been modified by another user.  This can happen if two users query a record, one user changes and saves the record, and the second user attempts to save the record.  It is important to stop the second user from writing over (losing) changes that the first user has made.  

MAXIMO updates the table based on the ROWSTAMP.  For example, when updating the companies table, the following SQL statement is generated by MAXIMO to ensure that the ROWSTAMP column has not changed since the time the record was first queried:

       update companies
       set company='test', name='newtest',
       changeby='MAXIMO', changedate='2005-02-09-'
       where company = 'test' and rowstamp= '000000000000D4C3'

If the ROWSTAMP has changed, the user will be informed that the record has changed and the update does not complete.

When inserting into MAXIMO tables using outside interfaces such as Excel or Access, the ROWSTAMP column will be populated automatically.  If you are inserting using  SQR or another SQL interface, the insert should ignore the ROWSTAMP column.  

For example, the altitem table has three columns: itemnum,  altitemnum, rowstamp.  When performing the insert, the statement should be coded as follows:

       insert into altitem (itemnum, altitemnum) values ('ITEM1', 'ALTITEM1');

The rowstamp column will be populated automatically by the server.


Lets have a table which have col1 int and col2 as timestamp.

now for inserting data u have to write

insert into table values (1, null)

now if u select * from table, u will see col2 has a hexa value which is a time stamp put by the sql server.

so if u search for select * from table where col2 is null, u wont get any results.

Adding to the above points

In a CREATE TABLE or ALTER TABLE statement, you do not have to supply a column name for the timestamp data type:

CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp)

If you do not supply a column name, SQL Server generates a column name of timestamp. The rowversion data type synonym does not follow this behavior. You must supply a column name when you specify rowversion.

A table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated. This property makes a timestamp column a poor candidate for keys, especially primary keys. Any update made to the row changes the timestamp value, thereby changing the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index.

A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.