Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MC01
Starting Member
4 Posts |
 Posted - 2012-03-01 : 12:59:38
|
I recently moved an Access database to SQL Server 2008 Express with great success. My web application works much better now, and a number of my problems went away.In the process of migrating, I used the SSMA migration assistant. In many of my tables, it automatically made the SSMA_TimeStamp columns. This column contains a number in some odd format that updates whenever ANYTHING in that record is updated. Update the row, the number goes up to the next number.I'm working on a project where I need to get records from that table remotely. I created a simple web page that goes to the server via AJAX, gets a record from the database, and shoves it in a local database here. What I want to do is to record what the SSMA_TimeStamp number is for the entry I get. Then, when I go to get the next entry, I can just say:SELECT * FROM tablename WHERE SSMA_TimeStamp > 0x00000005D3652 ORDER BY SSMA_TimeStamp ASC" When I run an SQL statement like this, it works great. I can get records that are newer than a given timestamp number.The problem is that I want to record these numbers locally. Unlike the other fields, I can't seem to write this number. Calling ars("SSMA_TimeStamp") gives an invalid type error. Troubleshooting the thing only revealed that whatever it is, it's only 4 characters long:len(rs("SSMA_TimeStamp")) was equal to 4Trying to see what the heck it is by printing out ascii codes, it's null-null-?-?.How can I record this value locally?(Yes, I understand that this might be a goofy way of replicating database entries...) |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-03-01 : 13:15:33
|
| I can't tell from that you wrote, but did you create a table with a TIMESTAMP column (also know an ROWVERSION)? If so, you cannot update a TIMESTAMP column. If you just want to copy the timestamp from one table over to another then you need to make the destiantion column a BINARY(8) if the timestamp is nonnullable or VARBINARY(8) if the timestamp is nullable.Here is a link to BOL (Books Onine) that describes TIMESTAMP/ROWVERSION in greater detail:http://msdn.microsoft.com/en-us/library/ms182776.aspx |
 |
|
|
MC01
Starting Member
4 Posts |
Posted - 2012-03-01 : 13:22:32
|
At this point, I haven't created anywhere in a database where I'm storing the value. I'm just trying to display it on-screen in a web browser, but it fails. I'll try writing it to another table in a BINARY(8) column now. Thanks!Technically, I really want to be able to display this value on screen. My page fetches data from a remote database, and then I write it to a local database here with a form that auto-submits itself. If I can't write the data on screen via:response.write(rs("SSMA_TimeStamp")...then I'll have to alter my strategy a little. |
|
|
|
MC01
Starting Member
4 Posts |
Posted - 2012-03-01 : 17:07:04
|
Lamprey, your answer didn't solve my problem directly, but your mention of BINARY(8) sure sent me on the right path. Thanks for the nudge!I really want to be able to display the data on an ASP page. The trick was in getting the datatype converted to something workable. I made a solution that worked on a table I made with a BINARY(8) copy, but then that failed to work directly on the TIMESTAMP values. My solution:SELECT column1,column2,CONVERT(varchar(18),CONVERT(binary(8),SSMA_TimeStamp),1) AS ssmats FROM tablename WHERE SSMA_TimeStamp > 0x00000000001AAEAA ORDER BY SSMA_TimeStamp ASC Works like a charm! |
|
|
|
|
|
|
|
|
I'd like to use SSMA_TimeStamp