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:
> 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/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Getting System Time From SQL Server

Neil (nospam@nospam.net) writes:
> 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/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Getting System Time From SQL Server

"Erland Sommarskog" wrote in message
news:Xns99F6EF225864CYazorman@127.0.0.1...
> Neil (nospam@nospam.net) writes:
>> 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

"Erland Sommarskog" wrote in message
news:Xns99F6EF225864CYazorman@127.0.0.1...
> Neil (nospam@nospam.net) writes:
>> 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" wrote:

>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

"Neil" wrote:

>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]" wrote in message
news:4a3uk39afrlojib6hgn1f3j59frc32livn@4ax.com...
> "Neil" wrote:
>
>>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]" wrote in message
news:4a3uk39afrlojib6hgn1f3j59frc32livn@4ax.com...
> "Neil" wrote:
>
>>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" wrote in message
news:474f32d7$0$497$815e3792@news.qwest.net...
> 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=test;"

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" wrote:

>Well, unfortunately that doesn't seem to be the case.

Which is why I put the weasel words "should".

>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

"Neil" wrote:

>Well, unfortunately that doesn't seem to be the case.

Which is why I put the weasel words "should".

>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" wrote in message
news:474f50d7$0$501$815e3792@news.qwest.net...
> 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=test;"
>
> 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" wrote:

>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

"Neil" wrote:

>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]" wrote in message
news:ukbvk3dm2tn1opt3tbtbp1d561lvq062qs@4ax.com...
> "Neil" wrote:
>
>>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]" wrote in message
news:ukbvk3dm2tn1opt3tbtbp1d561lvq062qs@4ax.com...
> "Neil" wrote:
>
>>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

> "Tony Toews [MVP]" wrote in message
> news:ukbvk3dm2tn1opt3tbtbp1d561lvq062qs@4ax.com...
>> "Neil" wrote:
>>
>>>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"

wrote in message news:O6o2%23EyMIHA.2432@TK2MSFTNGP04.phx.gbl...
> 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

> "Tony Toews [MVP]" wrote in message
> news:ukbvk3dm2tn1opt3tbtbp1d561lvq062qs@4ax.com...
>> "Neil" wrote:
>>
>>>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"

wrote in message news:O6o2%23EyMIHA.2432@TK2MSFTNGP04.phx.gbl...
> 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" wrote in message
news:8WT3j.25110$JD.20049@newssvr21.news.prodigy.net...
>
>> "Tony Toews [MVP]" wrote in message
>> news:ukbvk3dm2tn1opt3tbtbp1d561lvq062qs@4ax.com...
>>> "Neil" wrote:
>>>
>>>>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"
>
> wrote in message news:O6o2%23EyMIHA.2432@TK2MSFTNGP04.phx.gbl...
>> 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

Re: Getting System Time From SQL Server

"Neil" wrote in message
news:8WT3j.25110$JD.20049@newssvr21.news.prodigy.net...
>
>> "Tony Toews [MVP]" wrote in message
>> news:ukbvk3dm2tn1opt3tbtbp1d561lvq062qs@4ax.com...
>>> "Neil" wrote:
>>>
>>>>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"
>
> wrote in message news:O6o2%23EyMIHA.2432@TK2MSFTNGP04.phx.gbl...
>> 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