03-29-2017 04:42 AM
I am getting below error when i submit the pass through code on sas EG. We have recently installed the sas 9.4 env.
23 proc sql;
24 connect to odbc as redshift (datasrc='Redshift' USER=xxxxx PASSWORd=xxxxxxxxxxxxxxxxx);
25 create table work.xxxxxx as
26 select *
27 from connection to redshift (select top 200 * from prod.xxxxxxxxx);
ERROR: CLI prepare error: [Amazon][RedShift ODBC] (30) Error occurred while trying to execute a query: ERROR: syntax error at or
near "敳敬瑣琠灯㈠〰⨠映潲牰摯献扩敢摟汥癩牥y"LINE 1:
敳敬瑣琠灯㈠〰⨠映潲牰摯献扩敢 摟汥癩牥y ^
SQL statement: select top 200 * from prod.xxxxxxxxxxxxxx.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
28 disconnect from redshift;
NOTE: Statement not executed due to NOEXEC option.
When I submit the libname statement only (below line)its execuated sucesfully without error.
23 LIBNAME redshift ODBC DATAsrc=Redshift SCHEMA=xxxx USER=xxxxxxxx PASSWORD=XXXXXXXXXXXXXXXXX ;
NOTE: Libref REDSHIFT was successfully assigned as follows:
Physical Name: Redshift
03-29-2017 06:27 AM
connect to odbc(datasrc='Redshift' USER=xxxxx PASSWORd=xxxxxxxxxxxxxxxxx);
from connection to odbc (select top 200 * from prod.xxxxxxxxx);
and make sure
select top 200 * from prod.xxxxxxxxx
is the right sql for redshift db.
03-29-2017 07:21 PM
It would help if you had said this script works fine in Prod but not in Dev - is that correct?. If so the question is what is different between your Prod and Dev databases and connections? Ask your DBA.
03-29-2017 04:52 PM
interesting the messages on an error on Line1, and with the chinesse characters.
It seems to me that something is passing the code/instruction on doble-byte format to the redshift database, when it might be expecting something simple-byte, or similar. On what char set are you running the sas session?
Anyway, i would expect a lack of harmony between the client driver and the database. Did you checked and tested your diver and its connection?
If you get stuck, my best suggestion is to go to the logs of the database and the client driver on your SAS server, and if you still cannot find anything, just increase the level of the logs.