SAS/ACCESS Interface to OLE DB

LIBNAME Statement Specifics for OLE DB

This section describes the LIBNAME statement that SAS/ACCESS Interface to OLE DB supports and includes examples. For details about this feature, see Overview of the LIBNAME Statement for Relational Databases.

Here is the LIBNAME statement syntax for accessing OLE DB.

LIBNAME libref oledb <connection-options> <LIBNAME-options>;

Arguments

libref

specifies any SAS name that serves as an alias to associate SAS with a database, schema, server, or group of tables and views.

oledb

specifies the SAS/ACCESS engine name for the OLE DB interface.

connection-options

provide connection information and control how SAS manages the timing and concurrence of the connection to the data source. You can connect to a data source either by using OLE DB Services or by connecting directly to the provider. For details, see Connecting with OLE DB Services and Connecting Directly to a Data Provider.

These connection options are available with both connection methods. Here is how they are defined.

USER=<'>user-name<'>

lets you connect to an OLE DB data source with a user ID that is different from the default ID. The default is your user ID.

PASSWORD=<'>password<'>

specifies the OLE DB password that is associated with your user ID. If you do not wish to enter your OLE DB password in uncoded text, see PROC PWENCODE in Base SAS Procedures Guide for a method to encode it.

DATASOURCE=<'>data-source<'>

identifies the data source object (such as a relational database server or a local file) to which you want to connect.

PROVIDER=<'>provider-name<'>

specifies which OLE DB provider to use to connect to the data source. This option is required during batch processing. There is no restriction on the length of the provider-name. If the provider-name contains blank spaces or special characters, enclose it in quotation marks. If you do not specify a provider, an OLE DB Services dialog box prompts you for connection information. In batch mode, if you do not specify a provider the connection fails. If you are using the Microsoft Jet OLE DB 4.0 provider, specify PROVIDER=JET.

PROPERTIES=(<'>property-1<'>=<'>value-1<'> < . . . <'>property-n<'>=<'>value-n<'>>)

specifies standard provider properties that enable you to connect to a data source and to define connection attributes. If a property name or value contains embedded spaces or special characters, enclose the name or value in quotation marks. Use a blank space to separate multiple properties. If your provider supports a password property, that value cannot be encoded. To use an encoded password, use the PASSWORD= option instead. See your provider documentation for a list and description of all properties that your provider supports. No properties are specified by default.

PROVIDER_STRING=<'>extended-properties<'>

specifies provider-specific extended connection information, such as the file type of the data source. If the string contains blank spaces or special characters, enclose it in quotation marks. For example, the Microsoft Jet provider accepts strings that indicate file type, such as 'Excel 8.0'. The following example uses the Jet 4.0 provider to access the spreadsheet Y2KBUDGET.XLS. Specify the 'Excel 8.0' provider string so that Jet recognizes the file as an Excel 8.0 worksheet.

libname budget oledb provider=jet provider_string='Excel 8.0' 
                     datasource='d:\excel80\Y2Kbudget.xls';

OLEDB_SERVICES=YES | NO

determines whether SAS uses OLE DB Services to connect to the data source. Specify YES to use OLE DB Services or specify NO to use the provider to connect to the data source. When you specify PROMPT=YES and OLEDB_SERVICES=YES, you can set more options than you would otherwise be able to set by being prompted by the provider's dialog box. If OLEDB_SERVICES=NO, you must specify PROVIDER= first in order for the provider's prompt dialog boxes to be used. If PROVIDER= is omitted, SAS uses OLE DB Services, even if you specify OLEDB_SERVICES=NO. YES is the default. For Microsoft SQL Server data, if BULKLOAD=YES, then OLEDB_SERVICES= is set to NO. When OLEDB_SERVICES=YES and a successful connection is made, the complete connection string is returned in the SYSDBMSG macro variable.

PROMPT =YES | NO

determines whether one of these interactive dialog boxes displays to guide you through the connection process:

an OLE DB provider dialog box if OLEDB_SERVICES=NO and you specify a provider.
an OLE DB Services dialog box if OLEDB_SERVICES=YES or if you do not specify a provider.

The OLE DB Services dialog box is generally preferred over the provider's dialog box because the OLE DB Services dialog box enables you to set options more easily. If you specify a provider and set OLEDB_SERVICES=NO, the default is PROMPT=NO. Otherwise, the default is PROMPT=YES. If OLEDB_SERVICES=YES or if you do not specify a provider, an OLE DB Services dialog box displays even if you specify PROMPT=NO. Specify no more than one of the following options on each LIBNAME statement: COMPLETE=, REQUIRED=, PROMPT=. Any properties that you specify in the PROPERTIES= option are displayed in the prompting interface, and you can edit any field.

