SQL Pass-Through Facility Specifics for Microsoft SQL Server

Key Information

For general information about this feature, see Autopartitioning Techniques in SAS/ACCESS. Microsoft SQL Server examples are available.

Here are the SQL pass-through facility specifics for the Microsoft SQL Server interface under UNIX hosts.

The dbms-name is SQLSVR.
The CONNECT statement is required.
PROC SQL supports multiple connections to Microsoft SQL Server. If you use multiple simultaneous connections, you must use the alias argument to identify the different connections. If you do not specify an alias, the default alias is used. The functionality of multiple connections to the same Microsoft SQL Server data source might be limited by the particular data source driver.
The CONNECT statement database-connection-arguments are identical to its LIBNAME statement connection options.
These LIBNAME options are available with the CONNECT statement:
- AUTOCOMMIT=
- CURSOR_TYPE=
- KEYSET_SIZE=
- QUERY_TIMEOUT=
- READBUFF=
- READ_ISOLATION_LEVEL=
- TRACE=
- TRACEFILE=
- USE_ODBC_CL=
The DBMS-SQL-query argument can be a DBMS-specific SQL EXECUTE statement that executes a DBMS stored procedure. However, if the stored procedure contains more than one query, only the first query is processed.

CONNECT Statement Examples

These examples connect to a data source that is configured under the data source name

User's
                     Data

using the alias USER1. The first example uses the connection method that is guaranteed to be present at the lowest level of conformance. Note that DATASRC= names can contain quotation marks and spaces.

proc sql;
   connect to sqlsvr as user1
   (datasrc="User's Data" user=testuser password=testpass);

This example uses the connection method that represents a more advanced level of Microsoft SQL Server ODBC conformance. It uses the input dialog box that is provided by the driver. The DSN= and UID= arguments are within the connection string. The SQL pass-through facility therefore does not parse them but instead passes them to the ODBC driver manager.

proc sql;
   connect to SQLSVR as user1
   (required = "dsn=User's Data; uid=testuser");

In this example, you can select any data source that is configured on your machine. The example uses the connection method that represents a more advanced level of Microsoft SQL Server ODBC conformance, Level 1. When connection succeeds, the connection string is returned in the SQLXMSG and SYSDBMSG macro variables. It can then be stored if you use this method to configure a connection for later use.

proc sql;
   connect to SQLSVR (required);

This example prompts you to specify the information that is required to make a connection to the DBMS. You are prompted to supply the data source name, user ID, and password in the dialog boxes that are displayed.

proc sql;
   connect to SQLSVR (prompt);

Connection to Component Examples

This example sends Microsoft SQL Server 6.5 (configured under the data source name "SQL Server") an SQL query for processing. The results from the query serve as a virtual table for the PROC SQL FROM clause. In this example MYDB is the connection alias.

proc sql;
   connect to SQLSVR as mydb
      (datasrc="SQL Server" user=testuser password=testpass);
   select * from connection to mydb
      (select CUSTOMER, NAME, COUNTRY
          from CUSTOMERS
          where COUNTRY <> 'USA');
quit;

This next example returns a list of the columns in the CUSTOMERS table.

proc sql;
   connect to SQLSVR as mydb
      (datasrc = "SQL Server" user=testuser password=testpass);
   select * from connection to mydb
      (ODBC::SQLColumns (, , "CUSTOMERS"));
quit;