Thursday, June 23, 2016

SAS error "insufficient memory" on remote queries with wide rows

SAS can give the error The SAS System stopped processing this step because of insufficient memory when querying a single, wide row from a remote SQL Server. The following code fully demonstrates the problem and shows a workaround. Also, I eliminate the explanation that SAS data sets in general do not support rows this wide.


proc sql;
 /* Connect to Microsoft SQL Server */
 connect using sbox;

 execute (
  /* Create the table */
  create table dbo.zzz_varchar_max (
   id int,
   txt1 varchar(max),
   txt2 varchar(max)
  );

  /* Insert a single row */
  insert into zzz_varchar_max values (1, 'foo', 'bar');
 ) by sbox;
quit;

/* This step triggers the error */
data _null_;
 set sbox.zzz_varchar_max;
run;

/* This step does NOT trigger the error */
data _null_;
 set sbox.zzz_varchar_max(drop=txt2);
run;
/* ERROR: The SAS System stopped processing this step because of insufficient memory. */

/* This step inspects the metadata. SAS considers each character column as having
   the width 32767, which is the maximum string size for a SAS data set according to
   https://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/viewer.htm#a001336069.htm
*/
proc contents data=sbox.zzz_varchar_max;
run;

/* This step shows that SAS allows creating a data set that is even 
   wider than the query that fails, so the error isn't a fundamental limitation
   of SAS. */

data wide;
 id =1;
 format txt1 txt2 txt3 $32767.;
 txt1='foo';
 txt2='bar';
 txt3='';
run;

If the data set has a single column with NVARCHAR(MAX) or VARCHAR(MAX), there is no error. It happens only when there are (at least) two such wide columns.

Another workarounds include: use the KEEP option on the data set to KEEP only one of the wide columns, use a PROC SQL statement to query only one of the columns, or use a remote SQL query (maybe with SUBSTR) to truncate the columns.

Another workaround is to switch from the modern Microsoft ODBC driver (driver=ODBC Driver 11 for SQL Server) to the ancient driver (driver=sql server) by changing the ODBC connection string or DSN.

I tested with SAS 9.4 TS1M3 32-bit, Microsoft SQL Server 2012 (11.0 SP2), and the ODBC Driver 11 for SQL Server (2014.120.2000.08).

No comments:

Post a Comment