Date: Wed, 16 Feb 2011 09:15:31 -0600
Reply-To: matt.pettis@THOMSONREUTERS.COM
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Matthew Pettis <matt.pettis@THOMSONREUTERS.COM>
Subject: Re: SAS/ACCESS ODBC: Problem viewing tables with 'libname odbc'
engine (but ok with passthrough)
Content-Type: text/plain; charset="us-ascii"
I meant to add that this is a Microsoft SQL Server.
From: Pettis, Matthew (Legal)
Sent: Wednesday, February 16, 2011 9:10 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SAS/ACCESS ODBC: Problem viewing tables with 'libname odbc'
engine (but ok with passthrough)
Hi,
I am trying to find the right incantation to view tables via the
'libname odbc' engine. I can correctly query tables with passthrough
sql, but when I try to view the individual tables with the 'libname
odbc' engine, no tables are in the explorer for me to see. I think the
issue is that I don't know how to specify all of the correct pieces in
the libname statement. Below is an example of two libname statements
(both succeed in connecting, but explorer for the assigned libname
doesn't show any tables). Any help to make the libname engine work is
appreciated.
Thanks,
Matt
117 *
118
--------------------------------------------------------------------
119 Libnames connect, but no tables visible in explorer in either
case.
120
--------------------------------------------------------------------
121 ;
122 libname filer odbc datasrc='storageoptdb-prod'
user='storageData_readonly' password=XXXXXXXXXX;
NOTE: Libref FILER was successfully assigned as follows:
Engine: ODBC
Physical Name: storageoptdb-prod
123 libname filer odbc datasrc='storageoptdb-prod'
user='storageData_readonly' password=XXXXXXXXXX
123! schema=StorageData;
NOTE: Libref FILER was successfully assigned as follows:
Engine: ODBC
Physical Name: storageoptdb-prod
124
125 *
126
--------------------------------------------------------------------
127 Query is successful.
128
--------------------------------------------------------------------
129 ;
130 proc sql;
131 connect to odbc (datasrc='storageoptdb-prod'
user='storageData_readonly' password=XXXXXXXXXX);
132
133 create table temp as
134 select
135 *
136 from
137 connection to odbc (
138 select * from StorageData.dbo.sanhost
139 )
140 ;
NOTE: Compressing data set WORK.TEMP decreased size by 94.31 percent.
Compressed is 7 pages; un-compressed would require 123 pages.
NOTE: Table WORK.TEMP created, with 1840 rows and 3 columns.
141 disconnect from odbc;
142 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.06 seconds
cpu time 0.01 seconds