UDL_FILE=<'>path-and-file-name<'>

specifies the path and filename for a Microsoft universal data link (UDL). For example, you could specify UDL_FILE="C:\WinNT\profiles\me\desktop\MyDBLink.UDL" This option does not support SAS filerefs. SYSDBMSG is not set on successful completion. For more information, see Microsoft documentation about the Data Link API. This option overrides any values that are set with the INIT_STRING=, PROVIDER=, and PROPERTIES= options.

This connection option is available only when you use OLE DB Services.

INIT_STRING='property-1=value-1<...;property-n=value-n>'

specifies an initialization string, enabling you to bypass the interactive prompting interface yet still use OLE DB Services. (This option is not available if OLEDB_SERVICES=NO.) Use a semicolon to separate properties. After you connect to a data source, SAS returns the complete initialization string to the macro variable SYSDBMSG, which stores the connection information that you specify in the prompting window. You can reuse the initialization string to make automated connections or to specify connection information for batch jobs. For example, assume that you specify this initialization string:

init_string='Provider=SQLOLEDB;Password=dbmgr1;Persist
Security Info=True;User ID=rachel;Initial Catalog=users;
Data Source=dwtsrv1';

Here is what the content of the SYSDBMSG macro variable would be:

OLEDB:  Provider=SQLOLEDB;Password=dbmgr1;
Persist Security Info=True;User ID=rachel;
Initial Catalog=users;Data Source=dwtsrv1;

If you store this string for later use, delete the OLEDB: prefix and any initial spaces before the first listed option. There is no default value. However, if you specify a null value for this option, the OLE DB Provider for ODBC (MSDASQL) is used with your default data source and its properties. See your OLE DB documentation for more information about these default values. This option overrides any values that are set with the PROVIDER= and PROPERTIES= options. To write the initialization string to the SAS log, submit this code immediately after connecting to the data source: %put %superq(SYSDBMSG);

Only these connection options are available when you connect directly to a provider.

COMPLETE=YES | NO: specifies whether SAS attempts to connect to the data source without prompting you for connection information. If you specify COMPLETE=YES and the connection information that you specify in your LIBNAME statement is sufficient, then SAS makes the connection and does not prompt you for additional information. If you specify COMPLETE=YES and the connection information that you specify in your LIBNAME statement is not sufficient, the provider's dialog box prompts you for additional information. You can enter optional information as well as required information in the dialog box. NO is the default value. COMPLETE= is available only when you set OLEDB_SERVICES=NO and you specify a provider. It is not available in the SQL pass-through facility. Specify no more than one of these options on each LIBNAME statement: COMPLETE=, REQUIRED=, PROMPT=.
REQUIRED=YES | NO: specifies whether SAS attempts to connect to the data source without prompting you for connection information and whether you can interactively specify optional connection information. If you specify REQUIRED=YES and the connection information that you specify in your LIBNAME statement is sufficient, SAS makes the connection and you are not prompted for additional information. If you specify REQUIRED=YES and the connection information that you specify in your LIBNAME statement is not sufficient, the provider's dialog box prompts you for the required connection information. You cannot enter optional connection information in the dialog box. NO is the default value. REQUIRED= is available only when you set OLEDB_SERVICES=NO and you specify a provider in the PROVIDER= option. It is not available in the SQL pass-through facility Specify no more than one of these options on each LIBNAME statement: COMPLETE=, REQUIRED=, PROMPT=.

LIBNAME-options

define how SAS processes DBMS objects. Some LIBNAME options can enhance performance, while others determine locking or naming behavior. The following table describes the LIBNAME options for SAS/ACCESS Interface to OLE DB, with the applicable default values. For more detail about these options, see LIBNAME Options for Relational Databases.

