mibuso.com

Microsoft Business Solutions online community
It is currently Thu Apr 24, 2014 4:01 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 16 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: How identify new records in any navision table?
PostPosted: Sun Jan 22, 2012 12:25 am 
Offline

Joined: Sat Jan 21, 2012 9:58 pm
Posts: 3
Country: Lithuania (lt)
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?


Top
 Profile E-mail  
 
 Post subject: Re: How identify new records in any navision table?
PostPosted: Sun Jan 22, 2012 1:24 pm 
Offline
Microsoft employee

Joined: Sat Jan 21, 2012 10:19 am
Posts: 14
Country: Denmark (dk)
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)

_________________
Thomas Hejlsberg
CTO, Architect - Microsoft Dynamics NAV


Top
 Profile E-mail  
 
 Post subject: Re: How identify new records in any navision table?
PostPosted: Sun Jan 22, 2012 4:35 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Tue Aug 14, 2001 7:01 am
Posts: 5349
Location: Prague
Country: Czech Republic (cz)
For SSIS integration the simplest is just to create a new Navision field "Last Edited On" in the particular table.

_________________
David Singleton
Dynamics NAV Freelancer
Dynamics Book
Go-Live International


Top
 Profile E-mail WWW  
 
 Post subject: Re: How identify new records in any navision table?
PostPosted: Sun Jan 22, 2012 7:16 pm 
Offline

Joined: Sat Jan 21, 2012 9:58 pm
Posts: 3
Country: Lithuania (lt)
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
Quote:
The SQL timestamp is just a “counter” that is guaranteed to count upwards

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...


Top
 Profile E-mail  
 
 Post subject: Re: How identify new records in any navision table?
PostPosted: Mon Jan 23, 2012 10:41 am 
Offline
Microsoft employee

Joined: Sat Jan 21, 2012 10:19 am
Posts: 14
Country: Denmark (dk)
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
Code: Select all
SELECT CONVERT(bigint,timestamp),<other fields> FROM <tablename>

Having that number you can now add a WHERE clause.
Code: Select all
SELECT CONVERT(bigint,timestamp),<other fields> FROM <tablename> WHERE CONVERT(bigint,timestamp)>12345

or simply

SELECT CONVERT(bigint,timestamp),<other fields> FROM <tablename> WHERE timestamp>12345

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...

_________________
Thomas Hejlsberg
CTO, Architect - Microsoft Dynamics NAV


Top
 Profile E-mail  
 
 Post subject: Re: How identify new records in any navision table?
PostPosted: Mon Jan 23, 2012 4:24 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7966
Location: Howell, MI
Country: United States (us)
Great information :mrgreen:

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:
Code: Select all
SELECT [timestamp] FROM [std2009R2$Cust_ Ledger Entry] WHERE
CONVERT(bigint,[timestamp]) <  130940

SELECT [timestamp] FROM [std2009R2$Cust_ Ledger Entry]
WHERE timestamp < 0x000000000001FF7C

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?

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Top
 Profile  
 
 Post subject: Re: How identify new records in any navision table?
PostPosted: Mon Jan 23, 2012 4:38 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Tue Aug 14, 2001 7:01 am
Posts: 5349
Location: Prague
Country: Czech Republic (cz)
DenSter wrote:
Great information :mrgreen:

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:
Code: Select all
SELECT [timestamp] FROM [std2009R2$Cust_ Ledger Entry] WHERE
CONVERT(bigint,[timestamp]) <  130940

SELECT [timestamp] FROM [std2009R2$Cust_ Ledger Entry]
WHERE timestamp < 0x000000000001FF7C

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.

:thumbsup:

_________________
David Singleton
Dynamics NAV Freelancer
Dynamics Book
Go-Live International


Top
 Profile E-mail WWW  
 
 Post subject: Re: How identify new records in any navision table?
PostPosted: Mon Jan 23, 2012 4:45 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Tue Aug 14, 2001 7:01 am
Posts: 5349
Location: Prague
Country: Czech Republic (cz)
ThomasHej_MSFT wrote:
Finally - SQL Timestamp is being deprecated and is replaced by the more correctly describing name: RowVersion (but the concept is the same)


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.

_________________
David Singleton
Dynamics NAV Freelancer
Dynamics Book
Go-Live International


Top
 Profile E-mail WWW  
 
 Post subject: Re: How identify new records in any navision table?
PostPosted: Mon Jan 23, 2012 6:29 pm 
Offline

Joined: Sat Jan 21, 2012 9:58 pm
Posts: 3
Country: Lithuania (lt)
ThomasHej_MSFT wrote:
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
Code: Select all
SELECT CONVERT(bigint,timestamp),<other fields> FROM <tablename>

Having that number you can now add a WHERE clause.
Code: Select all
SELECT CONVERT(bigint,timestamp),<other fields> FROM <tablename> WHERE CONVERT(bigint,timestamp)>12345

or simply

SELECT CONVERT(bigint,timestamp),<other fields> FROM <tablename> WHERE timestamp>12345

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...

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


Top
 Profile E-mail  
 
 Post subject: Re: How identify new records in any navision table?
PostPosted: Mon Jan 23, 2012 7:27 pm 
Offline

Joined: Fri Aug 06, 2004 9:30 pm
Posts: 1024
Location: Richardson
Country: United States (us)
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).

_________________
David Machanick
http://mibuso.com/blogs/davidmachanick/


Top
 Profile  
 
 Post subject: Re: How identify new records in any navision table?
PostPosted: Tue Jan 24, 2012 5:37 pm 
Offline
Microsoft employee

Joined: Sat Jan 21, 2012 10:19 am
Posts: 14
Country: Denmark (dk)
David Singleton wrote:
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.

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.

DenSter wrote:
...this produced the same results, so I am guessing it doesn't matter if you convert the value or not

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].

DenSter wrote:
Will this work on large datasets too?

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.. :-)

_________________
Thomas Hejlsberg
CTO, Architect - Microsoft Dynamics NAV


Top
 Profile E-mail  
 
 Post subject: Re: How identify new records in any navision table?
PostPosted: Tue Jan 24, 2012 6:01 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7966
Location: Howell, MI
Country: United States (us)
Thanks for taking the time to write such a thorough reply, it really helps understand how this works. :mrgreen: :thumbsup:

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Top
 Profile  
 
 Post subject: Re: How identify new records in any navision table?
PostPosted: Tue Jan 24, 2012 10:21 pm 
Offline

Joined: Tue Jun 21, 2011 1:39 pm
Posts: 522
Country: Netherlands (nl)
here here =D>


Top
 Profile E-mail  
 
 Post subject: Re: How identify new records in any navision table?
PostPosted: Wed Jan 25, 2012 9:49 am 
Offline

Joined: Fri Sep 30, 2011 9:14 pm
Posts: 21
Country: Germany (de)
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


Top
 Profile E-mail  
 
 Post subject: Re: How identify new records in any navision table?
PostPosted: Wed Jan 25, 2012 11:04 am 
Offline
Microsoft employee

Joined: Sat Jan 21, 2012 10:19 am
Posts: 14
Country: Denmark (dk)
ndbcs wrote:
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.

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... :-)

_________________
Thomas Hejlsberg
CTO, Architect - Microsoft Dynamics NAV


Top
 Profile E-mail  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 16 posts ]  Go to page 1, 2  Next

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: