st: Re: ODBC load problems

["Joseph Coveney" <jcoveney@bigplanet.com>]

James Morden wrote:

We are having some difficulty loading a table from an Access file into Stata
using ODBC and wondered if anyone might be able to help.

We have started to use Macro 4 to store our data. We can set up an ODBC
connection to the SQL server 8 database we get from this and have no trouble
loading all tables in the dataset into Stata using "odbc load". However w= e
also wanted to take a snapshot of the data to store permanently so for this the
data was copied from SQL server 8 into Access 2007.
We then tried to set up an odbc connection to this and load the tables into
Stata. This all worked fine apart from  one fairly wide table (approx 150
variables) which would not load, and gave error message "r(1010) - system limit
exceeded -- width shortage...." e.t.c Now the table is not that wide, and we
have previously had no trouble loading in much wider tables with around 230
variables (and many more observations too). All the other tables in this
database also still load fine, it seems strange that this one would load from
SQL server but not from Access.

We have tried playing around with different version of Access but this does not
seem to help. We have also checked all the variables in the Access database and
they all look normal, nothing which would suggest there should=  be a problem
loading this table.

Has anyone encountered anything like this before or have any suggestions?

--------------------------------------------------------------------------------

Microsoft Access's default text column is CHAR(255).  If all or most of the 150
columns in the problematic table are relatively short CHAR() or VARCHAR() types
in MS SQL Server 2000, and if the transfer procedure from MS SQL Server 2000 to
MS Access 2007 permits the latter to use its default, then you'd have 150 * 255
bytes, about 38 kilobytes for the row width.  (Double that for columns set up as
NCHAR()/NVARCHAR() in MS SQL SERVER transferred into MS Access without Unicode
Compression set on.)

I assume that you're not using Stata/IC, so the maximum limit wouldn't be
exceeded, but what are your -maxvar- settings?  The help file (Stata 11) for the
error that you're seeing states, "The maximum width is set to 12*maxvar."  If
your -maxvar- is set for much fewer than 3200 variables, then that combination
of events could be a cause.

Perhaps another diagnostic is to pull the table directly into Stata from MS SQL
Server, then push it into a fresh MS Access 2007 database using -odbc insert . .
., create-.  Compare the two versions of the same table, especially column
widths--does the direct-from-SQL Server end up as long integers when -odbc
insert . . ., create- end up as bytes?  CHAR(255)s and MEMO versus VARCHAR(20)s?

Joseph Coveney


*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/