Consider the following rules and guidelines when you configure an SQL transformation in mappings:
General guidelines
- You can't use the saved query type in an SQL transformation.
- To enable the autocommit behavior in an SQL query and stored procedure, set the EnableSqlTxAutoCommitSP custom property value to true for the Secure Agent.
SQL query
- You can't validate an entered query that you define in the SQL editor.
- You can't use the following properties when you process an entered query:
▪ Continue on SQL Error within row
▪ In-out and input parameters
▪ Stop on error
Stored procedure
- You can't use the same name for multiple stored procedures.
- Mappings fail when the stored procedure contains special characters or Unicode characters.
- You can't configure the On Stored Procedure Error advanced property in a mapping task.
- When you import data from PostgreSQL that contains the numeric data type, the Secure Agent imports the numeric data type with a default precision of 28 and scale of 26 irrespective of the precision you define for the numeric data type.
- When you import data from PostgreSQL that contains the char, varchar, text and bytea data types, the Secure Agent imports the data types with a precision of 32000 irrespective of the precision you define for the data types.
- If you add a new stored procedure to the database while you have the mapping open, the new stored procedure does not appear in the list of available stored procedures. To refresh the list, close and reopen the mapping.
- You can't use the bytea, time, boolean, citext, and timestamp with timezone data types in a stored procedure.