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 with the program

It is very interesting to me that most people in this group can't do simple
SQL. Are you viewing MySQL as a simple file system?As long as you do that,
you will
not understand SQL in any dialect. So from now on:

When you have a question about SQL. post the table structures. Uh, "With
Create".
Post some sample data in the form of INSERTS.

Regards,
Rich

--
The journey is the reward."Rich R" <rryan@.cshore.com> wrote in message
news:IxhHd.19290$by5.3314@.newssvr19.news.prodigy.c om...
> It is very interesting to me that most people in this group can't do
simple
> SQL. Are you viewing MySQL as a simple file system?As long as you do that,
> you will
> not understand SQL in any dialect. So from now on:
> When you have a question about SQL. post the table structures. Uh, "With
> Create".
> Post some sample data in the form of INSERTS.
>
> Regards,
> Rich
> --
> The journey is the reward.

Sorry, wrong group. Hate when that happens. Please ignore.

Regards,
Rich|||"Rich R" <rryan@.cshore.com> wrote in message
news:0AhHd.19291$by5.12203@.newssvr19.news.prodigy. com...
> "Rich R" <rryan@.cshore.com> wrote in message
> news:IxhHd.19290$by5.3314@.newssvr19.news.prodigy.c om...
>> It is very interesting to me that most people in this group can't do
> simple
>> SQL. Are you viewing MySQL as a simple file system?As long as you do
>> that,
>> you will
>> not understand SQL in any dialect. So from now on:
>>
>> When you have a question about SQL. post the table structures. Uh, "With
>> Create".
>> Post some sample data in the form of INSERTS.
>>
>>
>> Regards,
>> Rich
>>
>> --
>> The journey is the reward.
>
> Sorry, wrong group. Hate when that happens. Please ignore.
> Regards,
> Rich

That's alright, its a universal issue, just ask --CELKO--

Get the variable from Execute Process Task to C#

Hi!

I need help with some C# code. I have build a SSIS package with an Execute Process Task. I need to send dynamic variables in to my C# program so I thought it was a good idea to use the StandardInputVariable.

How do I get the variable in my C# code?
Thanks

CarlYour Main methods parameter collection?|||

Peter K wrote:

Your Main methods parameter collection?

yes. provided that this functionality has been built into the c# code.|||Thanks for your help.

