upsizing large table help!

upsizing large table help!

Post by Brian Cagl » Tue, 24 Mar 1998 04:00:00



I have a database that consists of several tables, I've been able to
upsize all but one from Access97 to SQLServer using the upsizintg wizard.
The one that won't upsize contains about 30, 000 records.  I've tried to
export to a ODBC database but I get a "ODBC driver Timeout expired #0"
error.

Can anyone tell me how to configure SQLServer or Access to eliminate the
timeout error and let the computer get back to exporting that table?  Or
maybe a better way, I can't make heads or tales out of
these books yet...

Brian,

 
 
 

upsizing large table help!

Post by Ande » Wed, 25 Mar 1998 04:00:00




Quote:>I have a database that consists of several tables, I've been able to
>upsize all but one from Access97 to SQLServer using the upsizintg wizard.
>The one that won't upsize contains about 30, 000 records.  I've tried to
>export to a ODBC database but I get a "ODBC driver Timeout expired #0"
>error.

>Can anyone tell me how to configure SQLServer or Access to eliminate the
>timeout error and let the computer get back to exporting that table?  Or
>maybe a better way, I can't make heads or tales out of
>these books yet...

>Brian,

I'd recommend exporting the table structure only. Make sure all your
indexes and datatypes were transferred correctly, then you can append
the data manually from Access. In your query, just increase the ODBC
timeout in the query's properties. The Upsizing wizard is nice, but it
doesn't do to well with large tables, or with appending the data, in
my experience.

Cheers,
Anders

 
 
 

upsizing large table help!

Post by Paul Bate » Wed, 25 Mar 1998 04:00:00


I am not certain, but I think that sql server will have stopped reponding
because it is:

a) Out of locks

or

b) The transaction log is full

To test b) edit the database under Enterprise manager.  Ensure that the
transaction log has enoughj free space.  If not you can press 'truncate',
however this usually does not work and you have to run 'dbcc checkdb(<your
db name>) through the query tool. Microsoft sucks !

To test a) see if you can run a query through enterprise manager after the
timeout occurs.

Paul




>>I have a database that consists of several tables, I've been able to
>>upsize all but one from Access97 to SQLServer using the upsizintg wizard.
>>The one that won't upsize contains about 30, 000 records.  I've tried to
>>export to a ODBC database but I get a "ODBC driver Timeout expired #0"
>>error.

>>Can anyone tell me how to configure SQLServer or Access to eliminate the
>>timeout error and let the computer get back to exporting that table?  Or
>>maybe a better way, I can't make heads or tales out of
>>these books yet...

>>Brian,

>I'd recommend exporting the table structure only. Make sure all your
>indexes and datatypes were transferred correctly, then you can append
>the data manually from Access. In your query, just increase the ODBC
>timeout in the query's properties. The Upsizing wizard is nice, but it
>doesn't do to well with large tables, or with appending the data, in
>my experience.

>Cheers,
>Anders

 
 
 

upsizing large table help!

Post by Chleide Christop » Thu, 26 Mar 1998 04:00:00


Hi Brian,

I have recieved the solution from a NewsGroup User ( David Preschel )
and it works fine.

Here's what you need to do:

-open the upsizing wizard add-in file in access. The file was called
WZCS97.MDA

- open the UT_MODGLOBALS module and search for 'ODBCTimeout'

- change the lines:

           if not missing (vODBCTimeout)
            then
                 qry.ODBCTimeout = CLng(vODBCTimeout)
           end if

   to :
           qry.ODBCTimeout = 0


Quote:>I have a database that consists of several tables, I've been able to
>upsize all but one from Access97 to SQLServer using the upsizintg wizard.
>The one that won't upsize contains about 30, 000 records.  I've tried to
>export to a ODBC database but I get a "ODBC driver Timeout expired #0"
>error.

>Can anyone tell me how to configure SQLServer or Access to eliminate the
>timeout error and let the computer get back to exporting that table?  Or
>maybe a better way, I can't make heads or tales out of
>these books yet...

>Brian,

Chleide Christophe

 
 
 

1. Upsizing Large Tables

I am currently having a problem upsizing large table to the SQL 7.0 server, I
have tried both Access 97 and 2000, have tried shrinking the databases but they
still won't upsize, it skips the table and gives no reason for it. If I select a
small amount of data that will import but not the whole table. I have tried
increasing the "Locks" that has not helped this problem. Any insight is greatly
appreciated.

Robin

2. Holding open an connection to a linked server

3. Upsize Wizard for Access 97 - Getting Timout w/ Large Table

4. Creating db on another drive

5. Access Upsizing Tool: Can't upsize tables with OLE field

6. Whats wrong, please help!

7. Upsized table dropped, re-upsize doesn't work

8. Upsizing tool - record to large

9. Large Cursor updating Large Table

10. How do i speed up inserting a large amount of data into a very large table

11. Large deletes/inserts against large tables....