I have had to move everything over to a brand new server from my old server.
Did an IMPORT of the database(s) to new server.All tables imported perfectly and no lost data. Permissions have been properly assigned.
I had to redo all PK assignments in all the tables as that did not seem to carry over with the import.
PROBLEM: After a record loads and a data update has been entered I am now receiving the error: 'Cannot update identity column 'ID'
Column ID is the PK and is set to increment +1 on the creation of a new record.
I can read in existing records fine.
I am able to create a new record fine.
I cannot edit or update any existing record at all.
All XSD files have been updated and reloaded.
All connection strings have been changed and all appear to be working fine.
This all worked fine on my old server (running Server 2008 - SQL Server 2016) and still works today as I just tried it. I can create a new record and then do edits with no errors on the old server. Something is overlooked evidently on the new server (running Server 2016 - SQL Server 2016) that allows edits of current records. I have compared the old DB to the new and all settings "appear" to be set the same.
I am obviously overlooking something. Can anyone make a suggestion as to where to look or what to check please?
If you are migrating to a new server, it would be easier to backup the database on the old server and restore it to the new server. Importing the data, as you saw, require you to drop the constraints and recreate them. Is there something that prevents you from being able to do a backup/restore?
The error you are seeing - 'Cannot update identity column 'ID' - is the response from SQL Server when you are trying to update an identity column. SQL Server does not allow this. You can run this example to see this behavior.
CREATE TABLE #tmp(id INT IDENTITY(1,1), val INT);
INSERT INTO #tmp VALUES (11);
-- will generate an errro.
UPDATE #tmp SET id = 10;
So look for why/where you are updating an identity column.
Another things to consider is that if you are migrating from SQL 2008 to SQL 2016, the behavior of the identity column between restarts of the server would be different between the two versions. In SQL 2008 you can expect the values to be contiguous (with some exceptions e.g. rollbacks), but in SQL 2016, that is not guaranteed.
Unless I am reading this wrong - the OP is not upgrading from SQL Server 2008 to SQL Server 2016, rather just upgrading the OS from 2008 to 2016.
For an import process - you would have to SET IDENTITY_INSERT ON for the table to allow inserting of the existing identify values, which is what I think the OP is running into...
jeffw8713 - You are correct I am running the same SQL Server 2016 just the new server is running Windows Server 2016 and the old server is running Windows Server 2008R2..
I tried importing direct from the"old" database to the new database but it failed miserably every time whining about Illegal Anonymous User not allowed etc. as well as various error messages too numerous to mention (or remember). This I needed to do this fairly quick and after 2 days of frustration did an IMPORT instead of a COPY.
Not familiar with how to "backup" and then "Restore" it to the new server. The new server was put online because my old is running out of HDD space fast. I'm still learning SQL Admin and am a sole proprietor with limited funds to pay someone big $$ to properly teach me. I am still fresh enough in the new server that I am willing to try this if it is feasible. I assume that would require me to dump the new setup (databases) and import fresh?
I have checked all my code and there is NO call to update the 'ID' column (that was one of the 1st steps I took)... only other columns in the row. Once an identity is written there is no reason to "change" or update that number. I am only looking to load the record and simply update the data within that row, less the ID column.
As for using SET IDENTITY_INSERT ON I see this not feasible for two reasons:
Unless I totally misunderstand that command I understand it to be used to insert a new ID or change an identity number This is not what I am trying to do.
This would also be an issue as I have one connection string that will conflict when I load different forms (multiple forms in one program) since I usually only run one session and my understanding is Identity Insert can only be done on one table at a time per session. There are also occasions where I am writing to 2 separate tables at the same time. Again, there is NO need or desire to overwrite the ID column.
Excellent. Thank you tons. Maybe I should have asked here first before I attempted to do this all on my own!! You guys are excellent help and it is much appreciated. I will follow up on this tomorrow or soon after I attempt to see if the DB will back up and am able to restore it back to the new server. I did OK I think overall, but obviously I am nowhere near pro level. Am learning a TON of new tings I was not previously knowledgeable of. So this is a good learning tool for me.
Also, how long does it take to back-up a roughly 39mb Database (not very large by most corporate standards). Also will backing up the DB actually back up all the settings and what not (save the user logins)?
The error you are getting is because you are trying to import the data into a table that has an identity. The import process is bringing the data from the old system with the identity value - and you cannot import that columns value unless you tell SQL Server to allow inserting the identity columns value.
Your best option is to backup the existing database and restore it to the new server. This avoids any issues with importing data since all the data already exists in the backup file.
jeffw8713: Excellent and very well explained. I now fully understand why I'm getting the error 100% now. I am attempting to backup the DB up now and hope it is successful. It seems to be taking a very long time to do it though...
UPDATE: 17:45 Central:
Followed ALL instructions given as well as read the documentation provided. Everything is back on track, updating, writing, creating records as previously. Thank both of you TONS for the assist and the pointers. Well explained and excellent references to refer to.
I apologize I could only provide a heart for one of you... BOTH of you, equally, put me on the right path and should share equally the resolution!!
I was doing backup daily but was using Symantec Back-Up Exec. That, I found out the hard way, only allows restore to the same machine, not to a different on. That became apparent when I initially tried to pull that back up off the NAS.
I will now use MSSMS to do a daily backup to the NAS.
