SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

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

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.


Accepted Solutions
Solution
‎03-01-2017 11:58 AM
PROC Star
Posts: 1,167

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

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

View solution in original post


All Replies
Super User
Posts: 3,260

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

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

Contributor
Posts: 40

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

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;

Solution
‎03-01-2017 11:58 AM
PROC Star
Posts: 1,167

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

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

Contributor
Posts: 40

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

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.
SAS Employee
Posts: 215

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1121 views
  • 2 likes
  • 4 in conversation