I tried to get the variable through the main method but it dont work, the only thing I got was the argument.
My test code:
static void Main(string[] args)
{

for (int i=0; i<args.Length; i++)
{
Console.WriteLine(args);
Console.ReadLine();
}

Carl|||

ctsand wrote:

Thanks for your help.

I tried to get the variable through the main method but it dont work, the only thing I got was the argument.
My test code:
static void Main(string[] args)
{

for (int i=0; i<args.Length; i++)
{
Console.WriteLine(args);
Console.ReadLine();
}

Carl

did you include the variable as an argument to your c# executable in the execute process task?|||I have a static argument and a variable in StandardInputVarable. I put a value in the variable for testing but it will be dynamic.|||

ctsand wrote:

I have a static argument and a variable in StandardInputVarable. I put a value in the variable for testing but it will be dynamic.

is this necessary? can't you just use a dynamically updated ssis variable when calling your executable in the execute process task?|||

My intention was have to have the argument to jump to a special method in the code. The variable will have information about witch rows in the table the code shall read in and treat.

In any case, can I put a dynamic variable in the argument?

Carl

|||

the code below is how to execute package in c# code.the red code tell you how to dymamic edit variable.i think this method can get variable.but i didn't try.please try it

//add reference "Microsoft.SqlServer.ManagedDTS"(in microsoft.sqlserver.manageddts.dll)
Imports Microsoft.SqlServer.Dts.Runtime

Dim pkg As String = "package directory"

Dim app As Application = New Application()
Dim p As Pakage = app.LoadPackage( pkg, Nothing )
p.InteractiveMode = true
Dim pty As DtsProperty

'Dim n As Integer = p.Configurations.Count

Dim strPty As String
For Each pty In p.Properties
strPty = pty.Name & ":"
Try
If pty.Get Then strPty &= pty.GetValue( pty ).ToString()
Catch
End Try
Console.WriteLine( strPty )
Next

Dim vir As Variables = p.Variables
vir( "strFile" ).Value = "C:\MyApp2.txt"

Console.WriteLine( p.Execute( Noting, vir, Nothing, Nothing, Nothing ).ToString() )

|||

ctsand wrote:

My intention was have to have the argument to jump to a special method in the code. The variable will have information about witch rows in the table the code shall read in and treat.

understood

In any case, can I put a dynamic variable in the argument?

Carl

i don't know. did you try?|||

I tried it but it didnt worked. The only thing I got was the name of the variable.

You wrote earlier:

is this necessary? can't you just use a dynamically updated ssis variable when calling your executable in the execute process task?

What did you mean by that?

|||

ctsand wrote:

I tried it but it didnt worked. The only thing I got was the name of the variable.

You wrote earlier:

is this necessary? can't you just use a dynamically updated ssis variable when calling your executable in the execute process task?

What did you mean by that?

what are the option settings in the process page of the execute process task editor?|||

RequireFullFileName = True

Executable = M:\Program\Person.exe

Arguments = fakt

WorkingDirectory = M:\Program

StandardInputVariable = User::test

StandardOutputVariable =

StandardErrorVariable =

FailTaskIfReturnCodeIsNotSuccessValue = True

SuccessValue = 0

TimeOut = 0

TerminateProcessAfterTimeOut = True

WindowStyle = Normal

|||what is the value and data type of User::test immediately before the execute process task starts executing? is the value of this variable correct?|||

The data type is string and the value is testing.

I have a question for you.

Is't meaning that I shall use the main-method to get the argument and the variable?

Carl

get the data directory

Hi,

I am searching for how getting the data directory where default mdf files are based.

(C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data)

My goal is to deploy mdf file in this folder during installation.

Thanks

hi,

I'm little in trouble giving advieces as well as registry values are not honored as the seem..

you can query, via a NON documented extended stored procedure the Windows registry for some info..

you can see a HKLM\Microsoft\Microsoft SQL Server key..

SQL Server 2005 registers instances as MSSQL.1, MSSQL.n, and this for every engine type, like Report server, Olap engine, ...

but you can get the MSSQL.x value querying the \Instance Names\SQL key as well

something like

SET NOCOUNT ON;

DECLARE @.test varchar(256);

DECLARE @.instance VARCHAR(128);

DECLARE @.regKey VARCHAR(128);

SELECT @.instance = CONVERT(varchar, SERVERPROPERTY('InstanceName'));

IF @.instance IS NULL

SET @.regKey = 'MSSQLServer';

ELSE

SET @.regKey = @.instance;

SELECT @.regKey AS [Instance name];

EXEC master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',

@.key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\',

@.value_name = @.regKey,

@.value = @.test OUTPUT;

SELECT @.test AS [base instance directory];

SET @.regKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @.test + '\Setup';

EXEC master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',

@.key = @.regKey ,

@.value_name = 'SQLDataRoot',

@.value = @.test OUTPUT;

SELECT @.test AS [SQL Path as per Setup key];

--<-

Instance name

MSSQLServer

base instance directory

MSSQL.1

SQL Path as per Setup key

--

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL

but here problems arise... as usually data folder is in \...\MSSQL.1\MSSQL\Data , but that Data part is not shown anywhere.. .you can "perhaps" going littbe bit further, querying the \Parameters\SQLArg0 , which reports the full path of the master.mdf file formatted as the startup parameter required by SQL Server, thats to say

SQLArg0 = "-dC:\Program Files\..here full path.......\master.mdf"

and parse the result to extract the required "path".. or just hope no one changes that path and hardcode a + '\Data\' addition...

but, again, this whole post is in complete undocumented and unsupported mode..

regards

|||Thanks for your help Andrea

Get tablename in trigger

Hello,
I have a general trigger program used for many tables, but how do I refer to
the tablename currently been modified in my script?
Thanks!
PerCREATE TABLE TT
(
COL INT
)
CREATE TRIGGER MY_TR ON TT
FOR INSERT
AS
DECLARE @.ObjID int
SET @.ObjID = (SELECT parent_obj FROM sysobjects WHERE id = @.@.PROCID)
SELECT OBJECT_NAME(@.ObjID) AS 'Parent Table'
INSERT INTO TT VALUES (1)
SELECT * FROM TT
DROP TABLE TT
"Per Buus S?rensen" <PerBuusSrensen@.discussions.microsoft.com> wrote in
message news:99BA6A44-27DC-4D58-8794-7A90F24A2B8F@.microsoft.com...
> Hello,
> I have a general trigger program used for many tables, but how do I refer
> to
> the tablename currently been modified in my script?
> Thanks!
> Per
>|||Working perfect :-)
Thanks!
"Uri Dimant" wrote:

> CREATE TABLE TT
> (
> COL INT
> )
> CREATE TRIGGER MY_TR ON TT
> FOR INSERT
> AS
> DECLARE @.ObjID int
> SET @.ObjID = (SELECT parent_obj FROM sysobjects WHERE id = @.@.PROCID)
> SELECT OBJECT_NAME(@.ObjID) AS 'Parent Table'
> INSERT INTO TT VALUES (1)
> SELECT * FROM TT
> DROP TABLE TT
>
>
> "Per Buus S?rensen" <PerBuusSrensen@.discussions.microsoft.com> wrote in
> message news:99BA6A44-27DC-4D58-8794-7A90F24A2B8F@.microsoft.com...
>
>|||I'm not certain what you mean by a "general trigger program". If you
mean you are generating and re-using the same code for each table then
surely you would put the table name in there when you generate the
code, in which case there would be no need to do it dynamically at
runtime. That's the method I would recommend anyway.
If you mean you are calling the same proc from each trigger then Uri's
code won't help you. In that case I think you will have to pass the
table name as a parameter.
David Portas
SQL Server MVP
--|||The code was OK, I am writting one procedure which can apply to many tables,
however I have a issue with dynamic SQL, which I have put in a new post.
Per
"David Portas" wrote:

> I'm not certain what you mean by a "general trigger program". If you
> mean you are generating and re-using the same code for each table then
> surely you would put the table name in there when you generate the
> code, in which case there would be no need to do it dynamically at
> runtime. That's the method I would recommend anyway.
> If you mean you are calling the same proc from each trigger then Uri's
> code won't help you. In that case I think you will have to pass the
> table name as a parameter.
> --
> David Portas
> SQL Server MVP
> --
>