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.
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
Please post an example SQL program log with this error. Do you get this error with all queries or just some?
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;
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.