SAS/ACCESS LIBNAME Options for OLE DB
Option	Default Value
ACCESS=	none
AUTHDOMAIN=	none
AUTOCOMMIT=	data-source specific
BL_KEEPIDENTITY=	NO
BL_KEEPNULLS=	YES
BL_OPTIONS=	not specified
BULKLOAD=	NO
CELLPROP=	VALUE
COMMAND_TIMEOUT=	0 (no time-out)
CONNECTION=	SHAREDREAD
CONNECTION_GROUP=	none
CURSOR_TYPE=	FORWARD_ONLY
DBCOMMIT=	1000 (inserting) or 0 (updating)
DBCONINIT=	none
DBCONTERM=	none
DBCREATE_TABLE_OPTS=	none
DBGEN_NAME=	DBMS
DBINDEX=	NO
DBLIBINIT=	none
DBLIBTERM=	none
DBMAX_TEXT=	1024
DBMSTEMP=	NO
DBNULLKEYS=	YES
DEFER=	NO
DELETE_MULT_ROWS=	NO
DIRECT_EXE=	none
DIRECT_SQL=	YES
IGNORE_ READ_ONLY_COLUMNS=	NO
INSERT_SQL=	data-source specific
INSERTBUFF=	1
MULTI_DATASRC_OPT=	NONE
PRESERVE_COL_NAMES=	see Naming Conventions for OLE DB
PRESERVE_TAB_NAMES=	see Naming Conventions for OLE DB
QUALIFIER=	none
QUALIFY_ROWS=	NO
QUOTE_CHAR=	not set
READBUFF=	1
READ_LOCK_TYPE=	see Locking in the OLE DB Interface
READ_ISOLATION_LEVEL=	not set (see Locking in the OLE DB Interface)
REREAD_EXPOSURE=	NO
SCHEMA=	none
SPOOL=	YES
SQL_FUNCTIONS=	none
STRINGDATES=	NO
UPDATE_ISOLATION_LEVEL=	not set (see Locking in the OLE DB Interface)
UPDATE_LOCK TYPE=	ROW
UPDATE_MULT_ROWS=	NO
UTILCONN_TRANSIENT=	NO

Connecting with OLE DB Services

By default, SAS/ACCESS Interface to OLE DB uses OLE DB services because this is often the fastest and easiest way to connect to a data provider.

OLE DB Services provides performance optimizations and scaling features, including resource pooling. It also provides interactive prompting for the provider name and connection information.

Assume that you submit a simple LIBNAME statement, such as this one:

libname mydblib oledb;

SAS directs OLE DB Services to display a dialog box that contains tabs where you can enter the provider name and connection information.

After you make a successful connection using OLE DB Services, you can retrieve the connection information and reuse it in batch jobs and automated connections. For more information, see the connection options INIT_STRING= and OLEDB_SERVICES=.

Connecting Directly to a Data Provider

To connect to a data source, SAS/ACCESS Interface to OLE DB requires a provider name and provider-specific connection information such as the user ID, password, schema, or server name. If you know all of this information, you can connect directly to a provider without using OLE DB Services.

If you are connecting to Microsoft SQL Server and you are specifying the SAS/ACCESS option BULKLOAD=YES, you must connect directly to the provider by specifying the following information:

the name of the provider (PROVIDER=)
that you do not want to use OLE DB Services (OLEDB_SERVICES=NO)
any required connection information

After you connect to your provider, you can use a special OLE DB query called PROVIDER_INFO() to make subsequent unprompted connections easier. You can submit this special query as part of a PROC SQL query in order to display all available provider names and properties. For an example, see Examples of Special OLE DB Queries.

If you know only the provider name and you are running an interactive SAS session, you can be prompted for the provider's properties. Specify PROMPT=YES to direct the provider to prompt you for properties and other connection information. Each provider displays its own prompting interface.

If you run SAS in a batch environment, specify only USER=, PASSWORD=, DATASOURCE=, PROVIDER=, PROPERTIES=, and OLEDB_SERVICES=NO.

OLE DB LIBNAME Statement Examples

In the following example, the libref MYDBLIB uses the SAS/ACCESS OLE DB engine to connect to a Microsoft SQL Server database.

libname mydblib oledb user=username password=password 
   datasource=dept203  provider=sqloledb properties=('initial catalog'=mgronly);
proc print data=mydblib.customers; 
   where state='CA';
run;

In the following example, the libref MYDBLIB uses the SAS/ACCESS engine for OLE DB to connect to an Oracle database. Because prompting is enabled, you can review and edit the user, password, and data source information in a dialog box.

libname mydblib oledb user=username password=password datasource=v2o7223.world 
                      provider=msdaora prompt=yes; 

proc print data=mydblib.customers;
   where state='CA';
run;

In the following example, you submit a basic LIBNAME statement, so an OLE DB Services dialog box prompts you for the provider name and property values.

libname mydblib oledb;

The advantage of being prompted is that you do not need to know any special syntax to set the values for the properties. Prompting also enables you to set more options than you might when you connect directly to the provider (and do not use OLE DB Services).

Top of Page