When you create a new Microsoft Data Engine (MSDE) or SQL Server database, the select into/bulkcopy and IDENTITY_INSERT options are set to OFF.
Upsized Make-Table Queries
When you upsize a database that contains make-table queries, the queries migrate as stored procedures. These stored procedures use the SELECT INTO statement to create a new table and then add records to it. You must set the select into/bulkcopy option of the database to ON in any database that contains a stored procedure that uses the SELECT INTO statement.
Upsized Update and Append Queries
If you upsize a Microsoft Access table that contains a field of the AutoNumber data type, the field will be created on MSDE or SQL Server as an IDENTITY column. Unlike the AutoNumber data type, you cannot directly edit IDENTITY columns, nor explicitly insert data into an IDENTITY column while the IDENTITY_INSERT option for that table is set to OFF. To insert or update data in an IDENTITY column, you must set the IDENTITY_INSERT option to ON.
To set the select into/bulk copy option to ON for your database, create a stored procedure that uses the sp_dboption system stored procedure to toggle the setting. The following SQL sample modifies a database named MyDatabase so you can use SELECT INTO statements:
CREATE PROCEDURE SetMyOptions
EXEC sp_dboption 'MyDatabase','bulkcopy','ON'
You can use the SET IDENTITY_INSERT statement to set the IDENTITY_INSERT option. SET IDENTITY_INSERT always references a table, and should be placed before the UPDATE or INSERT statement that modifies or inserts data into an IDENTITY column. The following example sets IDENTITY_INSERT for the NewEmployees table.
When you try to upsize to SQL Server 2000 from Access 2000 using the Upsizing Wizard you receive an "Overflow" error message.
For additional information about this issue, click the article number below
to view the article in the Microsoft Knowledge Base: