Getting System Time From SQL Server | | |
I'm using an Access MDB as a front end to a SQL 7 database. When I place the
system time in a field in Access, it used the user's clock. I'd like to use
the SQL Server system time, so that the times will be synchronized. I
suppose I can just do a pass-through query with "select getdate()." But I
was wondering if there was some built-in method, either in Access or SQL
Server. I'm concerned about the overhead of running a pass-through query
every time I need the current time.
Thanks.
| | | | re: Getting System Time From SQL Server
Neil (nospam@nospam.net) writes: Quote:
I'm using an Access MDB as a front end to a SQL 7 database. When I place
the system time in a field in Access, it used the user's clock. I'd like
to use the SQL Server system time, so that the times will be
synchronized. I suppose I can just do a pass-through query with "select
getdate()." But I was wondering if there was some built-in method,
either in Access or SQL Server. I'm concerned about the overhead of
running a pass-through query every time I need the current time.
It you want the time on the server, "SELECT getdate()" is probably the
simplest way to do it. But you could of course make a remote procedure
call outside SQL Server as a little exercise in Windows programming
if you like. I can't see that's worth the pain, though.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx | | | | re: Getting System Time From SQL Server
"Erland Sommarskog" <esquel@sommarskog.sewrote in message
news:Xns99F6EF225864CYazorman@127.0.0.1... Quote:
Neil (nospam@nospam.net) writes: Quote:
>I'm using an Access MDB as a front end to a SQL 7 database. When I place
>the system time in a field in Access, it used the user's clock. I'd like
>to use the SQL Server system time, so that the times will be
>synchronized. I suppose I can just do a pass-through query with "select
>getdate()." But I was wondering if there was some built-in method,
>either in Access or SQL Server. I'm concerned about the overhead of
>running a pass-through query every time I need the current time.
>
It you want the time on the server, "SELECT getdate()" is probably the
simplest way to do it. But you could of course make a remote procedure
call outside SQL Server as a little exercise in Windows programming
if you like. I can't see that's worth the pain, though.
>
:-) Thanks! Just wanted to make sure I wasn't missing something. | | | | re: Getting System Time From SQL Server
"Neil" <nospam@nospam.netwrote: Quote:
>I'm using an Access MDB as a front end to a SQL 7 database. When I place the
>system time in a field in Access, it used the user's clock. I'd like to use
>the SQL Server system time, so that the times will be synchronized. I
>suppose I can just do a pass-through query with "select getdate()." But I
>was wondering if there was some built-in method, either in Access or SQL
>Server. I'm concerned about the overhead of running a pass-through query
>every time I need the current time.
In a corporate network everyone should be using a common time source.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ | | | | re: Getting System Time From SQL Server
Well, unfortunately that doesn't seem to be the case. Individual computers
vary from the server time by as much as 10 minutes! Is there some sort of
setting to use to use the server time. Maybe I'll pass that along to the
admin and see if he implements it.
"Tony Toews [MVP]" <ttoews@telusplanet.netwrote in message
news:4a3uk39afrlojib6hgn1f3j59frc32livn@4ax.com... Quote:
"Neil" <nospam@nospam.netwrote:
> Quote:
>>I'm using an Access MDB as a front end to a SQL 7 database. When I place
>>the
>>system time in a field in Access, it used the user's clock. I'd like to
>>use
>>the SQL Server system time, so that the times will be synchronized. I
>>suppose I can just do a pass-through query with "select getdate()." But I
>>was wondering if there was some built-in method, either in Access or SQL
>>Server. I'm concerned about the overhead of running a pass-through query
>>every time I need the current time.
>
In a corporate network everyone should be using a common time source.
>
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ | | | | re: Getting System Time From SQL Server
Hi Neil,
Add a column to your sql server table which will have the default value
set to getdate(). In the Design view of the table go to the Default
Value cell and type getdate(). Whenever a new row gets added, the sever
system date will be added automatically.
If you don't have access to Enterprise manager, then you can use ADO and
a command object to add the column using
"Alter Table yourTable Add CurDate datetime Not Null Default(getdate()))
Rich
*** Sent via Developersdex http://www.developersdex.com *** | | | | re: Getting System Time From SQL Server
Thanks, Rich -- no I have the default date when a record is created. I need
it for other things, such as functions that are performed in my VBA code.
Thanks,
Neil
"Rich P" <rpng123@aol.comwrote in message
news:474f32d7$0$497$815e3792@news.qwest.net... Quote:
Hi Neil,
>
Add a column to your sql server table which will have the default value
set to getdate(). In the Design view of the table go to the Default
Value cell and type getdate(). Whenever a new row gets added, the sever
system date will be added automatically.
>
If you don't have access to Enterprise manager, then you can use ADO and
a command object to add the column using
>
"Alter Table yourTable Add CurDate datetime Not Null Default(getdate()))
>
>
Rich
>
*** Sent via Developersdex http://www.developersdex.com ***
| | | | re: Getting System Time From SQL Server
try this:
Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
cmd.ActiveConnection = "Provider=SQLOLEDB;Data
Source=yourserver;Database=yourDB;UID=test;PWD=tes t;"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandText = "Select getdate()"
Set RS = cmd.Execute
Debug.Print RS(0)
RS.Close
cmd.ActiveConnection.Close
you retrieve the server date and can apply it as needed
Rich
*** Sent via Developersdex http://www.developersdex.com *** | | | | re: Getting System Time From SQL Server
"Neil" <nospam@nospam.netwrote: Quote:
>Well, unfortunately that doesn't seem to be the case.
Which is why I put the weasel words "should". <smile> Quote:
>Individual computers
>vary from the server time by as much as 10 minutes! Is there some sort of
>setting to use to use the server time.
Yes, there is however I'm not at all sure how to do it. I think it involves ensuring
a server is hitting an Internet time source on a regular basis. Then, presumably
using group policy, ensure everyone on the network get thier time from the server.
But I'm not an IT person so I have no idea.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ | | | | re: Getting System Time From SQL Server
Thx.
"Rich P" <rpng123@aol.comwrote in message
news:474f50d7$0$501$815e3792@news.qwest.net... Quote:
try this:
>
Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
>
cmd.ActiveConnection = "Provider=SQLOLEDB;Data
Source=yourserver;Database=yourDB;UID=test;PWD=tes t;"
>
cmd.ActiveConnection.CursorLocation = adUseClient
>
cmd.CommandText = "Select getdate()"
>
Set RS = cmd.Execute
Debug.Print RS(0)
>
RS.Close
cmd.ActiveConnection.Close
>
you retrieve the server date and can apply it as needed
>
Rich
>
*** Sent via Developersdex http://www.developersdex.com ***
| | | | re: Getting System Time From SQL Server
"Neil" <nospam@nospam.netwrote: Quote:
>Well, unfortunately that doesn't seem to be the case. Individual computers
>vary from the server time by as much as 10 minutes! Is there some sort of
>setting to use to use the server time. Maybe I'll pass that along to the
>admin and see if he implements it.
BTW there are functions to also get the system time from a server and
compare that to the time on the PC. You can use that to show the IT
department.
API: Retrieve NT Server's Time http://www.mvps.org/access/api/api0039.htm
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ | | | | re: Getting System Time From SQL Server
The command line (which should be included in the logon script) is:
net time /DOMAIN:DomainName /SET
HtH
Pieter
"Tony Toews [MVP]" <ttoews@telusplanet.netwrote in message
news:ukbvk3dm2tn1opt3tbtbp1d561lvq062qs@4ax.com... Quote:
"Neil" <nospam@nospam.netwrote:
> Quote:
>>Well, unfortunately that doesn't seem to be the case. Individual computers
>>vary from the server time by as much as 10 minutes! Is there some sort of
>>setting to use to use the server time. Maybe I'll pass that along to the
>>admin and see if he implements it.
>
BTW there are functions to also get the system time from a server and
compare that to the time on the PC. You can use that to show the IT
department.
>
API: Retrieve NT Server's Time http://www.mvps.org/access/api/api0039.htm
>
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ | | | | re: Getting System Time From SQL Server
Quote:
"Tony Toews [MVP]" <ttoews@telusplanet.netwrote in message
news:ukbvk3dm2tn1opt3tbtbp1d561lvq062qs@4ax.com... Quote:
>"Neil" <nospam@nospam.netwrote:
>> Quote:
>>>Well, unfortunately that doesn't seem to be the case. Individual
>>>computers
>>>vary from the server time by as much as 10 minutes! Is there some sort of
>>>setting to use to use the server time. Maybe I'll pass that along to the
>>>admin and see if he implements it.
>>
>BTW there are functions to also get the system time from a server and
>compare that to the time on the PC. You can use that to show the IT
>department.
>>
>API: Retrieve NT Server's Time
> http://www.mvps.org/access/api/api0039.htm
>>
>Tony
>--
>Tony Toews, Microsoft Access MVP
> Please respond only in the newsgroups so that others can
>read the entire thread of messages.
> Microsoft Access Links, Hints, Tips & Accounting Systems at
> http://www.granite.ab.ca/accsmstr.htm
> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ >
>
"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.no spam.please@online.replace.with.norway>
wrote in message news:O6o2%23EyMIHA.2432@TK2MSFTNGP04.phx.gbl... Quote:
The command line (which should be included in the logon script) is:
>
net time /DOMAIN:DomainName /SET
>
HtH
>
Pieter
>
Just to be clear, putting that into the user's logon script will set their
PC's time to the server's time? | | | | re: Getting System Time From SQL Server
"Neil" <nospam@nospam.netwrote in message
news:8WT3j.25110$JD.20049@newssvr21.news.prodigy.n et... Quote:
> Quote:
>"Tony Toews [MVP]" <ttoews@telusplanet.netwrote in message
>news:ukbvk3dm2tn1opt3tbtbp1d561lvq062qs@4ax.com.. . Quote:
>>"Neil" <nospam@nospam.netwrote:
>>>
>>>>Well, unfortunately that doesn't seem to be the case. Individual
>>>>computers
>>>>vary from the server time by as much as 10 minutes! Is there some sort
>>>>of
>>>>setting to use to use the server time. Maybe I'll pass that along to the
>>>>admin and see if he implements it.
>>>
>>BTW there are functions to also get the system time from a server and
>>compare that to the time on the PC. You can use that to show the IT
>>department.
>>>
>>API: Retrieve NT Server's Time
>> http://www.mvps.org/access/api/api0039.htm
>>>
>>Tony
>>--
>>Tony Toews, Microsoft Access MVP
>> Please respond only in the newsgroups so that others can
>>read the entire thread of messages.
>> Microsoft Access Links, Hints, Tips & Accounting Systems at
>> http://www.granite.ab.ca/accsmstr.htm
>> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ >>
>>
>
"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.no spam.please@online.replace.with.norway>
wrote in message news:O6o2%23EyMIHA.2432@TK2MSFTNGP04.phx.gbl... Quote:
>The command line (which should be included in the logon script) is:
>>
>net time /DOMAIN:DomainName /SET
>>
>HtH
>>
>Pieter
>>
>
Just to be clear, putting that into the user's logon script will set their
PC's time to the server's time?
>
To the DOMAIN controller's time.
The SQL server (which in most cases is not a DC) should also synch to the
domain controller.
There's also a registry setting to force it to synch that can be set under
domain policies.
The only time I've had to do the net time /domain: in recent memory was when
the time of the client was so far off that it wouldn't log in properly.
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html |  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,510 network members.
|
