Discouraged About Timestamps
Mar 30, 2007
I have read the forums and fail to be encouraged about using the timestamp to handle concurrency. I have SQL 2005 and am using the DataReader and command objects for data manipulation. The database has about a dozen related tables to each other in some way. Correct me if I am wrong, but using timestamps means I must store each table's original rowversion at read time in several possible combinations of variables (based on the joined tables at the time of read). Is this right? Then at update time, what if some related tables are timestamped with the original value and others have changed since read time? How would each update stored procedure know which other dependent tables were fetched at read time? It's easy to corrupt the data this way.
SELECT s.OrderNumber, a.UserName AS OrderedBy, cn.LastName + ', ' + cn.FirstName AS ContactName, s.CarbonCopy, s.Application, s.OrderDate,
s.FollowUpDate, s.ProdStartDate, s.InternalNote, s.SampleNote, s.UpdatedBy, s.DateLastUpdated, cn.EMail AS ContactEmail, s.MfgID,
co.CompanyName, s.ShipVia, cn2.LastName + ', ' + cn2.FirstName AS ContactName2, co2.CompanyName AS Expr1,
cn3.LastName + ', ' + cn3.FirstName AS ContactName3, s.MfgContactID, s.DistributorID, s.DistContactID, s.CustomerContactID,
s.VersionStamp, a.VersionStamp, cn.VersionStamp, co.VersionStamp, cn3.VersionStamp <------- Is all this really necessary?
FROM Samples AS s LEFT OUTER JOIN
Associates AS a ON s.OrderedByID = a.AssociateID LEFT OUTER JOIN
Contacts AS cn ON s.CustomerContactID = cn.ContactID INNER JOIN
Companies AS co ON s.MfgID = co.CompanyID LEFT OUTER JOIN
Contacts AS cn3 ON s.DistContactID = cn3.ContactID LEFT OUTER JOIN
Contacts AS cn2 ON s.MfgContactID = cn2.ContactID LEFT OUTER JOIN
Companies AS co2 ON s.DistributorID = co2.CompanyID
WHERE (s.SampleID = @SampleID)
What would be a simple way to update a table that depends on other tables having original version stamps? Then at runtime how would I enforce it without generating violations when the users were updating, say, the Samples table and just viewing the dependent tables' columns, not changing them.
View 4 Replies
Nov 10, 2000
When i try to load tables from DB2 OS390 Via DTS and DB2 ODBC it
give me an error with the timestamp field. Its (DTS) says the timestamp
field on SQL is marked read only.
Is there anyway around this problem?
View 1 Replies
View Related
Oct 5, 2007
Hello!
I think I am missing some essential idea when working with a timestamp column, maybe someone around here can give me the needed pointers on how-to...
My situation is: I want to copy data periodically from one DB to another. Both are in the same SQL instance, so there are no transaction, networking or similar issues involved. It all really comes down to identifing the new rows in source and moving them over to destination.
I added a TIMESTAMP column to my source, and I can see it count up slowly for every line inserted. On the destination side, I added a table containig just one field, type binary(8) to store the last value to which the last transfer ran.
Notice that on source side I got the counter on every row, but on destination side I got only one value, I thought it would be waste to carry over the data for every row when I really only need the latest one.
Now, what my package does is:
a) Select the last used binary(8) value into a package variable named TS of type object (works)
b) Start a dataflow, where the source is SELECT statement and the WHERE clause is TIMESTAMP > package variable TS (works)
c) Multicast the data into two recordsets
d) One recordset makes most of the columns flow through some Lookups, Derive Columns into a Destination and are written back. (works)
e) The other exit of the Multicast shall go through all lines and catch the highest TIMESTAMP that came by using an Aggregate and write it down, since that is the point I need to pick up later. (and that is the problem)
Problems are: I cannot run Aggregate max on the TIMESTAMP. Only Count is allowed.
So I need to convert the BINARY(8) into a number to be able to catch the max value. Converting between BINARY(8) and DT_I8 or similar seems not possible, or the result is wrong due to the byte ordering (MSB/LSB, most/less significant byte first)
Later on, I need to write out my new found highest value again, but here the same problem applies. How do I convert from DT_I8 back to BINARY(8)?
Having my own reference value stored as bigint instead of binary(8) does not work either: You cannot assign bigint from a select to a package variable, bigint are loaded as string (as I read in a blog, and I think they are right since I get exactly the same errors).
So...
HOW does one work in an efficient way with a TIMESTAMP column to Aggregate it to max and store out this max value?
Or do you all keep the TIMESTAMP from source appended to your rows and stored with each row in destination (Wasting eight precious bytes a row)?
Thanks for your comments!
Ralf
View 3 Replies
View Related
Jan 19, 2005
Hi Everyone....
Crazy one here....
I need to populate a table with all the times that
are available in a 24 hour period, down to the 5 minute
interval.
So the table should look like....
id ds (datetime stamp)
--- --------------------------
0 1/1/2005 00:00:00
1 1/1/2005 00:05:00
2 1/1/2005 00:10:00
3 1/1/2005 00:15:00
.........
xx 1/1/2005 23:55:00
Please advise on a way to accomplish this in a script....
thanks
tony
View 14 Replies
View Related
Mar 18, 2008
I'm trying to filter out timestamps that land exactly at .000 milliseconds. (e.g. 2007-12-05 16:30:50.000) Do I have to convert the timestamp to a string first and then use the LIKE statement? If so, can somebody show me how. I'm pretty green to SQL but know the basics. Any help would be greatly appreciated!
View 2 Replies
View Related
Jul 20, 2005
I have an Access XP ADE application connected to a SQL Server 7.0 SP4database. I have created a timestamp column in the main table.Unfortunately, I am now getting persistent write conflict errors.The order of operations are:1. The application starts and loads the recordset into the form using astored procedure.2. I modify a field and press a save button which uses me.dirty=false toforce a save.3. The field is saved to the database. Using profiler I can observe themodified field being saved. As I would expect, the update statement isusing the primary key and the timestamp column value. For the sake of thisdiscussion let's assume the value of the timestamp is 5ad9.4. Without navigating off the record, I alter the same field (or adifferent field) and press save again and a write conflict will appear.Using profiler I can see the update statement that is attempting to updatethe record. The update statement is using the previous value (5ad9) of thetimestamp column.I thought that the timestamp column value is incremented each time therecord is updated. The ADE application does not appear to be recognizingthe new timestamp value.Any help or advice you could give would be appreciated.ThanksGeorge
View 3 Replies
View Related
Mar 19, 2007
Facundo writes "Hi:
How can i add a timestamp to a generated file of a complete database backup, the idea is to archive full database backups using the date to identified it.
Thanks a lot.
FB"
View 1 Replies
View Related
Apr 1, 2008
How do you add a specific timestamp to a backup? For example, if the backups are going to the same drive location on disk and you want to retain 3 days worth of backups online, how do you add the timestamp to the filename to make each backup unique?
F:MSSQL.1MSSQLBackup and your user database is <xyz>_<timestamp>.bak
The user database dumps each night at 9 PM.
You want to keep 3 days of online backups.
View 9 Replies
View Related
Nov 27, 2006
Greetings once again SSIS friends,
I have some source tables which contain timestamp fields (that's timestamp data type not datetime). My dimension table holds the maximum timestamp value as a varbinary(8).
I want my package to have a variable that holds that value but I don't know which data type to use for this. The reason for this is because I want to use that variable to then retrieve all records from my source table that have a timestamp value greater than the value stored in the variable.
Please advise on what data type is suitable.
Thanks for your help in advance.
View 2 Replies
View Related