topic Re: ERROR: CLI cursor fetch erro in SAS Programming

ERROR: CLI cursor fetch erro

phongpham — Fri, 30 May 2025 00:36:11 GMT

Hi everyone,

I am downloading data from Server.

When I download small data (< 450 rows), it was successful, but same command with larger number of row (>1000 row), it will inform error as below.

I am wonder if anyone in our team coped with same error and know how to solve it.

Thanks very much if you can help!

ERROR: CLI cursor fetch error: [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]While executing transform        assignment:Bad data in assignment for <destination>.The first 17 bytes seem ok: "8.848774444739404"but the next 4 bytes        are: "e-05"This is not valid in a decimal.To support exponents in a decimal, add the 'allow..<truncated>

Re: ERROR: CLI cursor fetch erro

Tom — Fri, 30 May 2025 02:23:25 GMT

The error message seems be suggesting something in there did not understand how to translate scientific notation into numbers.

What type of database is the CRDHRDTA libref pointing to?

What engine is the _SCAMS libref using? Is it another foreign database?

Note if your actual variables are using more than 16 digits of precision then trying to copy them to SAS as NUMBERS will cause loss of precision. SAS floating point numbers cannot store that many digits precisely.

Re: ERROR: CLI cursor fetch erro

SASKiwi — Fri, 30 May 2025 03:03:31 GMT

Looks like the ODBC driver can't handle exponential notation. That is a perfectly acceptable number if you assign it in SAS.

I suspect you will have to cast the column into a format acceptable to the ODBC driver, but the first thing would be to identify which one is causing the problem. Try selecting a subset of the columns until you identify it. Also what external database are you reading?

Re: ERROR: CLI cursor fetch erro

phongpham — Mon, 02 Jun 2025 00:22:12 GMT

Thanks Tom.

Regarding to your questions, I would like to answer as below:

Type of database where the CRDHRDTA libref pointing to is SQL Window server.
Engine the _SCAMS libref using is SAS Viya 3.5
Is it another foreign database -> I am not aware of it

Re: ERROR: CLI cursor fetch erro

phongpham — Mon, 02 Jun 2025 00:25:08 GMT

Thanks for your suggestion!

Could you please provide more details on how to check a format acceptable to the ODBC driver.

I am not familiar with this process.

Cheers,

Phong

Re: ERROR: CLI cursor fetch erro

phongpham — Mon, 02 Jun 2025 00:34:41 GMT

I identified 5 columns that responsible for the situation. If eliminate it, then download is Ok. The data base is SQL Server.

Re: ERROR: CLI cursor fetch erro

phongpham — Mon, 02 Jun 2025 01:23:39 GMT

I found the solution: just need to cast the 5 caused column as text then it will fix problem.

Re: ERROR: CLI cursor fetch erro

Tom — Mon, 02 Jun 2025 02:44:39 GMT

@phongpham wrote:

Thanks Tom.

Regarding to your questions, I would like to answer as below:

Type of database where the CRDHRDTA libref pointing to is SQL Window server.

Engine the _SCAMS libref using is SAS Viya 3.5

Is it another foreign database -> I am not aware of it

So I suspect you meant it is connecting to a Microsoft SQL Server database. That probably does run on a Microsoft Windows Server, but it is the database itself rather than the computer it runs on that is important to know.

Do you mean that _SCAMS is pointing to a CASLIB? Or are you just using normal SAS libname statement that points to a physical disk directory which will use the native BASE SAS engine that only supports floating point numbers and fixed length character variables.

Foreign database just means not using native SAS libref engines, instead connecting to some other database, like Microsoft SQL Server.

You might want to look into seeing if using PROC FEDSQL instead of PROC SQL might help. That is supposed to be able to copy between foreign databases what uses data types that the BASE SAS does not support. So perhaps with PROC FEDSQL you could copy from your SQL Server database directly into your CASLIB file.

Re: ERROR: CLI cursor fetch erro

Tom — Mon, 02 Jun 2025 03:01:40 GMT

@phongpham wrote:

Thanks for your suggestion!

Could you please provide more details on how to check a format acceptable to the ODBC driver.

I am not familiar with this process.

Cheers,

Phong

It is probably more important to know the TYPE of the variables involved. You should be able to use the ODBC::SQLColumns() function in pass-thru SQL to find the names and data types of the variables in the dataset (or "columns" in the "table") that you are trying to extract.

https://documentation.sas.com/doc/en/pgmsascdc/v_063/acreldb/p1f29m86u65hken1deqcybowtgma.htm

If the variable is a LONGINT or DECIMAL with too large a precision it will not be able to transfer it as a number into a SAS dataset. Casting it as CHAR is one way to allow the data to transfer.