This generic message is provided by the Upsizing Wizard when something goes wrong. Check for a more meaningful error message on the Upsizing Report. If there is no additional information there, review the section below on the Convert OEM to ANSI Problem and review your data source. Find additional information in the SQL Server error log or the Windows NT Event Log.
If you can't find a more meaningful message, use the Upsizing Wizard to upsize the table structure without the data. Then link to the table and use an append query to move the data from Microsoft Access to SQL Server.
If the table structure won't transfer, try it again without transferring the table relationships. If that succeeds, modify the final table later to restore referential integrity. If all else fails, try exporting the table without the wizard. If you do this, you must attend to the indexes, referential integrity, defaults, and other elements on your own.
The common upsizing error "Table <table name> skipped or export failed" is usually caused by an incorrect setting in the ODBC data source.
Open Windows Control Panel and double-click ODBC. Select the data source for your server and click Configure. Click Options and verify that the Convert OEM to ANSI Characters check box is not checked. If it is, uncheck it, click OK, and close the ODBC dialog box. Try the Upsizing Wizard again.
If this fails, it may be because a user selected the language options associated with OEM to ANSI conversion while the box was checked. If that is the case, you can't easily undo those selections. Instead, make a note of the other values in the dialog box, remove the whole data source, and recreate it, taking care not to check the OEM to ANSI check box. Upsizing will normally proceed without this error if you use a data source newly created in this way.
This is an incomplete error message. After you click OK, it should be followed by the error that caused the ODBC call to fail.
ODBC Query Timeout
Server Error 0: Timeout expired
SQL that Caused Error(s)
UT_CopyData
The queries that move the data are internal to the wizard. Their timeout was lengthened programmatically to prevent the default 60-second timeout. Unfortunately, if you have a table that is several megabytes or if your network has a lot of traffic, the timeout still may not be long enough. Upsize the table structure without data, link to the new table, then move the data into it a Microsoft Access append query. Either set the ODBC Timeout property to 0 for your append query, or use a criteria that selects and moves only a portion of the data at a time. Repeat this until the entire table has been moved.
Microsoft does not support modifications of the Upsizing Wizard tool, however if this problem is causing a major stumbling block, try this:
If Not IsMissing(vODBCTimeout) Then
qry.ODBCTimeout = CLng(vODBCTimeout)
End IF'If Not IsMissing(vODBCTimeout) Then qry.ODBCTimeout = 0 'End If Here is the full error.
Can't allocate space for object '<usually a table name>' in database '<dbname>' because the '<name>' segment is full. If you ran out of space in Syslogs, dump
the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase
the size of the segment.
This error means your database is full. There are two situations in which this problem will occur. First, your database may be too small to hold the data. You must make the SQL Server database larger.
The second problem is more complicated and more common. The Upsizing Wizard moves the tables over in a transaction so that either the whole table is successfully transferred or none of the table is transferred. During the move, the data is written to the database twice: to the target table and to the transaction log. The log is integral to the transaction mechanism. If the transfer fails, the log establishes which records must be removed again. Because of this, you need to have twice as much room in the database as the largest table being moved. If the log and data are on separate devices, both the log portion and the data portion must be large enough to hold the largest table.
Sometimes you don't want to increase the size of your database. During the upsizing you are moving a lot of data, an activity that may not be a typical use pattern. The large log size required during the move may not be needed later. Though it is easy to increase the size of a database, it is difficult to shrink one. To get around this problem, consider one of these options:
Each field in a Microsoft Access table has a required property. If that field has a Yes property, indicating that a value must be entered, the Upsizing Wizard preserves that property when the table is move to SQL Server. By default, the Upsizing Wizard creates a trigger to enforce a required entry, nesting IF statements for each required field. The result is a trigger like:
CREATE TRIGGER customers_Utrig ON customers FOR UPDATE AS
/* PREVENT NULL VALUES IN 'cust_ID' */
IF (SELECT Count(*) FROM inserted WHERE 'cust_ID' IS NULL) >0
BEGIN
RAISERROR 44444 'Field' 'cust_ID' 'cannot contain a null value.'
ROLLBACK TRANSACTION
END
ELSE
/* PREVENT NULL VALUES IN 'name' */
IF (SELECT Count(*) FROM inserted WHERE 'name' IS NULL) >0
BEGIN
RAISERROR 44444 'Field' 'name' 'cannot contain a null value.'
ROLLBACK TRANSACTION
END
ELSE
The Upsizing Wizard uses triggers because they can be changed easily to allow null values later, most closely resembling Microsoft Access behavior. A SQL Server developer would more likely specify either NULL or NOT NULL for each field when creating the table definition. This looks like:
CREATE TABLE customers
(
cust_ID (CHAR(5) NOT NULL,
name VARCHAR(50) NOT NULL,
license VARCHAR(7) NULL,
…
There are three reasons why you might not use triggers to prevent the entry of null values into a field. First, a NOT NULL constraint (the NOT NULL table definition) fires before data is entered into the table. It is very fast. A trigger, on the other hand, permits the entry of the null record before evaluating the entry. If the trigger discovers an invalid value, it rolls back the insert or update. This is inherently slow.
Second, if you have many required fields, the creation of the trigger fails with errors similar to the following:
Server Error 170: Line 600: Incorrect syntax near 'WHERE;.
Server Error 191: Some part of your SQL Statement is nested too deeply.
Please re-write the query or break it up into smaller queries.
This is because the nesting level eventually fails.
Third, database purists like to use NULL and NOT NULL in their table definitions because that is the ANSI standard. Triggers don't exist in all databases, and when they do they are not implemented the same across all databases. Defining your tables with NULL and NOT NULL is good programming practice because it makes your applications more portable.
The Upsizing Wizard can be changed to use the standard NULL / NOT NULL syntax to specify required fields. Start Microsoft Access and open the Upsizing Wizard library database. For Microsoft Access 95, the filename is Wzcs.mda. For Microsoft Access 97, the filename is Wzcs97.mda. When the database is open, click the Modules tab and open the UT_ModUserConstants module. Search for the UT_USE_NULL_CONSTRAINTS constant and change the default value from False to True.
SQL Server is initially installed with the configuration setting of 5,000 locks. If you run out you will receive this error:
Server Error 1204: SQL Server has run out of LOCKS. Re-run your command when there are fewer active users, or ask your System Administrator to reconfigure SQL Server with more LOCKS.
Use the Server Configuration dialog box to increase the locks. The lock setting can be increased while you upsize your data, then reduced for the smaller activity expected during normal use. Alternatively, use the Edit Database dialog box to put your database in single-user mode so that SQL Server can maintain transaction integrity without using locking.
These errors occur when Microsoft Access thinks a record is no longer present. It usually occurs because of data type conversion issues. The solution is to add a timestamp column to the table.
If adding a timestamp field to your table does not resolve the problems, you probably are using a float data type as your primary key. To update a record, Microsoft Access identifies it by the primary key value. If that key is of a data type that cannot be accurately converted, the update will fail. The best solution is to avoid float and datetime fields as primary keys or as part of combined field primary keys. If this is not possible, use stored procedures to manipulate the table data.
Often, a special problem occurs when you upsize a Microsoft Access 95 database to a SQL Server 6.5 database. The Upsizing Wizard creates the table, moves the data into it, and then alters the table to add the timestamp. The timestamps are still blank when the process is completed. The SQL Server 6.5 ODBC driver enforces ANSI syntax not supported by Microsoft Access 95. Microsoft Access mistakenly creates queries with NULL timestamp fields "= 0x00" instead of "IS NULL." This syntax error prevents Microsoft Access from finding timestamp fields with null values. To solve this problem, update every record in the table after the upsizing has completed. This will populate the timestamp field. You can update simply by setting a field value to itself. Pick some non-indexed field to do this quickly. For example:
UPDATE mytable
SET zipcode = zipcode
This will update every record without changing data.
If you get the #Deleted value when sorting large record sets in SQL Server 6.5, apply Service Pack 2.
To resolve this problem with zero-length strings in Microsoft Access 2.0, replace the zero-length string with a null. Create a query with the field defined below, replacing <fieldname> with the name of your problem field:
Expr1:Len([<fieldname>])
Critieria: 0
Run this query to return records with fields containing zero-length strings.
Now change the SELECT query to an UPDATE query and type null in the Update To row of <fieldname>. Run the query. All zero length strings in the field are changed to null values. Now you can export the table to SQL Server. For more information, see the Microsoft Access Knowledge Base article Q131584, "ACC2: 'Invalid Argument' Error Msg Exporting Table to SQL Server."
Because the data in a SQL Server table is not in a native Microsoft Access format, Microsoft Access cannot manage it the same way it manages a native Microsoft Access table. It must have a unique index for identifying each row individually. For best performance, provide a unique index on each table.
When you link to a table without a unique index, you are prompted for a unique field or combination of fields.
In order to update records in this table you must select which field or fields uniquely identify each record. Select up to ten fields.
The status bar at the bottom of the screen presents the message "This Recordset is not updateable" if it attempts to edit a record in a table without a unique index.
The status bar at the bottom of the screen presents the message "Records not deleted. Data is read only" if it attempts to delete a record in a table without a unique index.
If a unique field or combination of fields exists but has no unique index, you can create an index in Microsoft Access, creating a copy of the field or fields so that Microsoft Access can identify each record. You can do one of the following:
CREATE UNIQUE INDEX myindex
ON
NameOfTheTable(UniqueColumnName)No matter how the index is created, it keeps a copy of the field or fields necessary to identify each row uniquely. If you can create a unique index on the server, SQL Server performs much faster in almost all circumstances. An index constructed in Microsoft Access for a SQL Server table is less useful, requires more space, and needs more maintenance to keep current. Use a Microsoft Access index only when the table must be updated and the index cannot be created on the server.
This error occurs with the original release of Microsoft Access for Windows 95 and a linked SQL Server table with an identity column. This bug is fixed in the free upgrade Microsoft Access 7.0a. Knowledge Base articles Q149535 and Q153151 describe this problem. To obtain this upgrade, call the Microsoft Order Desk at (800) 360-7561.
If you receive a timeout expired message immediately after your second asynchronous retry, you may need an updated SQL Server ODBC driver. This problem can occur with any network library when connecting with Microsoft Access 2.0, Visual Basic 3 or Visual Basic 4 16-Bit in asynchronous mode. The problem occurs in the SQL Server ODBC driver (Sqlsrvr.dll) version 2.65.0201 and is corrected in the drivers shipped with SQL Server 6.5, Service Pack 1. Upgrade to the new driver. You can also work around the problem by setting DisableAsync = 1 in the Msacc20.ini file. For more information, see Knowledge Base article Q153908, "16 bit Driver Times Out in calls to SQL Server."
When you make a trusted connection to SQL Server using the 16-bit Multi-Protocol Netlibrary on a Windows for Workgroups client computer that is logged on to both a Microsoft domain and a Netware network, you may be asked for your domain credentials again. This can happen even if the connection is forced over Named Pipes. This problem is discussed in Knowledge Base article Q154631, "BUG: Domain Credentials Inappropriately Requested." Currently, the only resolutions are to provide the domain credentials again or to remove the Netware network shell.
Remember that SQL Enterprise Manager is not SQL Server, it is a client just like Microsoft Access. If you make a change to your server using SQL Enterprise Manager, it will reflect that change. If you make a change to the server from some other client (such as Microsoft Access), SQL Enterprise Manager will not know about it until it checks with the server for new information. In the Server Manager window of SQL Enterprise Manager, right-click in the appropriate place ("Tables" or "Databases") and select Refresh from the menu.
By default, a limit of 4K is transmitted for text fields. Increase this amount by using the SET statement. When upsizing tables, the Upsizing Wizard increases the text size to 2,147,483,647 bytes. You can do the same with the command:
SET TEXTSIZE 2147483647
SET statements only persist for the current session and connection.