Date:         Wed, 5 May 2010 15:56:09 -0400
Reply-To:     Mike Rhoads <RHOADSM1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mike Rhoads <RHOADSM1@WESTAT.COM>
Subject:      Re: SQL server data source with long table names
In-Reply-To:  <201005051937.o45FNfI9027130@malibu.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"

We've encountered this at Westat as well, and the explicit passthrough solution is the best we've been able to come up with.  This is one of the "traps" that I covered in my SAS Global Forum paper last year, "Avoiding Common Traps When Accessing RDBMS Data."
http://support.sas.com/resources/papers/proceedings09/141-2009.pdf

Within that mechanism, you could also consider setting up views for the offending tables with SAS-compatible (and unique) names, either as SAS views or within the DBMS if this is something you can do (or your DBA is willing to do for you).

Not sure what happened with your installation, but you should certainly be able to add a SAS product or component if necessary without doing a complete reinstall.

Good luck!


Mike Rhoads
RhoadsM1@Westat.com


-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Arthur Tabachneck
Sent: Wednesday, May 05, 2010 3:38 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: SQL server data source with long table names

Dale,

I can't help with the 9.2 install issue, but did find a post that might
provide a solution for the long table names.  I quote:

"Actually the solution is to use a passthrough query. After some
searching, my coworker found it. So you do something like:
proc sql;
 connect to odbc (dsn='*****' user='******' password='*******');
 create table ae as
 select * from connection to odbc
(
<query text goes here>);
disconnect from odbc;

The passthrough bypasses SAS and we just end up with results in a
local dataset. Labels and formats must be handled separately.
Information available in paper 18-28, Judy Loren. Lots of nuggets for
people dealing with external systems over which they have no control."

HTH,
Art
---------
On Wed, 5 May 2010 12:00:55 -0700, Dale McLerran
<stringplayer_2@YAHOO.COM> wrote:

>All,
>
>I have been given access to a database on a SQL server.  I can
>establish an ODBC connection to the database.  With the ODBC
>connection, I can view all of the tables which are in the database
>by creating a new Microsoft Access file, selecting Get External
>Data -> Link Tables... -> ODBC -> <select ODBC source>.  It turns
>out that some of the tables in the SQL server have names longer
>than 32 characters, and are not unique up through 32 characters.
>For instance, Access shows the following tables:
>
>tblImport_SpecimenForSelection_Site108
>tblImport_SpecimenForSelection_Site142
>tblImport_SpecimenForSelection_Site65
>tblImport_SpecimenForSelection_Site75
>tblImport_SpecimenForSelection_Site83
>tblImport_SpecimenForSelection_Site88
>tblImport_SpecimenForSelection_Site92
>
>All of these tables have identical name through the first 32
>characterrs:
>
>tblImport_SpecimenForSelection_S
>
>
>I could go through a laborious process of opening each table in
>Access, exporting the table to an Excel file or some other format
>that I can store locally, and read the local file into SAS.  But I
>shouldn't have to do that, should I?
>
>My ODBC source is named MSA.  The SQL server requires authentication
>with user name and password.  Thus, I have a libname statement
>constructed as
>
>libname msa odbc dsn='MSA' user=<name> pw=<pw>;
>
>Subsequently, I would reference the table through the two-level
>name MSA.<table name>.  But with the long names which are not
>unique through 32 characters, how to I reference the table that
>I want.  I tried referencing the tables as
>
>tblImport_SpecimenForSelection_1
>tblImport_SpecimenForSelection_2
>...
>
>but that failed.
>
>
>On a somewhat related note, I am only able to access an ODBC
>data source in my 9.1.3 SAS install.  My 9.2 install reports
>that the ODBC engine is not found.  However, when I run PROC
>SETINIT, it shows
>
>---SAS/ACCESS Interface to ODBC             ddMONyyyy
>
>with ddMONyyyy the same for ODBC as for other components which
>do work.  Did the IT person who installed SAS 9.2 simply fail
>to install the ODBC component?  Is it feasible to update SAS
>to incorporate the ODBC component rather than do a complete
>reinstall?
>
>Thanks,
>
>Dale
>
>---------------------------------------
>Dale McLerran
>Fred Hutchinson Cancer Research Center
>mailto: dmclerra@NO_SPAMfhcrc.org
>Ph:  (206) 667-2926
>Fax: (206) 667-5977
>---------------------------------------