mibuso.com

Hi I'm working with SSIS and the data are daily updating. But I need to retrieve just new records from navision table (it can be any major table). Does there is some approach? How I undirstand the timestamp in navision table just identifying row?
Maybe there is some secret table with real unix timestamps and rows timestamps or something by which I can indentify new rows.

There are two ways:
1. select just new records from navision table
2. select all records and then do lookup...

First is the better, but is it possible?

I think we need a little more information around what you are trying to accomplish.

Getting “all new” (and only all new records) is not that easy. NAV offers a mechanism call Global Triggers that allows you to trap changes in real time to the database and – as you mention – SQL-timestamp could also be used if you write a query directly against SQL

The SQL timestamp is just a “counter” that is guaranteed to count upwards – Microsoft does not officially support getting any other information (e.g. date or time) from this field. It is supported to compare a timestamp field with timestamp fields from other records to find the “latest” one.

Finally SQL triggers could also be made to aid your scenario.

You will however need to realize, that all SQL timestamp mechanisms will yield all changed/inserted records since “last timestamp” but no info around deleted records.

Knowing which tables to “watch” will help you a lot, but we might provide more help if you could describe your scenario and why you need this…


Thomas

Finally - SQL Timestamp is being deprecated and is replaced by the more correctly describing name: RowVersion (but the concept is the same)

For SSIS integration the simplest is just to create a new Navision field "Last Edited On" in the particular table.

Not always you can go inside navision tables and edit them:) big companys with their own policys...
So question about the timestamp in navision tables

I can convert it to integer and vice versa?

If so it probably worked out, to take the last integer (converted timestamp to integer ) in my table and then just select records upwards that integer from navision table...

Yes, you should be able to select the timestamp column.

All NAV tables are created with a timestamp column (named 'timestamp'), SQL does not create such a column automatically, but in NAV-land we always create that column on the tables.

The fieldtype of the timestamp column is a binary(8) so you will get results like '0x0000000054e3d85c' if you do a raw select.

Converting this to a 64 bit integer can be done by doing a

Having that number you can now add a WHERE clause.

As you can see SQL does support implicit conversion here, so you don't need the CONVERT in the WHERE clause (although it does not harm to put it there)

This will give you all records that has a numeric timestamp (version) greater than 12345. Notice, this will include all new rows as well as all changed rows. Put otherwise: The timestamp gets updated on all inserts and modifications.

Hope this will solve your scenario...

Great information

I'm not sure about the binary timestamp. With this CONVERT statement, you essentially convert it to something humans can read, and I am wondering if it is necessary to do it in a query for ETL purposes.

Does the raw data sort properly? What I want to know is if we can use the raw field values to query, so that I don't have to worry about proper conversions.

I did the following two queries:

This produced the same results, so I am guessing it doesn't matter if you convert the value or not. Will this work on large datasets too?

I had the same question. If we can do it this way, and be sure it works it will make integration much simpler.

Thomas,

thank you for this information. It's always good to know what's going on rather than guessing. Especially when a client is relying on it.

Is there any chance that RowVersion will be documented (aka supported) in future versions. For some clients it is hard (politically) to use a feature that is not supported.

WOW!! if this is true it would be very very good, I'll try this:) thanks ThomasHej_MSFT:)

A method I successfully used was to add table triggers to store changes.
Using the easy way, using SQL to look at the inserted and deleted tables created problems by changing the timestamp.
Rashed Amini (Ara3n) on this forum advised me to use cursors instead which worked without problems.
This has been a while - it was SQL 2000 and NAV 4.01, but it would be worth trying with your version (providing you know a little SQL).

Ok, let me be more precise. Timestamp and rowversion are exactly the same thing on SQL Server. The unfortunate situation is that ISO standards describe SQL Timestamps as something related to date/time whereas MSSQL server decided to implement this as a simple counter. DB2, Oracle and other relational databases uses an actual timestamp (point in time) for timestamp.
Over time this has caused some confusion since developers using other databases as "used" to be able to extract date/time info from a timestamp, which is not possible on MSSQL.
Therefore MSSQL introduced the more correct name Rowversion to describe the same thing.
Furthermore the syntax when creating a table with a timestamp differs a litte from syntax creating a table with at rowversion (name of the column not needed when using timestamp)

The feature as such is there to stay and can be safely used.


Correct. The timestamp (being a counter) was originally created as a Binary[8], I assume that type was chosen since the big integer wasn't available before SQL Server 2000. Today it might as well have been a BigInt but is really does not matter since there is an implicit cast/convertion between bigint and binary[8].


The obvious answer: Yes! timestamp will act as any "normal" column.

The thoughtful answer: Watch out here. Problem is that SQL does not implicitly create an index on the timestamp column, so the WHERE timestamp>12345 will enumerate all records in the table (tablescan) unless you filter by other criteria (then only the subset will be enumerated).

To overcome this, you could add an index (manually) including the timestamp field - this would definetely solve the SELECT ... WHERE issue, but will add additional index-overhead to ANY modification since the timestamp gets updated on each modification.

Where does that leave you?

If you need to track all changes any technique (matching lookup, adding changes to another table, tracking deletions) will all be expensive and an index on timestamp will be as bad as any other mechanism.

In this scenario however, where you ONLY need to track new records (inserts) you might consider other techniques (after all - a record only gets inserted once in its lifetime), but this will of course require changes to the code or adding extra fields to tables in question.

But as always - try it out and measure,measure,measure..

Thanks for taking the time to write such a thorough reply, it really helps understand how this works.

here here

What about SQL Server Change Tracking (Standard Edition) or SQL Server Change Data Capture (Enterprise Edition only).
Those features were made for what you are looking for.

I think for your purposes CT is the smartest solution with the least amount of overhead on SQL Server.

I have worked with CT and the timestamp approach in real-life replication-like scenarios.
Both work pretty well.

I think the timestamp approach doesn't scale very well, because of the index-issue already mentioned.
In NAV 2009 R2 I would think about using the new global triggers instead of using the timestamp approach for small-scale solutions.
For the bigger-scale solutions I would use CT or CDC.

cheers
Tobias

Yes, they should also be evaluated in the general scenarios. (Thanks for pointing that out here!)

One need to realize however, that nothing is for free here. Both these techniques put the burden on SQL server to "track" the primary key or the entire record of changed records. This in turn means that every update will now generate inserts in tracking tables which again will require indexes (plural) to be updated.

Creating a single index on timestamp will - at least in theory - be more efficient, but this is exactly why one needs to measure...