Microsoft SQL Server: Difference between revisions
Jump to navigation
Jump to search
Jkuehlthau (talk | contribs) (Created page with "=Using rowversion fields= Many integrations run frequently and only want to pick up records modified since the last time the integration ran. This can be accomplished with Da...") |
(No difference)
|
Revision as of 18:35, 5 August 2016
Using rowversion fields
Many integrations run frequently and only want to pick up records modified since the last time the integration ran. This can be accomplished with Date/Time fields, but a better option is to use the MSSQL Row Version type of filed. This is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. See: https://msdn.microsoft.com/en-us/library/ms182776.aspx.
To accomplish this:
- Setup your origin to use the Rowversion field. Note that you must convert the rowversion field to the bigint field type. Example:
SELECT *, CONVERT(bigint, row_version) num_row_version FROM TBL_Company where CONVERT(bigint, COTIMESTAMP) > '@@VAR:LastRowVersion@@' order by COtimestamp asc
- Retrieve the LastRowVersion with a Before Once Before Conn script. Example:
Function ScriptedVariable ScriptedVariable=XrefRead("LastRowVersion","Accounts") End Function
- Set the LastRowVersion ewith an After Repeat Each Row script. Example:
Sub VBScriptProcedure If Not PreviewMode Then XrefWrite "LastRowVersion","Accounts","@@ORG:num_row_version@@" End If End Sub