Timestamp import from Database wrong?

[womisa]

Hi
i have a Timestamp column in a SqlServer 2500 Database an import this into DIADEM. Diadem rounds the Milliseconds > 500 to the next second. Is this right or wrong?

Database ......................................   Diadem
23.10.2007 13:19:17.357 ===> 23.10.2007 13:19:17.3570
23.10.2007 13:19:18.607 ===> 23.10.2007 13:19:19.3570     ??? why??
23.10.2007 13:19:19.357  ===>23.10.2007 13:19:19.6070

Can anybody explain me this?

Thank Achim

[Brad_Turpin]

Hi womisa,

Could you please provide a longer list of contiguous records, similar to the 3 record list you submitted already?  It doesn't look like a rounding error to me-- it looks like DIAdem is loading in the Nth millisecond value for the N+1th datetime value.  I seem to remember a similar bug in the ASCII Import Wizard in DIAdem a long time ago.  I have already begun pestering R&D about your 2 data base questions but haven't heard back yet.  64 bit integer fields and datetime fields with milliseconds are two use cases that don't come up that often.

Brad Turpin
DIAdem Product Support Engineer
National Instruments

[womisa]

Hi Brad,

thanks for your answer. I was nont in the office. I think it is a problem with the database query. (....ORDER BY xxx... the timestamp column). Can i configure the query in Diadem or ist this only possible with a script? I'm not a Diadem profi....Can i configure a query with each import?
Thanks
Achim

ps.: My problem is the import of the DatetimeTicks (bigint)...this is possible with ORIGIN. Have i a chance of a bugfix?

[Brad_Turpin]

Hi Achim,

So you're saying the "bigint" import issue and the "DateTime-with-milliseconds" import issue are one and the same?  I thought they were separate.  Does SQL-Server store a DateTime value as the number of milliseconds since a particular date?

If you want to add any conditions or ordering syntax to the import, you do indeed need to use a VBScript.  I am including below a "SQL Wizard" application with which you can do this without needing to program.  You will need to edit line 12 in the "SQL Wizard 101.VBS" to add your data base connection string, but otherwise you should just be able to run it.  This may not solve your problem, but it should enable you to try out a few things, such as adding an ORDER BY clause, etc.

Brad Turpin
DIAdem Product Support Engineer
National Instruments

[womisa]

Hi Brad,

thanks for your WIZARD. Sorry fo my little english...but i try...

The datatype in the SQLServer is for a TIMESTAMP of Type

...[WsDateTime] [datetime] NULL DEFAULT (getdate()),.....

with a granularity of 3 Millisec. The Timestamp is automatically inserted by the Databaseengine.....

I have ceated a DATAVIEW in the database  (with a order by datetime) and have this loaded into Diadem but this is not sorted in DIADEM. I must sort this explicit in Diadem. The Timestamp are right an has no rounding error.....but in the wrong sequenz....

The bigint Column has nothing do do with the Timestamp from the Database. C# has Timeticks (DateTimeTicks) with a granularity of 100 Nanosekunden. I use this as "MY"-Timestamp in the Database ...[WsDateTimeTicks] [bigint] NULL......

Thanks
Achim

[womisa]

Hi Brad,

is this the right wizard?
I have SQL Wizard 110.VBS not 101.VBS

Call OdsValAlloc("SqlWizDir", "ODS_TYPE_STRING", 0)
SqlWizDir = AutoActPath
Call ScriptCmdAdd(AutoActPath & "SQL Wizard 110 Event.VBS")
Navigator.Events.OnDataStoreLoading = "SQL_DataStoreLoading"
Navigator.Display.OpenDataStore("SQL Example")
Call DataDelAll
PtlShow = 1
'Navigator.Events.OnDataStoreLoading = ""

with has no Line 12  ===>"You will need to edit line 12 in the "SQL Wizard 101.VBS"

Thanks
Achim

[womisa]

Hi Brad,

i have now a simple testcase. The database table has 100 entrys. The Timestamp (WsDateTime)  is incremented by 100 milliseconds. I have no columns with a bigint!!! This is still wrong.
The dadabase.pdf shows the databaseentries, diadem.pdf the loaded data into Diadem. Compare the WsDatetime colums...what is wrong?  Bug?

Thanks
Achim

[womisa]

Hi Brad,

sorry..can you delete the previouse upload threads.

Thanks
Achim