Hi, I'm having a disagreement with my local SAS Tech Support, but I'm happy to be convinced that I'm incorrect. I'm accessing SQL Server tables via the ODBC engine. The two relevant tables are named RLDX_AUDIT_TRAIL and Record. The capitalization is important. Here is some test code: * set desired SAS options ;
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
options msglevel=I;
options fullstimer;
options mprint mrecall;
options compress=binary;
options ls=max;
options nocenter;
options lognumberformat=1;
libname sqlsvr odbc
NOPROMPT="Driver={SQL Server Native Client 10.0};Server=MYSERVER;Database=MYDB_dev;Trusted_Connection=yes;"
bulkload=yes
schema=rldxhosp
preserve_tab_names=no
;
filename temp temp;
* Note: RLDX_AUDIT_TRAIL has 29 records, Record exists but is an empty table (0 records) ;
proc datasets lib=sqlsvr;
copy out=work;
select rldx_audit_trail record;
quit;
proc datasets lib=sqlsvr;
copy out=work;
select RLDX_AUDIT_TRAIL record;
quit;
proc datasets lib=sqlsvr;
copy out=work;
select RLDX_AUDIT_TRAIL Record;
quit;
proc copy in=sqlsvr out=work;
select rldx_audit_trail record;
quit;
proc copy in=sqlsvr out=work;
select RLDX_AUDIT_TRAIL record;
quit;
proc copy in=sqlsvr out=work;
select RLDX_AUDIT_TRAIL Record;
quit;
proc cport lib=sqlsvr file="%sysfunc(pathname(temp))";
select rldx_audit_trail record;
run;
proc cport lib=sqlsvr file="%sysfunc(pathname(temp))";
select RLDX_AUDIT_TRAIL record;
run;
proc cport lib=sqlsvr file="%sysfunc(pathname(temp))";
select RLDX_AUDIT_TRAIL Record;
run; And here is the log: 1 The SAS System 11:27 Wednesday, April 12, 2017
1 ;*';*";*/;quit;run;
2 OPTIONS LS=120 PS=50;
3 OPTIONS PAGENO=MIN;
4 %LET _CLIENTTASKLABEL='preserve_tab_names=NO';
6 %LET _CLIENTPROJECTNAME='SAS ODBC case sensititivy.egp';
7 %LET _SASPROGRAMFILE=;
8
9 ODS _ALL_ CLOSE;
10 OPTIONS DEV=ACTIVEX;
11 GOPTIONS XPIXELS=0 YPIXELS=0;
12 FILENAME EGSR TEMP;
13 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14 STYLE=HtmlBlue
15 STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/6.1/Styles/HtmlBlue.cs
15 ! s")
16 NOGTITLE
17 NOGFOOTNOTE
18 GPATH=&sasworklocation
19 ENCODING=UTF8
20 options(rolap="on")
21 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22
23 GOPTIONS ACCESSIBLE;
24 * set desired SAS options ;
25 options sastrace=',,,d' sastraceloc=saslog nostsuffix;
26 options msglevel=I;
27 options fullstimer;
28 options mprint mrecall;
29 options compress=binary;
30 options ls=max;
31 options nocenter;
32 options lognumberformat=1;
33
34 libname sqlsvr odbc
ODBC: AUTOCOMMIT is NO for connection 0
35 NOPROMPT=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
36 bulkload=yes
37 schema=rldxhosp
38 preserve_tab_names=no
39 ;
NOTE: Libref SQLSVR was successfully assigned as follows:
Engine: ODBC
Physical Name:
40
41 filename temp temp;
2 The SAS System 11:27 Wednesday, April 12, 2017
42
43 * Note: RLDX_AUDIT_TRAIL has 29 records, Record exists but is an empty table (0 records) ;
44 proc datasets lib=sqlsvr;
ODBC: AUTOCOMMIT is NO for connection 1
ODBC: Called SQLTables with schema of rldxhosp
45 copy out=work;
46 select rldx_audit_trail record;
47 quit;
ERROR: The file SQLSVR.rldx_audit_trail (memtype=ALL) was not found, but appears on a SELECT statement.
ERROR: The file SQLSVR.record (memtype=ALL) was not found, but appears on a SELECT statement.
NOTE: Statements not processed because of errors noted above.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.15 seconds
user cpu time 0.03 seconds
system cpu time 0.00 seconds
memory 639.90k
OS Memory 14116.00k
Timestamp 04/12/2017 11:55:14 AM
48 proc datasets lib=sqlsvr;
ODBC: Called SQLTables with schema of rldxhosp
49 copy out=work;
50 select RLDX_AUDIT_TRAIL record;
51 quit;
NOTE: Copying SQLSVR.RLDX_AUDIT_TRAIL to WORK.RLDX_AUDIT_TRAIL (memtype=DATA).
ODBC: AUTOCOMMIT is NO for connection 2
ODBC_1: Prepared: on connection 2
SELECT * FROM rldxhosp.RLDX_AUDIT_TRAIL
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
INFO: Data set block I/O cannot be used because:
INFO: - The data sets use different engines, have different variables or have attributes that may differ.
ODBC_2: Executed: on connection 2
Prepared statement ODBC_1
NOTE: There were 29 observations read from the data set SQLSVR.RLDX_AUDIT_TRAIL.
NOTE: The data set WORK.RLDX_AUDIT_TRAIL has 29 observations and 21 variables.
NOTE: Compressing data set WORK.RLDX_AUDIT_TRAIL increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
ERROR: The file SQLSVR.record (memtype=ALL) was not found, but appears on a SELECT statement.
NOTE: Statements not processed because of errors noted above.
3 The SAS System 11:27 Wednesday, April 12, 2017
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.10 seconds
user cpu time 0.01 seconds
system cpu time 0.01 seconds
memory 394.40k
OS Memory 14372.00k
Timestamp 04/12/2017 11:55:14 AM
52 proc datasets lib=sqlsvr;
ODBC: Called SQLTables with schema of rldxhosp
53 copy out=work;
54 select RLDX_AUDIT_TRAIL Record;
55 quit;
NOTE: Copying SQLSVR.RLDX_AUDIT_TRAIL to WORK.RLDX_AUDIT_TRAIL (memtype=DATA).
ODBC: AUTOCOMMIT is NO for connection 2
ODBC_3: Prepared: on connection 2
SELECT * FROM rldxhosp.RLDX_AUDIT_TRAIL
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
INFO: Data set block I/O cannot be used because:
INFO: - The data sets use different engines, have different variables or have attributes that may differ.
ODBC_4: Executed: on connection 2
Prepared statement ODBC_3
NOTE: There were 29 observations read from the data set SQLSVR.RLDX_AUDIT_TRAIL.
NOTE: The data set WORK.RLDX_AUDIT_TRAIL has 29 observations and 21 variables.
NOTE: Compressing data set WORK.RLDX_AUDIT_TRAIL increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: Copying SQLSVR.Record to WORK.RECORD (memtype=DATA).
ODBC: AUTOCOMMIT is NO for connection 2
ODBC_5: Prepared: on connection 2
SELECT * FROM rldxhosp.Record
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
INFO: Data set block I/O cannot be used because:
INFO: - The data sets use different engines, have different variables or have attributes that may differ.
ODBC_6: Executed: on connection 2
Prepared statement ODBC_5
NOTE: There were 0 observations read from the data set SQLSVR.Record.
4 The SAS System 11:27 Wednesday, April 12, 2017
NOTE: The data set WORK.RECORD has 0 observations and 9 variables.
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.15 seconds
user cpu time 0.01 seconds
system cpu time 0.01 seconds
memory 417.15k
OS Memory 14372.00k
Timestamp 04/12/2017 11:55:14 AM
56
57 proc copy in=sqlsvr out=work;
58 select rldx_audit_trail record;
59 quit;
ODBC: Called SQLTables with schema of rldxhosp
ERROR: The file SQLSVR.rldx_audit_trail (memtype=ALL) was not found, but appears on a SELECT statement.
ERROR: The file SQLSVR.record (memtype=ALL) was not found, but appears on a SELECT statement.
NOTE: Statements not processed because of errors noted above.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE COPY used (Total process time):
real time 0.01 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 91.37k
OS Memory 14372.00k
Timestamp 04/12/2017 11:55:14 AM
60 proc copy in=sqlsvr out=work;
61 select RLDX_AUDIT_TRAIL record;
62 quit;
ODBC: Called SQLTables with schema of rldxhosp
NOTE: Copying SQLSVR.RLDX_AUDIT_TRAIL to WORK.RLDX_AUDIT_TRAIL (memtype=DATA).
ODBC: AUTOCOMMIT is NO for connection 2
ODBC_7: Prepared: on connection 2
SELECT * FROM rldxhosp.RLDX_AUDIT_TRAIL
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
INFO: Data set block I/O cannot be used because:
INFO: - The data sets use different engines, have different variables or have attributes that may differ.
ODBC_8: Executed: on connection 2
Prepared statement ODBC_7
5 The SAS System 11:27 Wednesday, April 12, 2017
NOTE: There were 29 observations read from the data set SQLSVR.RLDX_AUDIT_TRAIL.
NOTE: The data set WORK.RLDX_AUDIT_TRAIL has 29 observations and 21 variables.
NOTE: Compressing data set WORK.RLDX_AUDIT_TRAIL increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
ERROR: The file SQLSVR.record (memtype=ALL) was not found, but appears on a SELECT statement.
NOTE: Statements not processed because of errors noted above.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE COPY used (Total process time):
real time 0.06 seconds
user cpu time 0.00 seconds
system cpu time 0.01 seconds
memory 297.43k
OS Memory 14372.00k
Timestamp 04/12/2017 11:55:14 AM
63 proc copy in=sqlsvr out=work;
64 select RLDX_AUDIT_TRAIL Record;
65 quit;
ODBC: Called SQLTables with schema of rldxhosp
NOTE: Copying SQLSVR.RLDX_AUDIT_TRAIL to WORK.RLDX_AUDIT_TRAIL (memtype=DATA).
ODBC: AUTOCOMMIT is NO for connection 2
ODBC_9: Prepared: on connection 2
SELECT * FROM rldxhosp.RLDX_AUDIT_TRAIL
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
INFO: Data set block I/O cannot be used because:
INFO: - The data sets use different engines, have different variables or have attributes that may differ.
ODBC_10: Executed: on connection 2
Prepared statement ODBC_9
NOTE: There were 29 observations read from the data set SQLSVR.RLDX_AUDIT_TRAIL.
NOTE: The data set WORK.RLDX_AUDIT_TRAIL has 29 observations and 21 variables.
NOTE: Compressing data set WORK.RLDX_AUDIT_TRAIL increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: Copying SQLSVR.Record to WORK.RECORD (memtype=DATA).
ODBC: AUTOCOMMIT is NO for connection 2
ODBC_11: Prepared: on connection 2
SELECT * FROM rldxhosp.Record
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
INFO: Data set block I/O cannot be used because:
INFO: - The data sets use different engines, have different variables or have attributes that may differ.
6 The SAS System 11:27 Wednesday, April 12, 2017
ODBC_12: Executed: on connection 2
Prepared statement ODBC_11
NOTE: There were 0 observations read from the data set SQLSVR.Record.
NOTE: The data set WORK.RECORD has 0 observations and 9 variables.
NOTE: PROCEDURE COPY used (Total process time):
real time 0.12 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 321.71k
OS Memory 14372.00k
Timestamp 04/12/2017 11:55:14 AM
66
67 proc cport lib=sqlsvr file="%sysfunc(pathname(temp))";
68 select rldx_audit_trail record;
69 run;
ODBC: Called SQLTables with schema of rldxhosp
ODBC: AUTOCOMMIT is NO for connection 2
ODBC_13: Prepared: on connection 2
SELECT * FROM rldxhosp.RLDX_AUDIT_TRAIL
NOTE: PROC CPORT begins to transport data set SQLSVR.RLDX_AUDIT_TRAIL
ODBC_14: Executed: on connection 2
Prepared statement ODBC_13
NOTE: The data set contains 21 variables and 29 observations.
Logical record length is 485.
WARNING: Specified member SQLSVR.RECORD.* was not found.
NOTE: PROCEDURE CPORT used (Total process time):
real time 0.10 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 135.62k
OS Memory 14372.00k
Timestamp 04/12/2017 11:55:14 AM
70 proc cport lib=sqlsvr file="%sysfunc(pathname(temp))";
71 select RLDX_AUDIT_TRAIL record;
72 run;
7 The SAS System 11:27 Wednesday, April 12, 2017
ODBC: Called SQLTables with schema of rldxhosp
ODBC: AUTOCOMMIT is NO for connection 2
ODBC_15: Prepared: on connection 2
SELECT * FROM rldxhosp.RLDX_AUDIT_TRAIL
NOTE: PROC CPORT begins to transport data set SQLSVR.RLDX_AUDIT_TRAIL
ODBC_16: Executed: on connection 2
Prepared statement ODBC_15
NOTE: The data set contains 21 variables and 29 observations.
Logical record length is 485.
WARNING: Specified member SQLSVR.RECORD.* was not found.
NOTE: PROCEDURE CPORT used (Total process time):
real time 0.10 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 135.62k
OS Memory 14372.00k
Timestamp 04/12/2017 11:55:14 AM
73 proc cport lib=sqlsvr file="%sysfunc(pathname(temp))";
74 select RLDX_AUDIT_TRAIL Record;
75 run;
ODBC: Called SQLTables with schema of rldxhosp
ODBC: AUTOCOMMIT is NO for connection 2
ODBC_17: Prepared: on connection 2
SELECT * FROM rldxhosp.RLDX_AUDIT_TRAIL
NOTE: PROC CPORT begins to transport data set SQLSVR.RLDX_AUDIT_TRAIL
ODBC_18: Executed: on connection 2
Prepared statement ODBC_17
NOTE: The data set contains 21 variables and 29 observations.
Logical record length is 485.
WARNING: Specified member SQLSVR.RECORD.* was not found.
NOTE: PROCEDURE CPORT used (Total process time):
real time 0.07 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 135.62k
8 The SAS System 11:27 Wednesday, April 12, 2017
OS Memory 14372.00k
Timestamp 04/12/2017 11:55:14 AM
76
77
78 GOPTIONS NOACCESSIBLE;
79 %LET _CLIENTTASKLABEL=;
80 %LET _CLIENTPROJECTPATH=;
81 %LET _CLIENTPROJECTNAME=;
82 %LET _SASPROGRAMFILE=;
83
84 ;*';*";*/;quit;run;
85 ODS _ALL_ CLOSE;
86
87
88 QUIT; RUN;
89 I get the same results whether preserve_tab_names=yes or no. Questions: 1) For PROC DATASETS and PROC COPY, should the select statement be case-sensitive for the table names? From the sastrace output, I see: ODBC: AUTOCOMMIT is NO for connection 1
ODBC: Called SQLTables with schema of rldxhosp I'm thinking that "Called SQLTables" isn't doing the right thing, and SAS thinks the tables don't exist, when they do. 2) For PROC CPORT, should the select statement behave consistently with the select statement for PROC DATASETS and PROC COPY? Because it isn't - it appears to be uppercasing the table names, and then not finding the table. I can export the RLDX_AUDIT_TRAIL table because its name is already all uppercase, but I can't export the Record table at all. 3) Here is the doc link for CPORT: http://support.sas.com/documentation/cdl/en/proc/70377/HTML/default/viewer.htm#n1c1z2uzw5ejpfn1cp87c1h4yfcf.htm It says it supports SAS datasets, SAS catalogs, etc. IMO, a SAS/Access table is a "virtual SAS dataset", in that I can (usually) use it where I would use a SAS dataset (set statement, from statement, etc). So, should I expect to be able to directly export a SQL Server without creating an intermediate SAS dataset (just so PROC CPORT will work)? Lastly, it's interesting that my SQL Server database is not case sensitive. See attached screenshot. Thanks!
... View more
