Usage Note 69628: Bulk loading and unloading with SAS/ACCESS® Interface to Snowflake returns the error "Error executing COPY command: SQL compilation error..."
 |  |  |  |  |
SAS/ACCESS Interface to Snowflake uses the Snowflake supported COPY INTO data-loading methodology. As part of this process, there are some situations where additional options might be needed to successfully load the data into the desired table. The list of options supported by Snowflake with COPY INTO are available in the Snowflake documentation. However, SAS/ACCESS Interface to Snowflake supports only a standard subset of these options. As a result, if you add an option that the SAS/ACCESS engine does not support, a SQL compilation error is returned.
The standard COPY INTO syntax that is generated by the engine is similar to the following:
COPY INTO "SCHEMA"."TABLE" ("Var1","Var2","VarN")
FROM '@~/stagename/'
FILE_FORMAT=( TYPE=CSV FIELD_DELIMITER='\007' COMPRESSION=NONE )
FORCE=TRUE
FILES = ('temp_stage_data_file.dat' )
In some cases, you might want to add additional options. For example, FIELD_OPTIONALLY_ENCLOSED_BY = is a Snowflake FILE_FORMAT option that is often used to prevent data-load errors when special characters exist in the data. If you manually add this option to the SAS/ACCESS bulk-load options with BL_OPTIONS=, the noted SQL compilation error is returned. This error occurs because of the incorrect syntax that is generated by the SAS/ACCESS engine for the COPY INTO command. Any Snowflake supported option that would add to or alter existing options is not supported at this time.
This hot fix allows the use of the additional Snowflake COPY INTO options.
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 | SAS Release | |
| Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to Snowflake | Microsoft® Windows® for x64 | ||
| Linux for x64 | ||||
Viya on Linux: An update for this issue is available for SAS Viya 3.5. For instructions on how to access and apply software updates, see the Updating Your SAS Viya software section in the SAS Viya 3.5 for Linux Deployment Guide at
http://documentation.sas.com/?softwareId=administration&softwareVersion=3.5&softwareContextId=softwareUpdatesA fix for this issue for SAS/ACCESS Interface to Snowflake 9.42 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/I9Y.html#69628| Type: | Usage Note |
| Priority: |
| Date Modified: | 2022-11-01 10:51:22 |
| Date Created: | 2022-10-17 15:19:05 |