Unable to run a Data Connector import rule when a field includes the apostrophe character
|Article:TECH159464|||||Created: 2011-05-05|||||Updated: 2011-05-10|||||Article URL http://www.symantec.com/docs/TECH159464|
A Data Connector import rule accesses a field that includes the apostrophe character " ' ". When tested, the test succeeds, but when ran, it fails with various errors.
The database <not a database name> does not exist.
An error that there is a syntax error near "s" character, and the letter can change as well, can also occur.
The internal processing of an import rule when ran, regardless of a test working successfully (which is not actually performing the full import which is why it may work), does not by design include encapsulation for non-terminated apostrophe strings. This will result in the string not terminating as it should, and various errors then occurring because the specific field could not be imported.
In SQL, a string starts with and is terminated by a single quote (an apostrophe). For example:
'This is an example of a string in SQL.'
In a field, this appears without the single quotes.
When this issue occurs, an apostrophe is somewhere in a field that has a string value. For example:
'This is this article's example of a string with an apostrophe in SQL.'
What occurs when this is attempted to be added to the internal code is that the string becomes "This is this article" and the "'s example of a string with an apostrophe in SQL." becomes an invalid string. This results in an error.
If this issue occurs, do not use an apostrophe in the affected fields moving forward. In some cases, using an apostrophe may be fine, however.
- Use a SQL script to remove the apostrophes from the affected fields, or otherwise blanking their values.
- Set the data source that the import rule uses to use data pre-processing. A script can then be used to parse the affected fields looking for an apostrophe, and if found, removing the character dynamically before it is added to Altiris. Note: This does not remove the apostrophe from the original fields. The following article demonstrates how this can be made to work:
Data Connector import rule fails to import fields as null when they are double quotes
Article URL http://www.symantec.com/docs/TECH159464