BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Venkat4
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

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

5 REPLIES 5
SASKiwi
PROC Star

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

Venkat4
Quartz | Level 8

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;

TomKari
Onyx | Level 15

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

Venkat4
Quartz | Level 8
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.
JBailey
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 22111 views
  • 3 likes
  • 4 in conversation