|SAS/ACCESS Software for Relational Databases: Reference|
A Version 6 PROC SQL Pass-Through view does not need to be updated to be used in Version 7 or Version 8. The conversion of PROC SQL Pass-Through views is automatic and does not require you to use the PROC CV2ODBC procedure, as described in CV2ODBC Procedure. The ODBC interface and DBMS client must be available and ready to connect. In order for any truncated variable names to be correctly interpreted by the ODBC driver, you must specify the VALIDVARNAME=V6 option.
the view cannot be processed, or if you want to see what the view is, use
the DESCRIBE VIEW statement to see what the existing view is. Then you can
use the PROC SQL statements to create a new view for the ODBC connection.
|IBM AS/400 Specifics|
To run your SQL Pass-Through views for IBM AS/400, you must
|MS SQL Server Specifics|
To run your SQL Pass-Through views for the MS SQL Server, you are encouraged, but not required, to create a data source name. You can use the ODBC administrator to create it. Refer to Data Source Configuration for MS SQL Server for more information. If you do create a data source name, you must set the environment variable MSSQLDSN to be 'data-source-name'. Quotation marks are required if the name includes blanks or special characters.
|Pass-Through View Examples|
In this example,
CONNECT TO AS400 AS market;is converted to
CONNECT TO ODBC AS market (NOPROMPT="DSN=IBM AS/400 Database; UID=TESTUSER; PWD=TESTPASS; NAM=1" ) ;
In this example,
CONNECT TO SQLSERVR AS finance (user=testuser pass=testpass server='dbipc1.pc.sas.com' database='sample' ) ;is converted to
CONNECT TO ODBC AS finance (NOPROMPT="DSN=Microsoft SQL Server Database; SERVER=dbipc1.pc.sas.com; UID=testuser; PWD=testpass; DATABASE=sample" ) ;
In this example, you must use the SAS option VALIDVARNAME=V6 in order to successfully process this Version 6 SQL Pass-Through view.
options validvarname=v6; proc sql; describe view as4sql.invoice4; run; /* NOTE: SQL view AS4SQL.INVOICE4 is defined as: */ select INVOICEN as INVOICE, AMTBILLE as AMOUNT format=DOLLAR20.2, BILLEDON from connection to AS400 /* dbms=AS400, connect options=() */ (select invoicenum, amtbilled, billedon from sasdemo/invoice where paidon ='18OCT1998');
In Version 6, the AS/400 column name INVOICENUM is mapped to the SAS variable INVOICEN, and AMTBILLED is mapped to AMTBILLE. If you do not specify option VALIDVARNAME=V6, you get the following error because the ODCB driver attempts to find the truncated column names in the DBMS table:
ERROR: The following columns were not found in the contributing tables: AMTBILLE, INVOICEN.
This example demonstrates a problem in which AS/400 short alias names cannot be returned by the AS/400 ODBC driver. This problem causes you to get an error, for example, if you have specified the short alias names in your selection list before the CONNECTION TO component, but have not specified the short alias names in the selection list that defines the view. If you encounter this problem with your Version 6 SQL Pass-Through views, you need to re-create the views.
This example creates an AS/400 table named TEST5 with the columns CUSTOMER_FIRST_NAME and CUSTOMER_LAST_NAME. The short name alias for CUSTOMER_FIRST_NAME is FNAME and the short name alias for CUSTOMER_LAST_NAME is LNAME.
options validvarname=v6; %let name=test5; proc sql; describe view as4sql.&name; /* NOTE: SQL view AS4SQL.TEST5 is defined as: */ select FNAME, LNAME from connection to AS400 /* dbms=AS400, connect options=() */ ( select * from sasdemo/test where lname = 'Ju' ); quit; proc print data=as4sql.&name; run;This example generates the following errors:
ERROR: The following columns were not found in the contributing tables: FNAME, LNAME. ERROR: SQL View AS4SQL.TEST5 could not be processed.
The following two examples work successfully because the short alias names are specified in the SELECT statement that defines the view.
create view as4sql.&name as select FNAME, LNAME from connection to AS400 /* dbms=AS400, connect options=() */ (select FNAME, LNAME from sasdemo/test where lname = 'Ju' ); create view as4sql.&name as select * from connection to AS400 /* dbms=AS400, connect options=() */ (select fname, lname from sasdemo/test where lname = 'Ju' );
|Data Source Configuration for the IBM AS/400|
following instructions are specific to PC platforms.
Use the following steps to configure your data source for AS/400.
Note: You can install the software from 'IBM AS/400 Client Access Family' CD-ROM.
Refer to your IBM AS/400 documentation for more information.
|Data Source Configuration for MS SQL Server|
The following instructions are specific to PC platforms.
Use the following steps to configure your data source for MS SQL Server.
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.