LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous (more recent) messageNext (less recent) messagePrevious (more recent) in topicNext (less recent) in topicPrevious (more recent) by same authorNext (less recent) by same authorPrevious page (July 2007, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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


Back to: Top of message | Previous page | Main SAS-L page