BookmarkSubscribeRSS Feed
japsas100
Pyrite | Level 9

 

Hello,

 

I am getting below error when i submit the pass through code on sas EG. We have recently installed the sas 9.4 env. 

 

 

ERROR: 

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:
Engine: ODBC
Physical Name: Redshift

 

Please advise.

4 REPLIES 4
Ksharp
Super User

Try

connect to odbc(datasrc='Redshift' USER=xxxxx PASSWORd=xxxxxxxxxxxxxxxxx);

select *
 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.

japsas100
Pyrite | Level 9

Thanks....

 

Same script also runing into prod env. I am very sure there is no issue with script.

SASKiwi
PROC Star

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. 

JuanS_OCS
Amethyst | Level 16

Hello @japsas100,

 

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.

 

 

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 993 views
  • 0 likes
  • 4 in conversation