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.

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;