Microsoft SQL Server: Difference between revisions
Jkuehlthau (talk | contribs) |
Jkuehlthau (talk | contribs) |
||
Line 7: | Line 7: | ||
SELECT *, CONVERT(bigint, row_version) num_row_version FROM TBL_Company | SELECT *, CONVERT(bigint, row_version) num_row_version FROM TBL_Company | ||
where CONVERT(bigint, COTIMESTAMP) > '@@VAR:LastRowVersion@@' | where CONVERT(bigint, COTIMESTAMP) > '@@VAR:LastRowVersion@@' | ||
order by | order by num_row_version asc | ||
</source> | </source> | ||
Retrieve the LastRowVersion with a Before Once Before Conn script. Note that I am using SQLite Xrefs so I can read and write to the same Xref in the same Job. Example: | Retrieve the LastRowVersion with a Before Once Before Conn script. Note that I am using SQLite Xrefs so I can read and write to the same Xref in the same Job. Example: |
Revision as of 18:46, 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 num_row_version asc
Retrieve the LastRowVersion with a Before Once Before Conn script. Note that I am using SQLite Xrefs so I can read and write to the same Xref in the same Job. Example:
Function ScriptedVariable ScriptedVariable=XrefRead("LastRowVersion","Accounts") End Function
Set the LastRowVersion ewith an After Repeat Each Row script. Note that I am using SQLite Xrefs so I can read and write to the same Xref in the same Job. Example:
Sub VBScriptProcedure If Not PreviewMode Then XrefWrite "LastRowVersion","Accounts","@@ORG:num_row_version@@" End If End Sub