getdate() problem: where is the time taken from ?

Hi,

I have a funny situation.
Within: MSSQL 2000 SP3, everything below described is running on same
PC.

there is a program running, which sends information to two other
programs.
This information is a timestamp of the program in datetime format,
which has it's own clock.
The clock is incremented each 5 seconds of the program, which
corespondes to aprox. one second of the real time.
It means, each on second of real time, the computer time is updated +5
seconds.

Now, two other applications, are getting this information at the same
moment.
FIRST of this applications, updates local time of the computer with
the time recieved.
SECOND application, writes a protocol to file, with timestamp read at
moment of writing from operating system.
Until now, all times are equal (the differences are not biger that
ms).

Now, the SECOND application, after writing a log into file (with
proper timestamp), calls SP in database.
It passes as input prm. the time recieved from very first program,
which is the same time as the current system time, which is the same
time the SECOND application writes to the log file.

This SP (besides other things) at the very beginning writes a log into
table, where two times are logged:
- getdate() to first column,
- timestamp recieved as input parameter.

Now the funny thing.
I would expect, the times are equal.
getdate() = '2007.04.25 10:00:00.000'
prm_recieved = '2007.04.25 10:00:00.000'

I would expect, that the time from getdate() will be shifted with
miliseconds (because of call etc).
getdate() = '2007.04.25 10:00:00.123'
prm_recieved = '2007.04.25 10:00:00.000'

I would even expect, that the time is shifted 5 seconds ahead:
getdate() = '2007.04.25 10:00:05.000'
prm_recieved = '2007.04.25 10:00:00.000'

or, 5 seconds and some miliseconds:
getdate() = '2007.04.25 10:00:05.123'
prm_recieved = '2007.04.25 10:00:00.000'

What I can not UNDERSTAND, why sometimes the time is equal, or
sometimes is ALMOST equal (within the diff of miliseconds), and why
sometimes the time is like this(!!!) :

getdate() = '2007.04.25 10:59:55.000'
prm_recieved = '2007.04.25 10:00:00.000'

It seams to me, the getdate is getting somehow the PERVIOUS local
system time, which was acctualy already upgraded ! Becasue all other
app's are having the proper value.

All other apps are writen in C++ and are very simple.
I was trying to set the SQLServer running property higher - with no
result.
I need to mention, there is SQLServer Agent running, and one procedure
with endless loop, with waitfor delay equal 2 seconds.
But non of them (changing the waitfor delay to other value, disabling
SQLAgent) fixes the problem.

Can somebody then tell me, where from is the time taken, or what is
the root problem of this issue?
Or what can it be?

Best regards,

MatikMatik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

there is a program running, which sends information to two other
programs.
This information is a timestamp of the program in datetime format,
which has it's own clock.
The clock is incremented each 5 seconds of the program, which
corespondes to aprox. one second of the real time.
It means, each on second of real time, the computer time is updated +5
seconds.

So you have an application that modifies the computer clock every
second, and now you are asking why:

Quote:

Originally Posted by

What I can not UNDERSTAND, why sometimes the time is equal, or
sometimes is ALMOST equal (within the diff of miliseconds), and why
sometimes the time is like this(!!!) :
>
getdate() = '2007.04.25 10:59:55.000'
prm_recieved = '2007.04.25 10:00:00.000'

getdate() does not always reflect you recently updated system time.

I guess the answer is that there is not really reason that Windows and
SQL Server would behave the way you may want it to in this very special
scenario.

One reason that getdate() apparently lags behind is that getdate() has
a resolution of 3.33 ms which after all is quite a long time in a computer.
Assuming that SQL Server reads the system clock every 3.33 ms, getdate()
could seemingly lag behind your manipulated time.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||First, thanks Erland for the answer!

Quote:

Originally Posted by

So you have an application that modifies the computer clock every
second, and now you are asking why:

:) No ... of course, I'm not confused about the time changes :)
I'm expecting them ...
Confused for me, was this:

Quote:

Originally Posted by

Quote:

Originally Posted by

getdate() = '2007.04.25 10:59:55.000'
prm_recieved = '2007.04.25 10:00:00.000'

>
getdate() does not always reflect you recently updated system time.
>
I guess the answer is that there is not really reason that Windows and
SQL Server would behave the way you may want it to in this very special
scenario.
>
One reason that getdate() apparently lags behind is that getdate() has
a resolution of 3.33 ms which after all is quite a long time in a computer.
Assuming that SQL Server reads the system clock every 3.33 ms, getdate()
could seemingly lag behind your manipulated time.

