LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (May 2010, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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 >---------------------------------------


Back to: Top of message | Previous page | Main SAS-L page