Importing the Database From One Version of SQL to a Newer Version when Moving to a New Server

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

Advertisements

About studentmanagementsoftware

Director Student Management School Software PCSchool
This entry was posted in SQL, Technical / Sys Admin, Trouble Shooting / Error Messages and tagged , , , , . Bookmark the permalink.

One Response to Importing the Database From One Version of SQL to a Newer Version when Moving to a New Server

  1. Pingback: Transfer PCSchool to a New Server | PCSchool Student Management Tech Support

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s