SQL Server - Insufficient Memory

ftenorio — Wed, 26 Jun 2019 08:50:13 GMT

Hello,

I’m running a SAS 9.4 M5 under Linux and I got an error message while extracting data from a SQLSVR libname (note that I added a obs=1, with obs=0 it works).

data error_test;
   set clmcntr2.ccx_historysnapshot_pv (obs=1);
run;
ERROR: The SAS System stopped processing this step because of insufficient memory.

Then I decided to check the DB configuration:

proc sql;
connect using clmcntr2 as temp;
select * from connection to temp
(select * from master.sys.configurations);
disconnect from temp;
quit;

I get the same error message.

Here is the table’s description:

create table CLMCNTR2.ccx_historysnapshot_pv  (

   LoadCommandID num format=20. informat=20. label='LoadCommandID',

   PublicID char(20) format=$20. informat=$20. label='PublicID',

   ClaimNumber char(40) format=$40. informat=$40. label='ClaimNumber',

   PreviousAssignedManager char(1024) format=$1024. informat=$1024. label='PreviousAssignedManager',

   CreateTime num format=DATETIME26.7 informat=DATETIME26.7 label='CreateTime',

   AssignedManagerReference char(1024) format=$1024. informat=$1024. label='AssignedManagerReference',

   PreviousAssignedUser char(1024) format=$1024. informat=$1024. label='PreviousAssignedUser',

   AssignedUserReference char(1024) format=$1024. informat=$1024. label='AssignedUserReference',

   PreviousAssignedManagerName char(1024) format=$1024. informat=$1024. label='PreviousAssignedManagerName',

   UpdateTime num format=DATETIME26.7 informat=DATETIME26.7 label='UpdateTime',

   ClaimId num format=20. informat=20. label='ClaimId',

   AssignedUser num format=20. informat=20. label='AssignedUser',

   ID num format=20. informat=20. label='ID',

   PreviousAssignedUserName char(1024) format=$1024. informat=$1024. label='PreviousAssignedUserName',

   AssignedManager char(1024) format=$1024. informat=$1024. label='AssignedManager',

   AssignedProducer char(1024) format=$1024. informat=$1024. label='AssignedProducer',

   CreateUserID num format=20. informat=20. label='CreateUserID',

   AssignedUserName char(1024) format=$1024. informat=$1024. label='AssignedUserName',

   BeanVersion num format=11. informat=11. label='BeanVersion',

   ArchivePartition num format=20. informat=20. label='ArchivePartition',

   Retired num format=20. informat=20. label='Retired',

   AssignedManagerName char(1024) format=$1024. informat=$1024. label='AssignedManagerName',

   AssignedProducerName char(1024) format=$1024. informat=$1024. label='AssignedProducerName',

   UpdateUserID num format=20. informat=20. label='UpdateUserID',

   PreviousAssignedUserGroup char(1024) format=$1024. informat=$1024. label='PreviousAssignedUserGroup',

   AssignedGroup char(1024) format=$1024. informat=$1024. label='AssignedGroup',

   Type num format=11. informat=11. label='Type',

   PreviousAssignedUserReference char(1024) format=$1024. informat=$1024. label='PreviousAssignedUserReference',

   PrevAssignedManagerReference char(1024) format=$1024. informat=$1024. label='PrevAssignedManagerReference'

  );

If I drop at least 3 columns (char(1024)), I can generate 2 dataset then join them. The goal is to understand why this behavior is happening, as the same codes work in Production and worked before in Development.

The same error occurs via Proc SQL pass-through.

options sastrace=',,d,d' sastraceloc=saslog nostsuffix;

proc sql;
connect using clmcntr2 as temp;
Create table ccx_historysnapshot_pv as
select * from connection to temp
(select top 5 * from ccx_historysnapshot_pv);
disconnect from temp;
quit;

(...)

ACCESS ENGINE: DBMS column name: PrevAssignedManagerReference
ACCESS ENGINE: SAS variable name:  PrevAssignedManagerReference
ACCESS ENGINE: SAS [in]format:  $
ACCESS ENGINE: Exiting ptnam
ACCESS ENGINE: Exiting describ with rc=0X00000000
ACCESS ENGINE: Entering fetch
ERROR: The SAS System stopped processing this step because of insufficient memory.

If you any ideas, it would be very nice 🙂

Thanks,

Fernando.

Re: SQL Server - Insufficient Memory

ftenorio — Wed, 26 Jun 2019 13:49:32 GMT

Hello,

For information: the SAS Admin told me that, for this given libname, the buffering was limited. He increased this limit and the code is now working.

Thanks,

Fernando.

topic Re: SQL Server - Insufficient Memory in New SAS User

SQL Server - Insufficient Memory

Re: SQL Server - Insufficient Memory