Ok.
So ... that means for me as fallow:
The getdate() is not taking the current system time, only the buffered
SQLServer system time.
That means as well, that the time between changing system time,
writing into log (application) and calling procedure in DB, until this
position where the getdate() is called, MUST be shorter than a maximum
time of 3.33 ms.

Well, this is not I was thinking getdate() is doing:(
Is the current_timestamp function behaviour exactly in this way?
(probably yes, since in BOL says that this is the same as getdate())

Thank's Erland again for help.

Matik|||Matik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

So ... that means for me as fallow:
The getdate() is not taking the current system time, only the buffered
SQLServer system time.

I like to stress that is my own speculation of how the internals work.

Quote:

Originally Posted by

That means as well, that the time between changing system time,
writing into log (application) and calling procedure in DB, until this
position where the getdate() is called, MUST be shorter than a maximum
time of 3.33 ms.

If my theory is correct, yes, this appears to be a correct conclusion.

Quote:

Originally Posted by

Well, this is not I was thinking getdate() is doing:(
Is the current_timestamp function behaviour exactly in this way?
(probably yes, since in BOL says that this is the same as getdate())

I would expect that CURRENT_TIMESTAMP to be just a synonym fot getdate(). It
would be funny if two equivalent functions are implemented in different
ways.

I also like to point out that this kind of behaviour that could be different
in different versions of SQL Server, or even in different service packs.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||

Quote:

Originally Posted by

Quote:

Originally Posted by

The getdate() is not taking the current system time, only the buffered
SQLServer system time.

>
I like to stress that is my own speculation of how the internals work.
>

Quote:

Originally Posted by

That means as well, that the time between changing system time,
writing into log (application) and calling procedure in DB, until this
position where the getdate() is called, MUST be shorter than a maximum
time of 3.33 ms.

>
If my theory is correct, yes, this appears to be a correct conclusion.

I was thinkig about one more thing ...
Is there any way, to force sql server to refresh it's time?
Let's say, that by the procedure call, I will force him, to refresh
it's time ... will it be possible somehow?

Matik|||Matik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

I was thinkig about one more thing ...
Is there any way, to force sql server to refresh it's time?
Let's say, that by the procedure call, I will force him, to refresh
it's time ... will it be possible somehow?

Since all this is about behaviour that is strictly internal to SQL Server,
the likelyhood that there is a interface, documented or undocumented,
to affect this behaviour is about nil. Who knows, maybe there is a trace
flag, but don't stay up all night looking for it.

If you are on SQL 2005, you could write a CLR function which retrieves
the system time from Windows, with the regular 100 ns precision.

If you are on SQL 2000, you would have to write an extended stored
procedure, which may not be performant enough. (There is quite a cost
for the eontext switch.)

But getdate() seems dead in the water when you are living in the fast lane
like you do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||

Quote:

Originally Posted by

to affect this behaviour is about nil. Who knows, maybe there is a trace
flag, but don't stay up all night looking for it.

I wont :) Have other things to do as well :)

Quote:

Originally Posted by

If you are on SQL 2005, you could write a CLR function which retrieves

I'm on 2000 ... right now ...

Quote:

Originally Posted by

If you are on SQL 2000, you would have to write an extended stored

This is what I was thinking of...

Quote:

Originally Posted by

procedure, which may not be performant enough. (There is quite a cost
for the eontext switch.)

