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) No edit summary |
||
(3 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
=Overview= | |||
The "SQL Server" Connector pulls data from Microsoft SQL Server. | |||
=Using rowversion fields= | =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. | 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: | 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 | ||
where CONVERT(bigint, | where CONVERT(bigint, row_version) > '@@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: | |||
<source lang="vb"> | <source lang="vb"> | ||
Function ScriptedVariable | Function ScriptedVariable | ||
Line 15: | Line 18: | ||
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 |
Latest revision as of 17:02, 1 March 2019
Overview
The "SQL Server" Connector pulls data from Microsoft SQL Server.
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, row_version) > '@@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