Good evening,
I'm receiving the following error and wanted to know how I would handle in SAS.
For SQL Server I would have to use SELECT COUNT_BIG(*) is there an equivalent?
select count(*) into :sourcecnt from LipSDD00.Fundamental;
CLI open cursor error: [SAS/ACCESS to SQL Server][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Arithmetic
overflow error converting expression to data type int.
Regards,
Jonathan
You could use a passthrough...
proc sql;
connect using LipSDD00 as cLIP;
select
recs
into
:sourcecnt
from connection to cLIP (
select count_big(*) from Fundamental
);
quit;
You might need to use CAST() in the pass thru expression, otherwise chances are that you still encounter the same limitation.
I would like SAS just to ignore these errors and continue on if I could for a quick work around until another solution is found.
Any suggestions ?? How do I go about the SAS System to continue processing?
The SAS System stopped processing this step because of errors.
If this is DI Studio generated code open a track to SAS tech support and demand a fix!
Assuming you have a libname for the SQL Server table, you could use this brute force approach as a last-ditch solution:
data _null_;
set sqllib.fundamental end=finish;
if finish then call symput('sourcecnt',put(_n_,best20.));
run;
Hi jdmarshg,
See my reply to the other thread you mentioned above for more details on this macro:
Re: D.I. Studio - etls_recordCheck macro issues
You can disable this macro as I note in this thread or change the code it generates to suit your needs. SELECT COUNT_BIG(*) seems like Transact-SQL specific to SQL Server 2008 and higher, and would only be needed for tables with more than 2,147,483,647 rows - not unheard of, but certainly uncommon.
Mike F.
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.