Date:         Wed, 5 May 2010 15:37:54 -0400
Reply-To:     Arthur Tabachneck <art297@NETSCAPE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arthur Tabachneck <art297@NETSCAPE.NET>
Subject:      Re: SQL server data source with long table names
Comments: To: Dale McLerran <stringplayer_2@YAHOO.COM>

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
>---------------------------------------