{"id":103,"date":"2009-07-13T23:48:23","date_gmt":"2009-07-13T21:48:23","guid":{"rendered":"http:\/\/www.mysticslayer.com\/?p=103"},"modified":"2009-07-13T23:51:52","modified_gmt":"2009-07-13T21:51:52","slug":"timestamp-and-validation","status":"publish","type":"post","link":"http:\/\/www.mysticslayer.com\/?p=103","title":{"rendered":"Timestamp and validation"},"content":{"rendered":"

Timestamp in SQL Server:<\/p>\n

1) The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.<\/p>\n

2) It further states Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.<\/p>\n

For a customer we made some changes. And the situation is as follows. We exported the data to excel. Multiple tabels are exported. We changed the data in the excel file and the customer validated the data. We also did export the timetamp with the data. Why? Because we had to import it again to the database. If a Timestamp changed, we should not update the data from the excel file. That’s why. <\/p>\n

Problem situation:<\/p>\n

Because a timestamp isn’t a varchar, or numeric value, but a binary(8) value we had to convert it. Because we import the data by SSIS back into the database. But the data we imported is from a flat text file. So every column was read as a DT_STR by SSIS, and timestamp in the stored procedure was a datatype of Timestamp. So we did a comparison of a varchar against a timestamp. Well that situation didn’t work out that well.<\/p>\n

So what we did was to change the parameter of the stored procedure to varchar. Well SQL Server can’t handle a comparison of varchar against timestamp inside the stored procedure. Of course, so we did try it to convert it to a varbinary(8) or binary(8) and then validate it against the timestamp. Well that didn’t work out that well.<\/p>\n

\r\ndeclare @timestamp varbinary(8)\r\nset @timestamp = CONVERT(varbinary(8), '0x0000000001F4AD88')\r\nprint @timestamp\r\n\r\nreturned: 0x3078303030303030\r\n\r\ndeclare @timestamp varbinary(8)\r\nset @timestamp = CONVERT(varbinary(8), 0x0000000001F4AD88)\r\n\r\nprint @timestamp\r\n\r\nreturned: 0x0000000001F4AD88\r\n<\/pre>\n
That comparison didn’t work well. So I had to figure an other way to validate on timestamp. When I almost lost my hope I wrote a function in SQL Server.<\/p>\n
\r\ncreate function [dbo].[sp_hexadecimal] ( @var varbinary(255) )\r\nreturns varchar(255)\r\nas\r\n\r\nbegin\r\n      declare @charval varchar(255)\r\n      declare @i int\r\n      declare @length int\r\n      declare @hex char(16)\r\n\r\n      select @charval = '0x'\r\n      select @i = 1\r\n      select @length = datalength(@var)\r\n      select @hex = '0123456789abcdef'\r\n\r\n      while (@i <= @length)\r\n      begin\r\n            declare @tempint int\r\n            declare @firstint int\r\n            declare @secondint int\r\n\r\n            select @tempint = convert(int, substring(@var,@i,1))\r\n            select @firstint = floor(@tempint\/16)\r\n            select @secondint = @tempint - (@firstint*16)\r\n            select @charval = @charval +\r\n            substring(@hex, @firstint+1, 1) +\r\n            substring(@hex, @secondint+1, 1)\r\n\r\n            select @i = @i + 1\r\n      end\r\n\r\nreturn ( @charval )\r\nend\r\n<\/pre>\n
So what I did was the other. I managed to validate the data on the other way.<\/p>\n
\"sphexadecimal1\"
\nit did the trick...<\/p>\n","protected":false},"excerpt":{"rendered":"
Timestamp in SQL Server: 1) The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms. 2) It […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[101,16,197],"tags":[201,20,206,203,347,204,209,205,208,207],"class_list":["post-103","post","type-post","status-publish","format-standard","hentry","category-programming","category-sql-server","category-ssis","tag-sql","tag-sql-2000","tag-sql-2005","tag-sql-2008","tag-sql-server","tag-timestamp","tag-timestamp-varchar","tag-validation","tag-varchar-to-binary","tag-varchar-to-timestamp"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/pe1EH-1F","_links":{"self":[{"href":"http:\/\/www.mysticslayer.com\/index.php?rest_route=\/wp\/v2\/posts\/103","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mysticslayer.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mysticslayer.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mysticslayer.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mysticslayer.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=103"}],"version-history":[{"count":4,"href":"http:\/\/www.mysticslayer.com\/index.php?rest_route=\/wp\/v2\/posts\/103\/revisions"}],"predecessor-version":[{"id":109,"href":"http:\/\/www.mysticslayer.com\/index.php?rest_route=\/wp\/v2\/posts\/103\/revisions\/109"}],"wp:attachment":[{"href":"http:\/\/www.mysticslayer.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=103"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mysticslayer.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=103"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mysticslayer.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=103"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}