This is what I was afraid of :(

Quote:

Originally Posted by

But getdate() seems dead in the water when you are living in the fast lane
like you do.

I just need to change aproach probably, and try to solve it in
different way.
Probably the solution is what I did right now ...
Just, a the beginning, I'm sending procedure to sleep (waitfor delay).
It is not nice, and slows everything down, but maybe ... will be fast
enougth...
Otherwise ... try to do smth. else.

Thanks Erland again for your help and patience.

Best regards

Matik

Get User-Input into a Stored Proc

Hi,
Is there any way to accomplish this (SQL Server 2000):
Client-App sends an Update to an SP.
The SP has to perform various actions, amongst one setting a FK.
Normally there will only be One FK possible per Update.
BUT
It can happens there are multiple FK's possible for One Update.
Under that condition the possible FK's should be sent back to the App where
the User can select One FK.
Once that FK is returned to the SP, the Procedure can continue it's actions.
I fear this is science fiction though I'd like to be sure :-)
TIA,
MichaelMichael,
A stored procedure has no way to request further input from the user. You
can, of course, write two stored procedures (1) figure out if all is well
and (2) do the work, then design your app to use the procedures.
RLF
"Michael Maes" <michael.maes@.community.nospam> wrote in message
news:DD6A2970-CADA-4CF7-8188-9E95B73AE646@.microsoft.com...
> Hi,
> Is there any way to accomplish this (SQL Server 2000):
> Client-App sends an Update to an SP.
> The SP has to perform various actions, amongst one setting a FK.
> Normally there will only be One FK possible per Update.
> BUT
> It can happens there are multiple FK's possible for One Update.
> Under that condition the possible FK's should be sent back to the App
> where
> the User can select One FK.
> Once that FK is returned to the SP, the Procedure can continue it's
> actions.
> I fear this is science fiction though I'd like to be sure :-)
> TIA,
>
> Michael
>|||You can of course do all of this - but not in a stored proc. Why would that
matter? Stored procs are not for UI code.
David Portas
SQL Server MVP
--
"Michael Maes" <michael.maes@.community.nospam> wrote in message
news:DD6A2970-CADA-4CF7-8188-9E95B73AE646@.microsoft.com...
> Hi,
> Is there any way to accomplish this (SQL Server 2000):
> Client-App sends an Update to an SP.
> The SP has to perform various actions, amongst one setting a FK.
> Normally there will only be One FK possible per Update.
> BUT
> It can happens there are multiple FK's possible for One Update.
> Under that condition the possible FK's should be sent back to the App
> where
> the User can select One FK.
> Once that FK is returned to the SP, the Procedure can continue it's
> actions.
> I fear this is science fiction though I'd like to be sure :-)
> TIA,
>
> Michael
>|||Hi David & Russel,
Thanks for your replies.
The reason I would like to implement this is that various Bit, DateTime & FK
fields have to be set accross various tables depending on certain Updates on
another table.
On itself this is pretty straight foreward, but in the App this procedure
can be started on many forms under various ways and conditions.
It is the Undoing of this operation that is tadious in the App. (so many
variations). It makes it easy to break the logic.
Having it all done by an Update-Trigger on that table, causing it to launch
various sp's, makes it all solid.
The only caveat is that there * can * be more then One FK and it's
impossoble for non human-logic to determine which to use.
Thus I was "hoping" there would be any means to have a user-interaction on
this level.
I think I will have to come up with an alternative.
Any way: thanks for the input guys!
Regads,
Michael
"David Portas" wrote:

> You can of course do all of this - but not in a stored proc. Why would tha
t
> matter? Stored procs are not for UI code.
> --
> David Portas
> SQL Server MVP
> --
> "Michael Maes" <michael.maes@.community.nospam> wrote in message
> news:DD6A2970-CADA-4CF7-8188-9E95B73AE646@.microsoft.com...
>
>|||> The only caveat is that there * can * be more then One FK and it's
> impossoble for non human-logic to determine which to use.
I've no idea what this means. The foreign keys on a table are fixed
unless you are executing DDL in your update. So how can there be any
doubt about which they are?
David Portas
SQL Server MVP
--|||I think I have expressed myself badly.
it's all about the Value of the FK to save.
To put it in a simplified example:
A workorder can consist of various tasks.
Each task is a certain day performed by a certain technician (the FK)
In another table (installation - statistics) you can see what date the last
visit was by which technician for what type of job, ...
Normally an orders' childrececords (tasks) is always performed by the same
technician. But sometimes more technicians are assigned to an order (each
having his own taskrow).
Since the Technician FK only holds one Value, the user has to decide which
technician to assign for "the last visit" because it's important for
follow-up & support to know who was the "most important" technician. It's
impossible for 'Code' to know which one to choose.
Hence the User-Input.
I hope this clarifies a bit my 'case'.
Regards,
Michael
"David Portas" wrote:

> I've no idea what this means. The foreign keys on a table are fixed
> unless you are executing DDL in your update. So how can there be any
> doubt about which they are?
> --
> David Portas
> SQL Server MVP
> --
>|||Hi Michael,
Is it possible for you to provide a simplified table schema with sample
data to clarify it more? I am afriad one of the most possible resolution is
redesign the table structure or application structure.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
Thanks for your reply.
For the moment I have worked around the issue, so I guess the 'Case is
closed' :-)
Thanks,
Michael
"Michael Cheng [MSFT]" wrote:

> Hi Michael,
> Is it possible for you to provide a simplified table schema with sample
> data to clarify it more? I am afriad one of the most possible resolution i
s
> redesign the table structure or application structure.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi Michael,
You are welcome and thanks for the update.
If you have any questions or concerns next time, don't hesitate to let me
know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.