When running your Agent for a DataSync integration with MS SQL Server, a directive in the agent.xml configuration file named <force_preparedstatement_reload> has its value set to true by default so that SQL statements are batched together to prevent primary key violation exceptions. You can turn off this functionality, or stop reloading prepared SQL statements, by changing the value of this directive to false according to the procedure described below.
Prerequisites
First, you will need to install a DataSync Agent configured to share data to an MS SQL Server database.
You will also need to create a ServiceNow dynamic share with an update trigger or create a ServiceNow bulk share.
Finally, make sure to stop running your DataSync Agent before making any Agent configuration changes.
Stop reloading prepared SQL statements
To stop reloading prepared SQL statements for your DataSync integration to an MS SQL Server database, follow these steps:
Access your agent.xml configuration file
Navigate to the directory where you saved your agent.xml file when installing your DataSync Agent.
Change the <force_preparedstatement_reload> directive's value to false
Open your agent.xml file in a text editing application. Then, locate the <force_preparedstatement_reload> directive(s) nested within the <task> directive(s) and change each value to false per the example shown below:
<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<config>
<agent>
<share/>
<subscribe>
<task instances="1">
<task_name>mssql_test_subscribe</task_name>
<message_connection password="encrypted:vlOtU71yu8N/EFIJH85SSBtaIt7qEEfvqiqft9VZyYE=" queue="psp.out.replicator.test" use_basic_consume="true" user="admin">amqps://test.perspectium.net</message_connection>
<instance_connection password="encrypted:vlOtU71yu8N/EFIJH85SSPN9aF0P5/YViVwPEVFcGW4=" user="testuser">https://mycompany.service-now.com</instance_connection>
<handler>com.perspectium.replicator.sql.SQLSubscriber</handler>
<decryption_key>This is my decryption key for testing</decryption_key>
<database_type>sqlserver</database_type>
<database_server>localhost</database_server>
<database_port>1433</database_port>
<database_user>testuser</database_user>
<database_password>testpassword</database_password>
<database_parms/>
<database_column_max_size>251</database_column_max_size>
<database>psp_repl</database>
<force_preparedstatement_reload>false</force_preparedstatement_reload>
</task>
</subscribe>
<max_reads_per_connect>4000</max_reads_per_connect>
<polling_interval>5</polling_interval>
<skip_message_set_processing/>
</agent>
</config>
Save your agent.xml file
Save the changes you made to your agent.xml file and close the file.