Problem Note 64581: The Insert Rows transformation generates incorrect explicit PROC SQL pass-through code when the source and target tables are in different databases
 |  |  |  |  |
An issue occurs when your source and target tables for the Insert Rows transformation are in different database management systems (DBMS). In that scenario, SAS® Data Integration Studio generates incorrect code to insert the rows in the target table. The query is generated using explicit SQL procedure pass-through code. For example, if you are inserting rows into an Oracle table and a Microsoft SQL Server table is your source table, the PROC SQL INSERT section of the code would be generated as follows:
(
insert into scott.CLASS (NAME, SEX, AGE, HEIGHT, WEIGHT)
select
CLASS.Name,
CLASS.Sex,
CLASS.Age,
CLASS.Height,
CLASS.Weight
from
dbo.CLASS CLASS
) by ORACLE;
The Microsoft SQL Server table reference is included in the code that is processed by the Oracle database. You might receive an error similar to the following:
The workaround is to use one of these methods to change the setting for the PROC SQL pass-through option:
- Set the SQL procedure pass-through option to No. You will find the option in the properties of the Insert Row transformation. After you open the properties of the transformation, select Options ► Database pass-through.
- Deselect the Set Pass Through to yes for new SQL transformation check box. You will find this check box in Tools ► Options ► Job Editor.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Data Integration Studio | Microsoft® Windows® for x64 | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 |
| Microsoft Windows 8 Enterprise 32-bit | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Microsoft Windows 8 Enterprise x64 | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Microsoft Windows 8 Pro 32-bit | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Microsoft Windows 8 Pro x64 | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Microsoft Windows 8.1 Enterprise 32-bit | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Microsoft Windows 8.1 Enterprise x64 | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Microsoft Windows 8.1 Pro 32-bit | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Microsoft Windows 8.1 Pro x64 | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Microsoft Windows 10 | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Microsoft Windows Server 2012 Datacenter | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Microsoft Windows Server 2012 R2 Datacenter | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Microsoft Windows Server 2012 R2 Std | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Microsoft Windows Server 2012 Std | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Microsoft Windows Server 2016 | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Microsoft Windows Server 2019 | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Windows 7 Enterprise 32 bit | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Windows 7 Enterprise x64 | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Windows 7 Home Premium 32 bit | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Windows 7 Home Premium x64 | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Windows 7 Professional 32 bit | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Windows 7 Professional x64 | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Windows 7 Ultimate 32 bit | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||
| Windows 7 Ultimate x64 | 4.904 | 4.905 | 9.4 TS1M6 | 9.4 TS1M7 | ||