DATA Step, Macro, Functions and more

SQL Table Name Longer Than 32 Characters

Reply
Occasional Contributor
Posts: 11

SQL Table Name Longer Than 32 Characters

I'm getting the following error while trying access a SQL table trough an ODBC connection:

Name 'THISSQLTABLEISREALLYLONGOVER32CHAR' is too long for a SAS name in this context.

SAS only allows table names to be 32 characters but this one is longer then that. What can I do to be able to access the table? And no, I can't change the table name or run a query to duplicate the data to a table with a shorter name.
SAS Super FREQ
Posts: 8,745

Re: SQL Table Name Longer Than 32 Characters

Hi:
I believe if you consult the SAS/ACCESS documentation
http://support.sas.com/documentation/cdl/en/acreldb/63283/HTML/default/viewer.htm#/documentation/cdl...

you will find how to use NAME LITERALS in order to specify DBMS long table names.

cynthia
Occasional Contributor
Posts: 11

Re: SQL Table Name Longer Than 32 Characters

I read the documentation and put the ' at the front and end of the table name with an n at the end. The same error message is generated. The documentation references uses that for unacceptable characters but not for length. Under the length section there is no reference on how to use longer names.
SAS Super FREQ
Posts: 8,745

Re: SQL Table Name Longer Than 32 Characters

Ah, well.... worth a try.

If you are using the SAS Add-in for Microsoft Office, this note talks about a hot fix:
http://support.sas.com/kb/36/127.html (probably not appropriate for just BASE SAS, though)

You may need to open a track with Tech Support to resolve this. If you are using ODBC, the problem may be with the SAS/Access ODBC driver. It would be interesting to find out whether the same issue occurs with the SAS/Access product specifically designed for the database in question.

To send a question to Tech Support, go to http://support.sas.com/ and in the left-hand navigation pane, click on the link entitled "Submit a Problem". Alternately, you can go directly to the Tech Support Problem Form here:
http://support.sas.com/ctx/supportform/createForm

cynthia
Occasional Learner
Posts: 1

Re: SQL Table Name Longer Than 32 Characters

Hello,

Please pardon my post to an old topic, but this issue has been an on-going struggle for our company for most of the past year.

We use SAS Enterprise Business Intelligence (EBI) on Linux for X64 (SuSE 10) and connect to our operational data stored on a PostgreSQL 8.4 replication server, also on 64-bit Linux (SuSE 10), through the SAS/ACCESS Interface to ODBC.

Our database architect likes to make the table names descriptive. Since Base SAS 9.2 has a limit of 32 characters for table and view names, I and my predecessor have had to work-around this limit by manually creating views on our replication server to shorten the table names.

A current project instantiated 100+ views, many with names longer than 32 characters. A new requirement for this project involves surfacing many of these tables directly to our analysts.

I am working with the database architect to find a simple solution which does NOT involve renaming 50 or more views to fit this name length constraint. Unfortunately, it looks like we may have no choice in the next few weeks.

How can I, CurlerBob, and others best voice our need for SAS 9.x to support longer SQL names for tables, views, et cetera??

Thanks in advance for any insights you all can share!

Cheers,
BobJ
Super User
Posts: 3,115

Re: SQL Table Name Longer Than 32 Characters

I suggest you raise this as a Tech Support issue and ask them to escalate this suggestion back to the developers. If enough people do this then it will make the official SASWARE ballot. I have been using SAS long enough to remember when SAS names and tables were expanded from 8 to 32 characters long. User demand was a key driver for this.

In the meantime a quick fix might be to create a SAS dataset list of the longer than 32 character database tables, with a corresponding 32 character SAS name and then create a SAS macro to automatically generate valid SAS SQL views of the database tables from the SAS dataset list.
Ask a Question
Discussion stats
  • 5 replies
  • 3831 views
  • 0 likes
  • 4 in conversation