LIBNAME Statement Specifics for Microsoft SQL Server

Overview

This section describes the LIBNAME statement as supported in SAS/ACCESS Interface to Microsoft SQL Server and includes examples. For details about this feature, see the LIBNAME statement.

Here is the LIBNAME statement syntax for accessing Microsoft SQL Server.

LIBNAME libref sqlsvr <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.

sqlsvr

specifies the SAS/ACCESS engine name for the Microsoft SQL Server interface.

connection-options

provide connection information and control how SAS manages the timing and concurrence of the connection to the DBMS. When you use the LIBNAME statement, you can connect to Microsoft SQL Server in many different ways. Specify only one of these methods for each connection because they are mutually exclusive.

USER=, PASSWORD=, and DATASRC=
COMPLETE=
NOPROMPT=
PROMPT=
REQUIRED=

Here is how these options are defined.

USER=<'>user-name<'>

lets you connect to Microsoft SQL Server with a user ID that is different from the default ID. USER= is optional. UID= is an alias for this option.

PASSWORD=<'>password<'>

specifies the Microsoft SQL Server password that is associated with your user ID. PASSWORD= is optional. PWD= is an alias for this option.

DATASRC=<'>SQL-Server-data-source<'>

specifies the Microsoft SQL Server data source to which you want to connect. For UNIX platforms, data sources must be configured by modifying the .ODBC.ini file. DSN= is an alias for this option that indicates that the connection is attempted using the ODBC SQLConnect API, which requires a data source name. You can also use a user ID and password with DSN=. This API is guaranteed to be present in all drivers.

Alias: DATABASE=, DB=, DSN=

COMPLETE=<'>SQL-Server-connection-options<'>

specifies connection options for your data source or database. Separate multiple options with a semicolon. When connection succeeds, the complete connection string is returned in the SYSDBMSG macro variable. If you do not specify enough correct connection options, you are prompted with a dialog box that displays the values from the COMPLETE= connection string. You can edit any field before you connect to the data source. See your driver documentation for more details.

NOPROMPT=<'>SQL-Server-connection-options<'>

specifies connection options for your data source or database. Separate multiple options with a semicolon. If you do not specify enough correct connection options, an error is returned. No dialog box is displayed to help you with the connection string.

PROMPT=<'> SQL-Server-connection-options<'>

specifies connection options for your data source or database. Separate multiple options with a semicolon. When connection succeeds, the complete connection string is returned in the SYSDBMSG macro variable. PROMPT= does not immediately try to connect to the DBMS. Instead, it displays a dialog box that contains the values that you entered in the PROMPT= connection string. You can edit values or enter additional values in any field before you connect to the data source.

REQUIRED=<'>SQL-Server-connection-options<'>

specifies connection options for your data source or database. Separate multiple options with a semicolon. When connection succeeds, the complete connection string is returned in the SYSDBMSG macro variable. If you do not specify enough correct connection options, a dialog box prompts you for the connection options. REQUIRED= lets you modify only required fields in the dialog box.

These Microsoft SQL Server connection options are not supported on UNIX.

BULKCOPY=
COMPLETE=
PROMPT=
REQUIRED=

LIBNAME-options

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

SAS/ACCESS LIBNAME Options for Microsoft SQL Server

Option	Default Value
ACCESS=	none
AUTHDOMAIN=	none
AUTOCOMMIT=	varies with transaction type
BL_LOG=	none
CONNECTION=	data-source specific
CONNECTION_GROUP=	none
CURSOR_TYPE=	DYNAMIC
DBCOMMIT=	1000 when inserting rows; 0 when updating rows
DBCONINIT=	none
DBCONTERM=	none
DBCREATE_TABLE_OPTS=	none
DBGEN_NAME=	DBMS
DBINDEX=	YES
DBLIBINIT=	none
DBLIBTERM=	none
DBMAX_TEXT=	1024
DBMSTEMP=	NO
DBNULLKEYS=	YES
DBPROMPT=	NO
DBSLICEPARM=	THREADED_APPS,2 or 3
DEFER=	NO
DELETE_MUTL_ROWS=	NO
DIRECT_EXE=	none
DIRECT_SQL=	YES
IGNORE_READ_ONLY_COLUMNS=	NO
INSERT_SQL=	YES
INSERTBUFF=	1
KEYSET_SIZE=	0
MUTLI_DATASRC_OPT=	NONE
PRESERVE_COL_NAMES=	see Naming Conventions for Microsoft SQL Server
PRESERVE_TAB_NAMES=	see Naming Conventions for Microsoft SQL Server
QUALIFIER=	none
QUERY_TIMEOUT=	0
QUOTE_CHAR=	none
READBUFF=	0
READ_ISOLATION_LEVEL=	RC (see Locking in the Microsoft SQL Server Interface)
READ_LOCK_TYPE=	ROW
REREAD_EXPOSURE=	NO
SCHEMA=	none
SPOOL=	YES
STRINGDATES=	NO
TRACE=	NO
TRACEFILE=	none
UPDATE_ISOLATION_LEVEL=	RC (see Locking in the Microsoft SQL Server Interface)
UDPATE_LOCK_TYPE=	ROW
UPDATE_MULT_ROWS=	NO
UPDATE_SQL=	driver-specific
USE_ODBC_CL=	NO
UTILCONN_TRANSIENT=	NO

Microsoft SQL Server LIBNAME Statement Examples

In following example, USER= and PASSWORD= are connection options.

libname mydblib sqlsvr user=testuser password=testpass;

In the following example, the libref MYDBLIB connects to a Microsoft SQL Server database using the NOPROMPT= option.

libname mydblib sqlsvr
   noprompt="uid=testuser;
   pwd=testpass;
   dsn=sqlservr;"
   stringdates=yes;

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