Nesting SQL Pass-Through Access

You can nest server pass-through access. Nesting allows access to data that is stored on two different networks or network nodes. There are two ways to nest access.

You can use the SPDSENG database to reserve an SPD Server from within an existing SPD Server connection. Here is an example of a nested pass-through connection. On host Datagate, which is on a local network, SQL pass-through is nested to access the EMPLOYEE_INFO table. This table is available on the PROD host on a remote network. (You must have user access to the PROD host.)
```
proc sql;
connect to sasspds (dbq='domain1'
host='datagate' serv='spdsname'
user='usr1' passwd='usr1_pw');
execute (connect to spdseng (dbq='domain2'
host='prod' serv='spdsname'
user='usr2' passwd='usr2_pw')) by sasspds;
select * from connection to sasspds(
select * from connection to spdseng(
select employee_no, annual_salary
from employee_info));
execute (disconnect from spdseng) by sasspds;
disconnect from sasspds;
quit;
```
The connection to the SPDSENG database is specified in a second SQL CONNECT statement, which is submitted in the EXECUTE statement. Note that the SELECT statement and DISCONNECT statement for the second domain are also nested in SELECT and DISCONNECT statements for the first domain.
If you would prefer not to use the SPDSENG database to reference a server, you can use the LIBGEN=YES option in the LIBNAME statement. Libraries with the LIBGEN=YES option are automatically available in SQL environments. Here is an example of how LIBGEN=YES can be used to perform the same request as the one shown above.
```
libname domain2 sasspds "domain2" host="prod" serv="spdsname" 
user='usr2' password='usr2_pw' IP=YES LIBGEN=YES;

proc sql;
 connect to sasspds (dbq='domain1' host='datagate' serv='spdsname' 
user='usr1' password='usr1_pw');
   select * from connection to sasspds (select employee_no, 
annual_salary from domain2.employee_info);
 disconnect from sasspds;
quit;
```
For more information, see LIBGEN= LIBNAME Statement Option.

Last updated: February 8, 2017