Warning "This query contains at least one of the SQL statements that are unusual for data retrieval" occurs when using a Data Connector OLEDB data source that includes an import query
Last Updated April 29, 2014
When using a Data Connector OLEDB data source that connects to a SQL database, an import query is used. In this query, certain statements are present, such as CREATE, DROP, TRUNCATE, INSERT, UPDATE or DELETE. When the "View import data", "Test data source" or "Save changes" buttons are clicked, a warning about using these statements occurs.
Just as the warning states, a SQL statement is present that is not designed to retrieve data. This can cause data loss and is not recommended to be used in a Data Connector data source import query. Note: While earlier versions of Data Connector did not display this error, data loss could still occur. Therefore, SQL statements not designed to retrieve data for any version of Data Connector are not recommended.
Insofar as the warning is concerned, remove the SQL statements not designed to retrieve data to prevent this warning from occurring. While this may not be desired, the cause of the issue are these SQL statements. If the SQL script can be redesigned in such a way to not require these, this should then enable the import query to work successfully. Or, the user, at their own risk, can choose to click on the warning's OK button and allow the SQL statements to still run. This may not, however, enable the import query to work as expected (refer to the below information for more about why).
In Data Connector 7.1, SQL statements not designed to retrieve data will immediately execute when the data source's "View import data" or "Test data source" buttons are clicked, or when the import rule is later ran. Clicking on the "Save changes" button does not cause the SQL statements to be immediately executed.
In Data Connector 7.5, all three data source buttons including the "Save changes" button now cause the SQL statements to be immediately executed. For the "Save changes" button, 7.5 changed the way this works by increasing its "meta data" size saved of the data when the "Save changes" button is clicked. This cannot be disabled and my cause the import rule to not see the correct records to import or none at all.
Workarounds may be difficult if not impossible to accomplish. The following are some examples of how this could work:
Workaround 1: Use an task to perform the SQL statements not designed to retrieve data.
Use an task to perform the SQL statements in question and afterwards, it can then run the import rule. This would have virtually the same effect as if the import rule, when ran, processed the entire SQL script that used to be in the data source. For example:
Original import query from a data source
SET NOCOUNT ON UPDATE RM_ResourceComputer SET Name = 'New Name' WHERE GUID = '<GUID>' SELECT * FROM RM_ResourceComputer
When the data source is viewed, tested or saved, the UPDATE statement immediately runs, not when the user expects it to when the import rule is later run.
Using a task
Create a new task. This will then do two things:
Runs the UPDATE statement as a SQL command:
UPDATE RM_ResourceComputer SET Name = 'New Name' WHERE GUID = '<GUID>'
Runs the import rule that has the modified data source import query of:
SELECT * FROM RM_ResourceComputer
The following article describes how to create this task:
If this workaround will not work for the user, they will need to find some other way to process their data before it is used in a data source. This may have to then be a SQL script that they run in the SQL Server Management Studio, then export the results to a .CSV or .XLS file and use that with the data source instead of direct database manipulation.
Workaround 2: Use pre-processing or data filtering.
If the sole purpose of the change is to control what data is being presented to the import rule, instead consider using data source pre-processing or data filtering on the import rule. Through logic then, the same end result should be able to be achieved.
Workaround 3: Use a processed data file instead of an OLEDB import query.
Through whatever means, such as SQL Server Management Studio, commit the changes necessary. Then, export the changed data to a .CSV or .XLS file. Use this instead of directly accessing the database via an OLEDB data source that uses an import query.
Workaround 4: Perform the entire process in SQL.
Consider performing the entire process of what is being imported directly in SQL, if only changes are being made. The import query is already half-way there, simply set this up in SQL or via a task, either of which could be ran on a schedule.
Workaround for 7.5 only: Save the data source, then in SQL, undo the changes.
If only the data source causes and issue with the SQL statements, it's possible that after the save, if the changes can be undone in SQL directly. Then, when the import rule runs, if it then executes the SQL statements as expected, this could be a viable workaround.
Data Connector 7.5 for the warning, all versions of Data Connector for the symptoms.
Imported Document ID: TECH216947
Subscribing will provide email updates when this Article is updated. Login is required to Subscribe