topic Re: Import Error when Importing from Access in SAS Enterprise Guide

Import Error when Importing from Access

Mayanwolfe — Wed, 06 Aug 2014 17:45:09 GMT

Hello,

I am running SAS Enterprise Guide version: 5.100.0.13219 (32-bit)

Running in WOW64 (32-bit subsystem): Yes

Running as a 64 bit process: No

and Access 2010 version 14.0.6023.1000 (32-bit)

I am attempting to import a table (1.1 million rows, 35 columns) from a database in Access to SAS EG. Every time I use File > Import Data and select the file I get an error that "SAS has encountered a problem and needs to close." Below is the error. More details can be provided if needed. Needless to say, the import fails. What is causing this, and how can I fix it/is there an alternative method? Currently we have been forced to export the table from Access to Excel in two pieces, then import it to SAS and stitch it back together. It's inefficient and time-consuming, so any help would be greatly appreciated! Thank you.

-------------------------- Technical Information Follows --------------------------

Exception Details:

----------------------------------------

Exception type: System.IO.IOException

Message: No more data is available.

Source: mscorlib

Target Site: Win32Error

Re: Import Error when Importing from Access

SASKiwi — Fri, 08 Aug 2014 01:26:26 GMT

Do you have the SAS product SAS/ACCESS to PC Files? In EG in Server List, check in properties for the SAS products installed. If you do then you can run a SAS program/EG process to try importing the data.

Re: Import Error when Importing from Access

Kurt_Bremser — Fri, 08 Aug 2014 06:38:53 GMT

Do you get the error immediately or does it take some time?

Re: Import Error when Importing from Access

Mayanwolfe — Fri, 08 Aug 2014 12:33:04 GMT

Hi Kurt,

I receive the error immediately upon selecting the file to be imported. Proc Import also fails, though it does not cause a fatal error.

Re: Import Error when Importing from Access

Kurt_Bremser — Fri, 08 Aug 2014 12:42:37 GMT

Could it be that you are running out of space for the work library? It may be that EG tries to send a piece of data (converted into a SAS-readable format by MS DAO) to the workspace server so that SAS can take a look at it.

Another possible failure point is of course the MS DAO, which may not be happy with the data it reads from Access.

Consider a call to TS if nobody here has another idea.

Re: Import Error when Importing from Access

Mayanwolfe — Fri, 08 Aug 2014 12:42:49 GMT

Hi SASKiwi,

It appears the server does have ACCESS to PC Files installed, but when I try Proc Import that fails as well. Here is I have tried to run:

PROC IMPORT OUT=fakeserver.cxb_iha_ops_2014_08_07

DATATABLE='Member Level Data'

DBMS=ACCESS REPLACE;

DATABASE="&Q:\fakepath\Member Level Detail1.accdb";

USEDATE=YES;

SCANTIME=NO;

DBSASLABEL=NONE;

RUN;

What's the problem with my code? I'm still getting familiar with SAS, so I welcome corrections/alternatives. Thank you!

Re: Import Error when Importing from Access

Kurt_Bremser — Fri, 08 Aug 2014 12:49:44 GMT

To diagnose your code, you absolutely need to post the log that contains the error message(s). Since I'm closing shop for the weekend, it may be some time before I can help you further.

Re: Import Error when Importing from Access

Mayanwolfe — Fri, 08 Aug 2014 12:58:57 GMT

Apologies, Kurt. I meant to post the log and forgot.

1 The SAS System 08:26 Friday, August 8, 2014

1 rsubmit task1;

NOTE: Remote submit to TASK1 commencing.

1475 %put NOTE: Using grid node &SYSHOSTNAME to run task.;

NOTE: Using grid node isbsas09 to run task.

1476 %let _EGRC=0;

1477 ;*';*";*/;quit;run;

1478 OPTIONS PAGENO=MIN;

1479 %put NOTE: The current task will be submitted to the grid due to the server configuration policy.;

NOTE: The current task will be submitted to the grid due to the server configuration policy.

1480 %LET _CLIENTTASKLABEL='Program1';

