Date:         Tue, 10 Jul 2007 07:36:41 -0400
Reply-To:     Jack Clark <JClark@CHPDM.UMBC.EDU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Clark <JClark@CHPDM.UMBC.EDU>
Subject:      SAS to SQL Server - Error with Bulkload parameter
Content-Type: text/plain; charset="us-ascii"

I posted this last Friday - a very slow day for SAS-L.  I thought I
would put it out there again now that everyone may be back from their
holiday.

 

Thanks.

 

 

Jack Clark

Research Analyst

Center for Health Program Development and Management

University of Maryland, Baltimore County

________________________________

From: Jack Clark 
Sent: Friday, July 06, 2007 11:36 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SAS to SQL Server - Error with Bulkload parameter

 

Hello,

 

A couple of weeks ago I read some posts on SAS-L about using the
bulkload method of moving data from SAS to SQL Server.  It got me
interested in doing some testing with a similar process we have here.

 

We were currently using PROC DBLOAD to move the data, but I have been
testing the libname statement with ODBC and the bulkload parameter.  We
have 2 different servers that we write data to.  I was successful in
writing to one of them with the bulkload parameter.  The other, I can
write to with the libname statement, but not when I use the bulkload=yes
parameter.  What are some possible explanations of this?  (I am a total
beginner with SQL Server and it is managed by our IT staff - not me).

 

 

In every instance, the old table in SQL Server is deleted and a new
version replaces it.  There is no "inserting" of records into an
existing table.

 

I am using SAS 9.1 for Windows.  The libname statement I am using is
below:

 

libname mcapdb odbc noprompt="dsn=mcocap_tmp;" bulkload=yes;

 

The code I am using: 

 

* delete SQL Server tables ;   (This delete always works OK on both
servers)

proc sql;

   drop table mcapdb.mcos;

quit;

 

 

 

* send new tables to SQL Server ;

data work.mcos work.pacs;

   set mcos_tmp;

label=translate(label,' ',',');

if index(label,'PAC') then output work.pacs;

   else output work.mcos;

run;

 

data mcapdb.mcos (dbtype=(label='char(30)'));

   set work.mcos;

run;

 

This is the log when I attempt to use the bulkload=yes parameter on the
2nd database server:

 

 

28

29

30   data mcapdb.mcos (dbtype=(label='char(30)'));

31      set work.mcos;

32   run;

 

NOTE: SAS variable labels, formats, and lengths are not written to DBMS
tables.

ERROR: CLI execute error: [Microsoft][ODBC SQL Server
Driver][DBNETLIB]ConnectionRead (recv()). :

       [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network
error. Check your network

       documentation.

NOTE: The data step has been abnormally terminated.

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

NOTE: There were 1 observations read from the data set WORK.MCOS.

WARNING: The data set MCAPDB.mcos may be incomplete.  When this step was
stopped there were 0

         observations and 1 variables.

ERROR: ROLLBACK issued due to errors for data set MCAPDB.mcos.DATA.

NOTE: DATA statement used (Total process time):

      real time           0.20 seconds

      cpu time            0.01 seconds

 

 

Any feedback would be appreciated.  If I need to supply additional
information, let me know.  Thanks.

 

 

Jack Clark

Research Analyst

Center for Health Program Development and Management

University of Maryland, Baltimore County

410-455-6256