SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Returning Current Time		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts
Posted - 06/03/2004 :  08:19:42  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Ricky writes "I have a VB6 application using ADO connection to SQL Server 2000. It is a Time and Attendance package so it is very important that I pull the time from the same place everytime a transaction occurs. Problems ensue when I use this function because it uses the windows username to go to the server for the Time of Day and I do not want to have to add every user to the Server. The function itself does not allow me to specify to use the existing connection to the Server.

D = getRemoteTOD(ServerName)

Public Function getRemoteTOD(ByVal strServer As String) As Date

Dim result As Date
Dim lRet As Long
Dim tod As TIME_OF_DAY_INFO
Dim lpbuff As Long
Dim tServer() As Byte

tServer = strServer & vbNullChar
lRet = NetRemoteTOD(tServer(0), lpbuff)
If lRet = 0 Then
CopyMemory tod, ByVal lpbuff, Len(tod)
NetApiBufferFree lpbuff
result = DateSerial(tod.tod_year, tod.tod_month, _
tod.tod_day) + _
TimeSerial(tod.tod_hours, tod.tod_mins -tod.tod_timezone, _
tod.tod_secs)
getRemoteTOD = result
Else
Err.Raise Number:=vbObjectError + 1001, _
Description:="Cannot TOD from the Server"
End If

End Function

Question: Is there anyway to get the Date and Time from the server using the same connection that the Application is using?"

RickD
Slow But Sure Yak Herding Master

United Kingdom
3560 Posts
Posted - 06/03/2004 :  08:35:22  Show Profile  Reply with Quote
What part of SQL Server is this then??

In SQL you would use select getdate()
Edited by - RickD on 06/03/2004 08:35:33
Go to Top of Page

ClayCo68
Starting Member

2 Posts
Posted - 06/09/2004 :  10:07:43  Show Profile  Reply with Quote
I sent this to the great guys here at SQLTeam.com before I had a username and password, so yes, my real name is Ricky.

This is happening at runtime in the VB6 application on individual workstations connected to the network. How can I put the GetDate() function into a "Select" statement or How can I tell the GetDate() function which SQL Server connection to choose from?
Go to Top of Page

tfountain
Constraint Violating Yak Guru

USA
491 Posts
Posted - 06/09/2004 :  10:19:31  Show Profile  Reply with Quote
We had the same issue and determine we always wanted to use the database data and time through our various tiers. So we simply wrote a procedure to return the datetime value from the database like so:

USE <your database>
GO

IF OBJECT_ID('dbo.<pick your own name>') IS NOT NULL
	DROP PROCEDURE dbo.<pick your own name>
GO


CREATE PROCEDURE dbo.<pick your own name>
(
    @DateTime DATETIME OUTPUT
)
AS
SET @DateTime = GETDATE()
GO

GRANT ALL ON dbo.<pick your own name> TO PUBLIC
GO

Just call this procedure from your application. Better yet, make a utility procedure that wraps this call and just all that method from your code. This had to be a procedure instead of a function though since the use of GETDATE() is not allowed in functions (deterministic vs. non-deterministic stuff).
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts
Posted - 06/09/2004 :  12:06:45  Show Profile  Reply with Quote
What does the function NetRemoteTOD() do?

The function you provided does not connect to SQL Server. You mention you already have a connection.
Depending on how the connection is used, and when you want to get TOD, you may or may not be able to use the same connection. If you attempt to SELECT GETDATE() on a connection with a pending transaction or open resultset you will get an error in your app.
Go to Top of Page

ClayCo68
Starting Member

2 Posts
Posted - 06/14/2004 :  12:31:43  Show Profile  Reply with Quote
tfountain - Thanks I will give it a try.

kselvia - In the example that I provided, D = getRemoteTOD(ServerName), ServerName = is the IP address of the server that has SQL Server installed on it. The actual connection to SQL Server only occurs when the application starts or is being used by the employees, however I want to return the time every five seconds so that when the employees are clocking in and out they see an accurate representation of what thier time card is going to show. Actually connecting to SQL Server every five seconds or leaving the connection open is not really an option due to the number of people that will be using the system. I mainly hope to get the time from the same place that SQL Server would draw it from which is the actual server itself without the overhead of a full blown SQL connection.

Now that I've written it out, it seems that this is more of a VB6 issue than an SQL one but I do appreciate all the help and info. I will post my results to this anyway(if it is allowed to stay) so that it can be concluded.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000