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 rid of an anonymous subscription

Hi all

I've got the following situation:

setup up an merge publication (distributor and publisher at the same server) setup an anonymous subscription deleted the subscription

In the replication monitor, I still see the deleted subscription.

Why?

And how do I get rid of it?

Thanks a lot for any informations and greetz

Aline

1. try to delete the subscription on the publisher side using sp_dropmergesubscription, (see http://msdn2.microsoft.com/en-us/library/ms176045.aspx) and

2. try to delete the subscription on the subscriber side using sp_dropmergepullsubscription, (see http://msdn2.microsoft.com/en-us/library/ms187336.aspx) if hte subscription is a Pull Subscription.

3. Restart the replication monitor.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights

|||

Unfortunetaly, I've already done this (with the management studio). But without getting in touch with the distributor.

I thought no metadata are stored at the distributor for an anonymous subscriber, but as I see it in the replication monitor there got to be some traces...

I'm still thankful for any hint, to get rid of these traces after deleting.

|||

Let me make sure

1. You have run the SP (sp_dropmergesubscription and sp_dropmergepullsubscription) manaully on the publisher/subscriber right (not going through the UI)? If not please run them manually.

2. Can you check the subscription by running select*from sysmergesubscriptions on the publication database?

Thanks.

This posting is provided AS IS with no warranties, and confers no rights

|||

I've done both by now (dropping via UI and with sp_dropmergepullsubscription). And still have it on the replication monitor, even after a restart of the replication monitor.

The "select * from sysmergesubscriptions" on the publication database doesn't show any dropped subscriptions.

The problem on the productive system is even more complex. A subscriber db was deleted (without dropping the subscription properly) and a new subscription from this subscriber to the same publication was made. As a result, the subscription appeared two times on the replication monitor (some Laptops appear up to 4 times!!). Now, there seems to be also a performance problem. So I hoped, I first would tidy up a little bit.

So, what happens if I drop the subscription with sp_dropmergepullsubscription, I remove the still working subscription without removing it from the replication monitor. And this dilemma I already have...

Any other ideas?

Thanks for your help so far.

|||

Sorry for the late reply.

Can you try to run sp_replmonitorhelpsubscription on the distribution database, http://msdn2.microsoft.com/en-us/library/ms188073.aspx?

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||

sp_replmonitorhelpsubscription shows all subscriptions - the already dropped included.

I now realise, I need another approach. The new questions are:

After deletion of an anoymous pull subscriber database (without first dropping the subscription regularly dropped with sp_dropmergepullsubscription), how do I remove all traces of it at the distributor / publisher?

Can I force the cleanup to remove entries, which are waiting to be synchronized only to this deleted subscription?

Thanks for your help

Aline

|||

Hi Aline,

This is a bug.

Currently when you create an anonymous subscription and delete it with:

sp_dropmergepullsubscription on the subscriber and sp_dropmergesubscription on the publisher, the subscription is removed from the subscriber and publisher databases. However some trace is left behind in the distribution database. Now replication monitor reads this data from the distributor and hence this subscriptions still shows up there.

You can use the follwoing workaround/trick :)

On the same publication, pub database, and sub database, create a dummy PULL subscription. You dont need to synchronize. Then call sp_dropmergepullsubscription on the subscriber and sp_dropmergesubscription on the publisher, this time with the appropriate parameters saying that it is a pull subscription. This will clear the entry in the distribution database and your monitor will not show this subscription anymore.

Get Random record from SQL table

Hi,

I am in a situation where our developer is on leave (annual leave for a month), and I have to add a control to my website, which is in aspx apges.

To start with i have created a table in my SQL database. this table has records with one-liners from various movies, and the movie title. The tabel have 3 columns, i.e. ID, Liners, MTitle.

So i want to get random records on the page when ever its refreshed. I am totally non-coder/programmer guy.

I have got a SQL statement from the internet " SELECT TOP 1 * FROM <table name> ORDER By NEWID() "

So would anyone please help me out with it, as is it correct, how can i apply in the aspx pages.

Thank you.

the sql statement is correct. Just change the <table name> to your actual table name|||

Hi,

If you want to get random records from the database via a single SQL statememt, you may try the method below:

Assuming there is a unique identifier for each row, and that there is at least one record in the table, retrieving a random record can be quite easy. This method will work in SQL Server 7.0 and above (running on Windows 2000), but this could be a performance problem on larger tables / resultsets:

SELECT TOP 1 someColumn FROM someTable ORDER BY NEWID()

If you are not running on Windows 2000, then the following code will work if your table has a unique identifier (e.g. IDENTITY) column:

SELECT TOP 1 someColumn FROM someTable ORDER BY RAND((1000*IDColumn)*DATEPART(millisecond, GETDATE()))

Note that both of these methods also allow you to select 10 or 50 or 5000 random records, simply by altering the TOP parameter

Hope it helps.