If you are currently running PCSchool on an SQL2005 server and are transferring PCSchool to a new server which has a newer version of SQL (eg. SQL2008) then a possible error you may receive after the transfer is ‘ERROR: 515 – SQLSTATE: 23000 – Execute failed: Cannot insert the value NULL into column ‘RECNUM’, table ‘###’; column does not allow nulls. INSERT fails.’ or similar when trying to create new records in PCSchool (eg. add a new student).
EXAMPLE – In the example below the school has recently shifted SQL2008R2 server, they used the SQL Management Studio to import the previous database from SQL2005. Since the transfer PCSchool runs fine and data is available and can be edited.
However when they try to ADD a new record (not edit an existing record) they receive an error. Here the error has occurred when trying to add a new subject code after the transfer:
It appears that the system is failing to generate a record number (RECNUM).
CAUSE – When performing the conversion from SQL2005 to SQL2008 it was performed without creating the RECNUM columns as identity.
We can see the same error if converting the CUSTOMER table, from FDF16.1 Order Example, without its RECNUM columns as identity. See below how it is shown in SQLServer Management Studio:
Without creating recnum as identity
How it should look if done correctly
By default SQLServer will not allow you to change your tables to make the RECNUM columns as identity.
SOLUTION – You will need to perform the migration again and review the option to keep the identity columns.
For more information see http://www.packtpub.com/article/copying-database-sql-2008-copy-database-wizard