Architecting, installing and maintaining your SAS environment

SAS ODBC ERROR

Reply
Frequent Contributor
Posts: 126

SAS ODBC ERROR

 

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.

Super User
Posts: 10,041

Re: SAS ODBC ERROR

Posted in reply to japsas100

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.

Frequent Contributor
Posts: 126

Re: SAS ODBC ERROR

Thanks....

 

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

Super User
Posts: 3,260

Re: SAS ODBC ERROR

Posted in reply to japsas100

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. 

Trusted Advisor
Posts: 1,326

Re: SAS ODBC ERROR

Posted in reply to japsas100

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.

 

 

Ask a Question
Discussion stats
  • 4 replies
  • 188 views
  • 0 likes
  • 4 in conversation