Microsoft SQL Server Connector

Microsoft SQL Server Connector



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

Parameter
Description
Data Source
Initial Catalog (Database Name)

SQL Server Connection Edit Screen


    • Related Articles

    • Microsoft Dynamics ERP - AX Connector

      Our Microsoft Dynamics AX Connector is no longer supported.  We will offer support for Microsoft Dynamics 365 Finance and Operations in the near future. Parameter Description Domain Object Server Dynamics AX Connection Edit Screen

    • Microsoft Dynamics ERP - GP Connector

      Additional Documentation  This Connector is based on the Dynamics GPADO.NET Provider by CData. For additional help, including String parameters, please see: https://cdn.cdata.com/help/DPG/ado/ Dynamics GP Version Support The provider supports read ...

    • Microsoft Dynamics CRM Connector

      Versions of MS CRM Supported: On-Prem v2011 and later, MS CRM Cloud Parameter Description URL After logging into Dynamics CRM, this is the first portion of the URL that displays in your browser's address bar. If your account is hosted by Dynamics ...

    • Microsoft Dynamics NAV / Business Central

      Additional Documentation This Connector is based on the Dynamics NAV ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/CNG/ado/ As of June 15, 2020, this connector supports ...

    • Exchange Connector

      Additional Documentation This Connector is based on the Microsoft Exchange ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/CEG/ado/ Overview The "Exchange (NEW)" connector ...