Stored Procedures - sorengranfeldt/sqlma GitHub Wiki

The MA has the option to run stored procedures at certain points of the synchronization schedules. You can configure each Stored Procedure individually and emit some if they are not needed. These stored procedure options can be configured on the Global Parameter page of the MA.

The MA supports six different stored procedures -

  • Import
    • Pre-Import
    • Post-Import
  • Export
    • Pre-Export
    • Post-Export
    • Pre-ObjectExport
    • Post-ObjectExport

Import

You can configure two stored procedure to be run with your import synchronization. One to be run at the start of the import cycle and one to be run at the end of the import cycle.

Pre-Import (Run before import)

This stored procedure takes two parameters.

  • ImportType - a string parameter having either value 'Full' or 'Delta' depending on configuration of Run Profile.
  • CustomData - a string parameter holding the delta watermark. The value type of this string will depend on the Delta Column Type selected. Note that this could be 'Null' or blank so take appropriate action in stored procedure to handle this. This value can be used tom i.e. clean out old softdeleted values or do calculation of updated objects.

Post-Import (Run after import)

This stored procedure takes two parameters.

  • ImportType - a string parameter having either value 'Full' or 'Delta' depending on configuration of Run Profile.
  • CustomData - a string parameter holding the delta watermark. The value type of this string will depend on the Delta Column Type selected. Note that this could be 'Null' or blank so take appropriate action in stored procedure to handle this. This value can be used tom i.e. clean out old softdeleted values or do calculation of updated objects.

Export

You can configure two stored procedure to be run with your export synchronization. One to be run at the start of the export cycle and one to be run at the end of the export cycle.

Pre-Export (Run before export)

This stored procedure takes one parameter.

  • ExportType - a string parameter having either value 'Export' or 'Full Export'

Post-Export (Run after export)

This stored procedure takes one parameter.

  • ExportType - a string parameter having either value 'Export' or 'Full Export' Besides having stored procedures run before and after export, you can also configure stored procedure to be run before and after each object export. This allows you granular control over the individual objects, i.e. like taking a copy of the object about to be updated before it is actually updated, thereby allowing you to keep a history of changes to a particular object.

Pre-ObjectExport (Run before export object)

This stored procedure takes two parameters.

  • Anchor - the anchor value of the object being exported. Please note that this could be null if the export action is an Add and your tables are using data source generated ID's, i.e. using Identity Specification.
  • Action - a string value indicating the action that set for the exported object. Will be either 'Add', 'Replace' or 'Delete'.

Post-ObjectExport (Run after export object)

This stored procedure takes two parameters.

  • Anchor - the anchor value of the object being exported. Please note that this could be null if the export action is an Add and your tables are using data source generated ID's, i.e. using Identity Specification.
  • Action - a string value indicating the action that set for the exported object. Will be either 'Add', 'Replace' or 'Delete'.

Stored Procedure Timeout

Specify the maximum amount of seconds that any of the stored procedures is allowed to run. This setting applies to all stored procedures for the MA and therefore should be set to the highest common run time that any of the stored procedures is expected to run. A minimum value of 30 seconds and a maximum value of 99999 seconds is allowed.

Sample SQL Stored Procedures

Below are a few sample stored procedures that can work as a template for building your own.

This procedure template is the same for pre- and post-import, however the value of customdata could change before the call to the post-import procedure if you are using delta's, since this value is updated by the MA during import.

create procedure [dbo].[preimport]
	@importtype nvarchar(50) = null,
	@customdata int = null
as
begin
	-- log action to separate 'action' table
	insert into actions ([action], [actiontype], [customdata]) values ('preimport', @importtype, @customdata);

	-- clean up old soft-deletes
	delete from myobjects where isdeleted = 1 and rowversion < @customdata
end

This template procedure supports pre- and post-export calls and has the one parameter passed with value of either 'Export' or 'Full Export'

create procedure [dbo].[preexport]
	@exporttype nvarchar(50) = null
as
begin
	insert into actions ([action]) values ('preexport')
end

This procedure supports pre- and post-objectexport, where you can see that the anchor (@anchor) of the object being exported is passed along with action to be done on the particular object (@action). This template is the same for pre- and postexports.

create procedure [dbo].[preobjectexport]
	@anchor	uniqueidentifier,
	@action nvarchar(50)
as
begin
	-- log action to separate 'action' table
	insert into actions ([action], [anchor], [modtype]) values ('preobjectexport', @anchor, @action)
end

Make sure to set the type of the @anchor parameter to the same type as you are using for the primary key in the single-value table.