Microsoft SQL Server: Difference between revisions
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...") |
Jkuehlthau (talk | contribs) |
||
| Line 3: | Line 3: | ||
To accomplish this: | 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: | |||
<source lang="sql"> | <source lang="sql"> | ||
SELECT *, CONVERT(bigint, row_version) num_row_version FROM TBL_Company | SELECT *, CONVERT(bigint, row_version) num_row_version FROM TBL_Company | ||
| Line 9: | Line 9: | ||
order by COtimestamp asc | order by COtimestamp 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: | |||
<source lang="vb"> | <source lang="vb"> | ||
Function ScriptedVariable | Function ScriptedVariable | ||
| Line 15: | Line 15: | ||
End Function | End Function | ||
</source> | </source> | ||
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: | |||
<source lang="vb"> | <source lang="vb"> | ||
Sub VBScriptProcedure | Sub VBScriptProcedure | ||
Revision as of 18:37, 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. 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