1481 %LET _CLIENTPROJECTPATH='/sascode/myuserID/IHAWeeklyUpdate.egp';

1482 %LET _CLIENTPROJECTNAME='IHAWeeklyUpdate.egp';

1483 %LET _SASPROGRAMFILE=;

1484

1485 ODS _ALL_ CLOSE;

1486 OPTIONS DEV=ACTIVEX;

2 The SAS System 08:26 Friday, August 8, 2014

1487 %let sasworklocation="&_eg_gridworkpath/";

1488 GOPTIONS XPIXELS=0 YPIXELS=0;

1489 FILENAME EGSR "&_eg_gridworkpath/%sysfunc(uuidgen(0))_eggen";

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

1490 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue

1490! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/x86/SASEnterpriseGuide/5.1/Styles/HtmlBlue.css") NOGTITLE NOGFOOTNOTE

1490! GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");

1491

1492 GOPTIONS ACCESSIBLE;

1493 PROC IMPORT OUT=servername.cxb_iha_ops_2014_08_07

1494 DATATABLE='Member Level Data'

1495 DBMS=ACCESS REPLACE;

ERROR: DBMS type ACCESS not valid for import.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.01 seconds

user cpu time 0.01 seconds

system cpu time 0.00 seconds

memory 44.06k

OS Memory 14372.00k

Timestamp 08/08/2014 08:39:18 AM

Page Faults 0

Page Reclaims 25

Page Swaps 0

Voluntary Context Switches 6

Involuntary Context Switches 5

Block Input Operations 0

Block Output Operations 0

WARNING: Apparent symbolic reference Q not resolved.

1496 DATABASE="&Q:\fakepath\Member Level Detail\Member Level Detail1.accdb"

1496! ;

1497 USEDATE=YES;

1498 SCANTIME=NO;

1499 DBSASLABEL=NONE;

1500 RUN;

1501

1502 GOPTIONS NOACCESSIBLE;

1503 %LET _CLIENTTASKLABEL=;

1504 %LET _CLIENTPROJECTPATH=;

1505 %LET _CLIENTPROJECTNAME=;

1506 %LET _SASPROGRAMFILE=;

1507

1508 ;*';*";*/;quit;run;

1509 ODS _ALL_ CLOSE;

1510

1511

1512 QUIT; RUN;

1513 %sysrput _EGRCGRID=&_EGRC;

NOTE: Remote submit to TASK1 complete.

2 %let _EGRC=&_EGRCGRID;

Hope that helps. The items in strikethrough (user ID, server name, filepath), have been altered for confidentiality.

Re: Import Error when Importing from Access

ChrisHemedinger — Fri, 08 Aug 2014 13:05:58 GMT

From the error and from looking at other tracks, I think that this is failing when EG is trying to decide whether there is a Microsoft data access engine (ACE) driver installed.

It's looking for the ACE footprint in the Windows registry. For this 32-bit process on your 64-bit machine, this would be in:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall

Specifically, it's looking to see if you have MS Access:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\{90150000-0015-0409-0000-0000000FF1CE}

Or at least the ACE driver:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\{90150000-00D1-0409-0000-0000000FF1CE}

But this step seems to be failing while simply getting the list, implying that you might not even have the rights to query this. Perhaps there is a security package that is getting in the way? Can you view these values in RegEdit?

Chris

Re: Import Error when Importing from Access

ChrisHemedinger — Fri, 08 Aug 2014 13:20:03 GMT

The PROC IMPORT step is running in your SAS Grid environment on a UNIX/Linux platform. This session probably does not have access to the path where your MDB sits, and even if it did the SAS session would not be able to process this import of a Windows database directly. Instead, it would require a PC Files Server specification and use of the DBMS=ACCESSCS option to trigger it. I'm guessing that you don't have all of that set up, but if you do a SAS admin would need to tell you exactly what the PC File Server host and port specification should be.

Another possibility to get the data into the session via EG -- you could try File->Open->From ODBC. Then you could use the Microsoft Access ODBC driver to open the database file. However, for this large table it might be slow -- it's not usually the method we recommend.

Chris