and I'm getting back the number of rows updated so I know the transactions are happening on the server.. Now, yesterday afternoon, two transactions (same query/batch) were lost entirely (NO record written at all) on four out of the five tables..
This is a very simple question, but the SQL Server documentation doesn't explain this as clearly as I would like, so I would appreciate someone spelling it out for me.
I am updating a table with new values based on a spreadsheet - which means I'm using multiple Now, during my research on this I managed to find some documentation on how SQL Server handles this - which is that it uses the UPDATED value. You could also get constraint errors because of how updates are applied (especially on uniqueness) The way to do many updates in one go to avoid intermediate errors is to do a single update Thanks - that's definitely my problem alright.
The other thing that I've noticed is that it's usually one field with the same information that's missing. So, to give an example, I have a temp table with say, 15 columns..
this is the calculated data the user is writing to the DB..
I need to automatically change this to reflect the time in the timezone the db server is in. The dates going into this column are retrieved in batches every so often (5, 10, 15 minutes), so I think the only way to go is to alter the time once it has been received, not to add a Time Modified field or something. Another option here would be to use a calendar table where you map a UTC date and time to the local date and time value.
For example, if they send me PM, but I am in NYC, I would want it to be entered in the db as AM. Disadvantages here are a loss in some of the granularity.
then the second, but one field isn't written and remains NULL. Fourth table, missing the same field info, then the fifth may be missing it too. There is no consistency in which tables may not have that field written to, or when this may occur.
But, the next time the user sends an update to the DB.. Now, I've (in the interim) ran update queries to replace the lost info in the tables.. Nothing has stood out so far in the logs on the SQL server..
ALTER and UPDATE are the two modifying commands of SQL.
ALTER is used to modify the structure of the relations (Tables) in the database.
This would also have to account for differences in Daylight Savings between GMT and wherever the server is, which seems like a nightmare. If seconds are important I would not implement this; you can look at the size of the calendar record and compare it to the size of storing a datetime for every record in your transactional table.