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="4">
<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.
1 Comment
user-4287d
Hi user-1bc08 ,
Discussed this document with Howie and Mark. Although the information here is accurate, it is a bit confusing as the Title "Force reload a prepared SQL statement" suggest this document includes information on:
The steps walk you through modifying the agent.xml file to add the "<force_preparedstatement_reload>false</force_preparedstatement_reload>" tag. At quick glance, it suggest that this is how you Force reload a prepared SQL Statement but these are steps on how to disable the feature.
Additionally, the document also suggest that this feature is only available for Microsoft SQL Server. It is actually available for all database types. In SQL Server, we simply just default this tag to true without the tag being in the agent.xml file while it needs to manually added for all other Database types.
Possible to re-write this document based on this feedback? Please reach out if you have any questions.