Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Background:

I am programming a database in Ms-Access and using SQL-server as the Back-end. But since my database will be a multi-user platform I need to make sure that whenever a user is posting data into the back-end that their computer time is the same as the SQL-server time to ensure that record locking is being effectively used and the data is being timestamped properly.

Extra gibberish:

I only had a major concern for this when I saw a user in my company with his computer time set in the future!!! He was exactly 12 years ahead of us but had no idea! Hence this I have been searching for a solution to keep everyone on the same page.

Question:

Is it possible to write a script that will push the SQL-server time out on to networked client computers?

share|improve this question
2  
Are you saying that the timestamp value is sent by the user to the SQL Server? Why don't you just keep the time-stamping on the server end, and for e.g. default the timestamp column to getdate()? –  RoKa Apr 18 '13 at 8:54
2  
Force time synchronization in your domain. –  spaghettidba Apr 18 '13 at 9:49
add comment

2 Answers 2

up vote 3 down vote accepted

Unfortunately, you're trying to solve a problem with an inherently broken design: relying on a user-editable client-side variable to determine object version is never going to work perfectly.

Forcing time synchronization on the domain may help, but clients can still be out-of-sync, either because of clock drift, or because the user is a local admin and therefore has permissions to change the clock manually at any time, to any time.

Normally a server-side value such as an ever-increasing integer row version number is used for concurrency control instead of a client-side-generated value.

Also, if you're recording auditing information, as much of it as possible (particularly time values) should be generated by the server for both security and synchronization reasons.

share|improve this answer
    
I think you are right the design I have currently isnt the best. –  cocojay Apr 19 '13 at 8:06
    
However your idea with concurrency is perfect for what I need and also tells me the number of edits a user has done! So i will use that as my default method of tracking changes! Thank you Jon! –  cocojay Apr 19 '13 at 8:12
    
@cocojay: You're welcome. Glad to help. –  Jon Seigel Apr 19 '13 at 13:31
add comment

The answer to your question is that yes, you can, provided that the users will have the necessary rights to update the time on their local machine. In your access front end, you would write some code to get the date/time from the SQL Server as in "select getdate()" and then you could use that to update the local time.

But, that's not really the correct solution. The correct solution is to have all your computers and servers keeping their time right against an NTP server.

And the other aspect of the correct way of doing that - because even then you don't have full control - is to do the timestamping in the query using getdate() or similar so that it use the SQL server date/time rather than the client.

share|improve this answer
    
Thank you for your answer ETL! Yes i completely agree that the best is to keep it all contained safely on the server side. I think I will test this method as a backup but predominantly use a incremented integer as concurrency to track user imput. –  cocojay Apr 19 '13 at 8:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.