DBLOAD Procedure Specifics for ODBC

Overview

See DBLOAD Procedure for general information about this feature. ODBC examples are available.

SAS/ACCESS Interface to ODBC supports all DBLOAD procedure statements (except ACCDESC=) in batch mode. Here are the DBLOAD procedure specifics for ODBC:

The DBLOAD step DBMS= value is ODBC.
Here are the database description statements that PROC DBLOAD uses:

DSN= <'>ODBC-data-source<'>;

specifies the name of the data source in which you want to store the new ODBC table. The data-source is limited to eight characters.

The data source that you specify must already exist. If the data source name contains the _, $, @, or # special character, you must enclose it in quotation marks. The ODBC standard recommends against using special characters in data source names, however.

USER= <'>user name<'>;

lets you connect to an ODBC database with a user ID that is different from the default ID. USER= is optional in ODBC. If you specify USER=, you must also specify PASSWORD=. If USER= is omitted, your default user ID is used.

PASSWORD=<'>password<'>;

specifies the ODBC password that is associated with your user ID.

PASSWORD= is optional in ODBC because users have default user IDs. If you specify USER=, you must specify PASSWORD=.

Note: If you do not want to enter your ODBC password in uncoded text on this statement, see PROC PWENCODE in the Base SAS Procedures Guide for a method to encode it.

BULKCOPY= YES|NO;

determines whether SAS uses the Microsoft Bulk Copy facility to insert data into a DBMS table (Microsoft SQL Server only). The default value is NO.

The Microsoft Bulk Copy (BCP) facility lets you efficiently insert rows of data into a DBMS table as a unit. As the ODBC interface sends each row of data to BCP, the data is written to an input buffer. When you have inserted all rows or the buffer reaches a certain size (the DBCOMMIT= data set option determines this), all rows are inserted as a unit into the table, and the data is committed to the table.

You can also set the DBCOMMIT=n option to commit rows after every n insertions.

If an error occurs, a message is written to the SAS log, and any rows that have been inserted in the table before the error are rolled back.

Note: BULKCOPY= is not supported on UNIX.
Here is the TABLE= statement:

TABLE= <authorization-id.>table-name;

identifies the table or view that you want to use to create an access descriptor. The TABLE= statement is required.

The authorization-id is a user ID or group ID that is associated with the table.
Here is the NULLS statement:
NULLS variable-identifier-1 =Y|N|D < . . . variable-identifier-n =Y|N|D >;
enables you to specify whether the columns that are associated with the listed SAS variables allow NULL values. By default, all columns accept NULL values.

The NULLS statement accepts any one of these three values:

Y – specifies that the column accepts NULL values. This is the default.

N – specifies that the column does not accept NULL values.

D – specifies that the column is defined as NOT NULL WITH DEFAULT.

Examples

This example creates a new ODBC table, TESTUSER.EXCHANGE, from the DLIB.RATEOFEX data file. You must be granted the appropriate privileges in order to create new ODBC tables or views.

proc dbload dbms=odbc data=dlib.rateofex;
   dsn=sample;
   user='testuser';
   password='testpass';
   table=exchange;
   rename fgnindol=fgnindollars
          4=dollarsinfgn;
   nulls updated=n fgnindollars=n
         dollarsinfgn=n country=n;
   load;
run;

This next example sends only an ODBC SQL GRANT statement to the SAMPLE database and does not create a new table. Therefore, the TABLE= and LOAD statements are omitted.

proc dbload dbms=odbc;
   user='testuser';
   password='testpass';
   dsn=sample;
   sql grant select on testuser.exchange
      to dbitest;
run;