April 18, 2012 at 1:34 pm
fully understand it has nothing to do with time, but it does indicate the order rows were modifed in the table.
April 18, 2012 at 1:41 pm
the Max(timestamp) is to get the timestamp value of the row modifed in the table.
[LastBarcodeTimeStamp] is Binary(8) because MS said the a timestamp converted to a Binary(8) field
RowCounter is just for a PK to get to the record
April 18, 2012 at 1:43 pm
Ok, but there is no WHERE in your SELECT. So you are always getting the MAX from the entire table.
Jared
CE - Microsoft
April 18, 2012 at 1:44 pm
charles-1011021 (4/18/2012)
fully understand it has nothing to do with time, but it does indicate the order rows were modifed in the table.
Not really true or an accurate way to do that. You can get duplicates if you insert from a table that has a rowversion column. And in your code you are trying to update a timestamp which is not allowed. You simply cannot do what you are trying to do. The only way it would work would be to delete the row, then insert it with the new timestamp data. It all seems like WAY too much effort.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2012 at 1:48 pm
Sean Lange (4/18/2012)
charles-1011021 (4/18/2012)
fully understand it has nothing to do with time, but it does indicate the order rows were modifed in the table.Not really true or an accurate way to do that. You can get duplicates if you insert from a table that has a rowversion column. And in your code you are trying to update a timestamp which is not allowed. You simply cannot do what you are trying to do. The only way it would work would be to delete the row, then insert it with the new timestamp data. It all seems like WAY too much effort.
Sean... He's trying to update a BINARY(8) column.
SET [LastBarcodeTimeStamp]
😎
Jared
CE - Microsoft
April 18, 2012 at 1:50 pm
charles-1011021 (4/18/2012)
the Max(timestamp) is to get the timestamp value of the row modifed in the table.[LastBarcodeTimeStamp] is Binary(8) because MS said the a timestamp converted to a Binary(8) field
RowCounter is just for a PK to get to the record
Ok, your updating the record where rowcounter = 1 with the max(timestamp) from the other table? NOTE: not the MAX(timestamp) where rowcounter =1... The MAX(timestamp) from the table.
The record being UPDATED is what the RowCounter = 1 is referring to.
Jared
CE - Microsoft
April 18, 2012 at 1:50 pm
SQLKnowItAll (4/18/2012)
Sean Lange (4/18/2012)
charles-1011021 (4/18/2012)
fully understand it has nothing to do with time, but it does indicate the order rows were modifed in the table.Not really true or an accurate way to do that. You can get duplicates if you insert from a table that has a rowversion column. And in your code you are trying to update a timestamp which is not allowed. You simply cannot do what you are trying to do. The only way it would work would be to delete the row, then insert it with the new timestamp data. It all seems like WAY too much effort.
Sean... He's trying to update a VARBINARY(8) column.
SET [LastBarcodeTimeStamp]😎
Doh!!! Missed that a couple posts up where he said that.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2012 at 3:34 pm
charles-1011021 (4/18/2012)
I am trying to save a timestamp to a Binary(8) column and converting it to Binary(8), with no luck updating the table where I am tring to save it. This is an example of what I am trying// [LastBarcodeTimeStamp] is a Binary(8) datatype
UPDATE [TS].[dbo].[MPPS Data Vault]
SET [LastBarcodeTimeStamp] = (SELECT CONVERT(varbinary(max), MAX(timestamp), 1) as TS From [TS].[dbo].[Barcodes])
WHERE RowCounter = 1
This returns 1 record affected
******************************************************************************
If I run Just this:
SELECT CONVERT(varbinary(max), MAX(timestamp), 1) as TS From [TS].[dbo].[Barcodes]
it returns: 0x0000000016C1ADC3
As is should
Any ideas?
Without you giving more details about the unexpected outcome (as you say it is updating a row) why are you converting to Varbinary(max) instead of binary(8)?
// [LastBarcodeTimeStamp] is a Binary(8) datatype
UPDATE [TS].[dbo].[MPPS Data Vault]
SET [LastBarcodeTimeStamp] = (SELECT CONVERT(binary(8), MAX(timestamp), 1) as TS From [TS].[dbo].[Barcodes])
WHERE RowCounter = 1
Fitz
April 19, 2012 at 8:55 am
I want to thank everyone for there help.:-)
Using CAST not convert and Inserting into a varbinary(8) not binary(8) made it work.
It is working will. By saving the max timestamp, the next time I do a select on records with a timestamp > than the value I saved, I know the records that have changed.
Thanks again all
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply