Connect to SPD Server with Explicit SQL Pass-Through

To connect to the server for explicit SQL pass-through, you must use PROC SQL.

Submit an SQL CONNECT statement. The SQL CONNECT statement must specify the SASSPDS engine and server connection options. The SQL CONNECT statement invokes the explicit SQL pass-through facility.
Submit SQL statements as follows:
- Submit SQL statements that do not return a result set in the EXECUTE statement.
- Submit queries using the SELECT...FROM CONNECTION statement.
Terminate the explicit pass-through session with the DISCONNECT statement.

Here is an example of the code necessary to send an explicit SQL pass-through request in the SPD Server environment:

proc sql;
connect to sasspds
   (dbq='mydomain'
    host='servername'
    service='5400'
    user='MySPDuserid'
    password='MyPasswd');
execute (SQL-statements) by sasspds;
select * from connection to sasspds
(SELECT-query);
disconnect from sasspds;
quit;

In the CONNECT statement:

SASSPDS is the name of the SPD Server client engine.
The arguments in parentheses submit server connection parameters:

DBQ=

specifies the server domain.

HOST=

specifies a node name or an IP address for the server host. The HOST= argument is optional. If you omit the argument, SPD Server uses the current value of the SAS macro variable SPDSHOST to determine the node name.

SERVICE=

specifies the port number for the name server. You can specify the port name instead, if one is configured. When you use a port name, SPD Server determines the network address from the named service in the/etc/services file. The default port name is spdsname.

USER=

specifies a server user ID.

PASSWORD= (or PASSWD=)

specifies the password associated with the server user ID. The password that you specify must be valid for the form of authentication that your server is using. For example, if your server is using LDAP authentication, then you must specify your LDAP password.

Note: You can use PROMPT= instead of PASSWORD=.

PROMPT=YES

causes SPD Server to prompt for a password. The prompter is case-sensitive.

Note: You can use PASSWORD= instead of PROMPT=.

Note: If UNIX file security is the only form of security that is active for the server, then USER= and PASSWORD= are not required. All resources within the server domain are granted access by UNIX permissions for the server UNIX ID.

Note: AUTHDOMAIN= can also be used instead of USER= and PASSWORD=.

EXECUTE statement:

The EXECUTE statement enables you to send SQL statements that do not return a result set to the server. The server’s SQL processor supports the same SQL statements as PROC SQL, except SELECT. SELECT is not allowed. In addition, the SQL processor supports some SPD Server SQL statements that are available only in explicit pass-through. The PROC SQL statements function a little differently in the server SQL processor than they do in PROC SQL. For more information, see the following:

SELECT statement:

The SELECT statement establishes a pass-through connection for SELECT queries. See Differences between SAS SQL and SPD Server SQL for information about the SQL processor’s SELECT support.

The DISCONNECT statement ends the SQL pass-through session.

Last updated: February 8, 2017