Date:         Wed, 1 Nov 2006 18:55:13 -0500
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: Proc Import multiple connections to MS Access97 database
              failure

On Wed, 1 Nov 2006 09:49:27 -0800, Choate, Paul@DDS <pchoate@DDS.CA.GOV> wrote:

>Readers -
>
>I found using a single libname statement with an Access engine to
>connect, reading the tables, and then closing the connection works fine.

That's a good idea in any case, because you can use the engine to feed a
step which actually does something (eg, PROC SORT) instead of passing all of
the data an extra time just to get it into a native SAS table.

>
>Thanks.
>
>Paul Choate
>DDS Data Extraction
>(916) 654-2160
>
>-----Original Message-----
>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
>Choate, Paul@DDS
>Sent: Wednesday, November 01, 2006 9:16 AM
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: Proc Import multiple connections to MS Access97 database
>failure
>
>Hi SAS-Lers -
>
>SASv913 on XP connecting to a MS Access97 database on Novell network
>drive.
>
>We have a regular job that reads tables off an MS Access97 database on
>our network.  The job ran fine until last month, when the first table
>came off fine, but then the following tables didn't:
>
>5    PROC IMPORT OUT= SIRS.LastUpdate
>6                DATATABLE= "LastUpdate"
>7                DBMS=ACCESS97 REPLACE;
>8         DATABASE="J:\SIRS\SIRS.mdb"; run;
>
>NOTE: SIRS.LASTUPDATE was successfully created.
>9
>10
>11   PROC IMPORT OUT= SIRS.LU_Incident_Location
>12               DATATABLE= "LU Incident Location"
>13               DBMS=ACCESS97 REPLACE;
>14        DATABASE="J:\SIRS\SIRS.mdb"; run;
>
>ERROR: Unable to import, file J:\SIRS\SIRS.mdb does not exist.
>NOTE: The SAS System stopped processing this step because of errors.
>
>
>I found that if I used a 30 second sleep on SAS then the table becomes
>available:
>
>24   PROC IMPORT OUT= SIRS.LastUpdate
>25               DATATABLE= "LastUpdate"
>26               DBMS=ACCESS97 REPLACE;
>27        DATABASE="J:\SIRS\SIRS.mdb"; run;
>
>NOTE: SIRS.LASTUPDATE was successfully created.
>
>28   data _null_; x=sleep(30); run;
>
>NOTE: DATA statement used (Total process time):
>      real time           30.17 seconds
>      cpu time            0.07 seconds
>
>29
>30   PROC IMPORT OUT= SIRS.LU_Incident_Location
>31               DATATABLE= "LU Incident Location"
>32               DBMS=ACCESS97 REPLACE;
>33        DATABASE="J:\SIRS\SIRS.mdb"; run;
>
>NOTE: SIRS.LU_INCIDENT_LOCATION was successfully created.
>
>
>I had the database's DBA compact the database and the job would run
>without the sleep command in SAS. The database compacted by 50% or so
>from 700MB to 350MB.
>
>This month I'm getting the problem.  Is there a way around this in SAS,
>not relying on the database being compacted, and not sleeping SAS for 30
>seconds between each import?  We read 20+ tables and I'd prefer not to
>add 10 minutes wall-clock time to this process.
>
>I don't see any SAS Notes or SAS-L posts on this problem.  Any ideas
>appreciated.
>
>Paul Choate
>DDS Data Extraction
>(916) 654-2160