topic Re: ERROR: CLI open cursor error: Unable to retrieve error message. in SAS Data Management

ERROR: CLI open cursor error: Unable to retrieve error message.

Venkat4 — Wed, 01 Mar 2017 03:16:17 GMT

Recently our SQL server data source has changed to a new SQL server. When the SAS admins changed the hostname, then I have been getting this error.

ERROR: CLI open cursor error: Unable to retrieve error message.

I use the same PROC SQL code connecting via explicit passthrough through ODBC connection from the SAS server.

I tried both in EG and in batch, both of them giving the same error.

Even I tried local ODBC connection - still the same error with this new SQL server.

When I asked DBAs, they are saying they don't know about this error, and it is not related to the SQL server.

SAS admins could not able to figure out this error. Any help would be appreciated.

Thanks.

Re: ERROR: CLI open cursor error: Unable to retrieve error message.

SASKiwi — Wed, 01 Mar 2017 03:24:20 GMT

Please post an example SQL program log with this error. Do you get this error with all queries or just some?

Re: ERROR: CLI open cursor error: Unable to retrieve error message.

Venkat4 — Wed, 01 Mar 2017 03:32:49 GMT

This is the code I used to use, it worked fine. Now, throwing only this following error:ERROR: CLI open cursor error: Unable to retrieve error message.

When I just did a select * from the first table it worked fine. I am thinking the data type issue ? not sure how to fix....

PROC SQL;

Connect to odbc (dsn="SQLserver123" uid=&sqluser. password=&sqlpwd. readbuff=32767 );

CREATE table dfrd AS SELECT * FROM connection to odbc

(

SELECT CAST(a.Claim_Num AS CHAR(11)) AS Claim_ID,

d.Line_DT AS Claim_Dt,

d.Debit_Card_Num AS Card_Num,

c.Code_Desc AS FraudType,

Loss_Desc,

e.ClaimMinTran AS MinTran,

e.ClaimMaxTran AS MaxTran,

SUM(d.Claim_Amt) AS Grs_Fraud_Amt,

SUM(d.Claim_Amt - (Credit_Amt - Credit_Rev_Amt)) AS Net_Fraud_Amt

FROM DATUSER.Fraud a

LEFT JOIN DATUSER.Code_Table c ON a.Fraud_Type_CD = c.Code_Value

INNER JOIN DATUSER.Dispute_Claim d ON a.Claim_Num = d.Claim_Num

INNER JOIN (

SELECT Claim_Num,

MIN(Tran_DT_TM) AS ClaimMinTran,

MAX(Tran_DT_TM) AS ClaimMaxTran

FROM DATUSER.Dispute_Transaction a

GROUP BY Claim_Num) e

ON a.Claim_Num = e.Claim_Num

WHERE (c.Code_Type IS NULL OR c.Code_Type = 62)

AND d.Merge_IND <> 'M'

AND d.Category IN (3,4,7,9)

AND d.Line_DT >= &DTB.

GROUP BY CAST(a.Claim_Num AS CHAR(11)),

d.Line_DT,

d.Debit_Card_Num,

c.Code_Desc,

Loss_Desc,

e.ClaimMinTran,

e.ClaimMaxTran);

DISCONNECT FROM odbc;

QUIT;

Re: ERROR: CLI open cursor error: Unable to retrieve error message.

TomKari — Wed, 01 Mar 2017 10:02:04 GMT

Usually the most efficient process for this kind of problem is "inspired" trial and error / process of elimination.

1. Access your databse from your SAS server NOT using SAS facilities. Fix any problems.

2. Access your database in the simplest possible way using SAS. Fix any problems.

3. Access each individual table using SAS...

etc., building up to your actual query. At some point it will break, and you've found your problem. Then you can post a specific query, that we might be able to figure out.

Tom

Re: ERROR: CLI open cursor error: Unable to retrieve error message.

JBailey — Wed, 01 Mar 2017 13:00:08 GMT

Hi @Venkat4

Can you ask your DBAs for the version number of the new Microsoft SQL Server database? It is important to know if the new database is running on the Microsoft Azure platform. If it is, ask them which Azure service they are running - Azure SQL Database or Azure SQL Data Warehouse.

Best wishes,

Jeff

Re: ERROR: CLI open cursor error: Unable to retrieve error message.

Venkat4 — Wed, 01 Mar 2017 17:01:48 GMT

Thank you. The problem lied in card number, I had to cast the column to varchar like this cast(cardnum as varchar(16)) as cardnumber then this worked. I never thought this is causing the issue based on the error message.