This section describes the LIBNAME statement as supported in SAS/ACCESS Interface to Microsoft SQL Server. For general information about this feature, see LIBNAME Statement for Relational Databases.
Here is the LIBNAME statement syntax for accessing Microsoft SQL Server.
For general information about the LIBNAME statement that is not specific to SAS/ACCESS, see LIBNAME Statement in SAS Global Statements: Reference.
specifies any SAS name that serves as an alias to associate SAS with a database, schema, server, or group of tables and views.
sqlsvrspecifies the SAS/ACCESS engine name for the Microsoft SQL Server interface.
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.
Here is how these options are defined.
lets you connect to Microsoft SQL Server with a user ID that is different from the default ID.
Alias: UID=
Usage: optional
specifies the Microsoft SQL Server password that is associated with your user ID.
Alias: PWD=
Usage: optional
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. This option 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=
specifies connection options for your 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 database. See your driver documentation for more details.
Restriction: This option is not available on UNIX platforms.
specifies connection options for your 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.
Aliases: CONNECTSTR=, CONNECT_STRING=
specifies connection options for your 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 in SAS Display Manager 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 database.
Restriction: This option is not available on UNIX platforms.
specifies connection options for your 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.
Restriction: This option is not available on UNIX platforms.
specify 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. This table also identifies LIBNAME options that are valid in the CONNECT statement in the SQL procedure. For details, see LIBNAME Options for Relational Databases.
|
Option |
Default Value |
Valid in CONNECT |
|---|---|---|
|
none |
|
|
|
none |
|
|
|
NO |
● |
|
|
none |
● |
|
|
none |
● |
|
|
NO |
● |
|
|
temporary file directory that is specified by the UTILLOC= system option |
● |
|
|
YES |
● |
|
|
bell character (ASCII 0x07) |
● |
|
|
dfs.core.windows.net |
● |
|
|
none |
● |
|
|
none |
● |
|
|
none |
● |
|
|
none |
● |
|
|
0 |
● |
|
|
none |
● |
|
|
none |
● |
|
|
60 |
● |
|
|
NO |
● |
|
|
NO |
● |
|
|
NO |
● |
|
|
UNIQUE when the data source supports only one cursor per connection; otherwise, SHAREDREAD |
● |
|
|
none |
● |
|
|
DYNAMIC |
● |
|
|
NO |
● |
|
|
1000 (when inserting rows), 0 (when updating rows) |
|
|
|
none |
● |
|
|
none |
● |
|
|
none |
|
|
|
DBMS |
● |
|
|
NO |
|
|
|
none |
|
|
|
none |
|
|
|
1024 |
● |
|
|
NO |
|
|
|
YES |
|
|
|
YES |
|
|
|
NO |
● |
|
|
NONE |
|
|
|
NO |
● |
|
|
NO |
|
|
|
none |
|
|
|
YES |
|
|
|
NO |
|
|
|
YES |
|
|
|
Calculated automatically based on row size (if INSERT_SQL=YES) |
|
|
|
0 |
|
|
|
0 |
● |
|
|
NONE |
|
|
|
none |
|
|
|
|
||
|
NO |
● |
|
|
YES |
● |
|
|
|
||
|
none |
|
|
|
0 |
● |
|
|
none |
|
|
|
0 |
● |
|
|
● |
||
|
ROW |
● |
|
|
NO |
● |
|
|
NO |
|
|
|
none |
|
|
|
YES |
|
|
|
none |
|
|
|
none |
|
|
|
none |
|
|
|
NO |
● |
|
|
NO |
● |
|
|
none |
● |
|
|
|
||
|
ROW |
● |
|
|
NO |
|
|
|
driver-specific |
|
|
|
NO |
● |
|
|
NO |
|
|
|
NO |
● |
In this example, USER= and PASSWORD= are connection options.
libname mydblib sqlsvr user=myusr1 password=mypwd1 dsn=sqlserver;In this next example, the libref MYDBLIB connects to a Microsoft SQL Server database using the NOPROMPT= option.
libname mydblib sqlsvr noprompt="uid=myusr1;
pwd=mypwd1; dsn=sqlservr;" stringdates=yes;
proc print data=mydblib.customers;
where state='CA';
run;