This is my first SQL project. An Access database was converted to SQL Server 2005 Express using the DTS utility. The old AutoNumber fields were automatically converted to int identity not null fields. However, when attempting to INSERT a row, such as the following ASP code,
Conn.Execute("INSERT INTO tblAdmin3 (fldA3Username,fldA3TimeIn,fldA3Date) VALUES ('" & var5 & "','" & Time() & "','" & Date() & "')")the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'fldA3Num', table 'mhdsg.dbo.tblAdmin3'; column does not allow nulls. INSERT fails.
/mhds/login.asp, line 113
is received. The database works properly except when adding new rows. The SQL Server Management Studio Express program shows that the column properties for fldA3Num are:
Data Type = int
System Type = int
Primary key = true
Allow nulls = false
Identity = true
Identity seed = 1
Identity increment = 1
etc. I must be overlooking something simple, but I can't see it. It appears that the conversion by DTS of the AutoNumber fields to Identity fields is not effective, even though SQL Server Management Studio Express declares it to be correct. On another forum, the idea was suggested to add the line
Conn.Execute("Set Identity_Insert tblAdmin3 off")immediately before the "INSERT INTO ..." line. This resulted in the error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Table 'tblAdmin3' does not have the identity property. Cannot perform SET operation.
/mhds/login.asp, line 113
Goggle tells me that this error message appears when you try to use the SET IDENTITY_INSERT setting for a table that does not contain a column for which the IDENTITY property was declared. However, the SQL Server Management Studio Express program says that the fldA3Num column is set as Identity. So am still puzzled.
It certainly appears that the DTS wizard does not properly convert Access AutoNumber fields to SQL Identity columns.
The only thing I can think of to do now is to add a new Identity column, delete the existing Identity column, and rename the new column to the old name. I am reluctant to do this as this database has nearly forty tables that this operation must be performed on, and some of them contain significant amounts of data. Any ideas?