ERROR: CLI cursor fetch erro

[phongpham]

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>

[Tom]

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.

[phongpham]

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 

[Tom]

---

@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.

[SASKiwi]

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?

[phongpham]

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

[phongpham]

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

[phongpham · Accepted solution]

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

[Tom]

---

@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.