Appending records from Access to Sql Server - auto number problem

[AccessSQLUser]

I am appending data from an Access table to an empty SQLServer table. The field names and datatypes are all the same. The primary key field is an autonumber. When I try to append the data, including the autonumber field, I get an error that it couldn't append the records because of key violations. I understand that this doesn't work because it's an autonumber field. But then I figured that I would append the records without the autonumber field and it would automatically assign it an auto number. But I got an error:

ODBC--insert on a linked table 'dbo_ScheduleOfService' failed.
[Microsoft][ODBC SQL Server Driver]Fractional truncation (#0) [Microsoft][ODBC SQL Server Driver][SQL Server]Explicit value must be specified for identity column in table 'ScheduleOfService' when IDENTITY_INSERT is set to ON. (#545)

 

[tlbroadbent]

Somehow Identity_Insert is set On for the table. Execute the following line before running your insert query.

SET IDENTITY_INSERT tablename OFF

With Identity_Insert On SQl expects that values for Identiy columns will be supplied. Terry

X-) "Life would be easier if I had the source code." -Anonymous

 

[AccessSQLUser]

OK. I did that. But I still get the same error. I set it using Query Analyzer and the I tried appending the records using an Access append query. Any further ideas?

 

[AccessSQLUser]

Never mind! I was trying to insert records into the table before there were any records in the related table! Duh!

 

[tlbroadbent]

Thanks for that info. I have been scouring the Microsoft website, various news groups and the web for an answer. Everything that I found indicated that people had difficuly setting IDENTITY_INSERT ON because the default is OFF. I wondered how you got it set ON. It was driving me crazy because I wanted an answer myself. Terry

X-) "Life would be easier if I had the source code." -Anonymous