We are upgrading from 905702a to 10.1.600. During the first attempt at the db migration upgrade process Task ID 1515 (Upgrade Database ICE3.1.600.0) had an error (first screenshot). Our first attempt at the migration process was on our current E9 LIVE server. So we restarted the migration process on our new E10 server at Task 1515 and noticed the first steps finished rather quickly, but when it got to task 1515 it stalled and ran for about 4hrs and then errored out with the same error we got on our E9 server. It looks like it’s complaining about one of the SQL scripts it’s trying to execute called ‘UpdateSecColumn.sql’ and the error is ‘ExecuteNonQuery requires an open and available connection. The connection’s current state is closed’ (2nd and 3rd Screenshots)
Has anyone ran into this error and have a solution? I have a feeling a call to Support is in our future.
Restarted server and started the process again and got the same error. I tried running the sql script it seems to be getting hung up on in SSMS and it does the same thing in SSMS, just sits there and does nothing and then eventually times out. We have a call into epicor support but they are really dragging their feet and not helping currently. Our upgrade in the meantime is hung up on this one error. anyone have any other help or insight?
Try running that SQL Script by hand and see if you can figure out where its getting hung up in terms of the script… maybe its hitting a record lock or something funky.
Do you have custom stored procedures, views etc in your DB?
Have you manually extended any fields? added new ones? etc?
Did you have multi-company enabled and working in Epicor 9? Could you post the complete log? I am curious if the last step in your screenshot is really where it hangs.
Can you run this part of the query and post the results?
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_SCHEMA IN ('Erp','Ice')
AND COLUMN_NAME LIKE '%SchemaName%'
AND TABLE_NAME not in ('Column','Table','Index','IndexColumn','ZSystem','IMZSystem')
ORDER BY TABLE_SCHEMA,TABLE_NAME, COLUMN_NAME
It’s getting hung up on the ‘UpdateSchemaNameCols.sql’ script I believe as this is the script I tried running in SSMS and it got to the exact same point as it does in the DB Migration Program. The second error I believe is just a consequence of the first error which is the script timing out.
The snippet of the query I put in the previous post is from UpdateSchemaNameCols and it builds the data for the cursor. This part should run and I am curious if you get 39 rows from it. Could you run just that part?
Can you send the output of Messages tab in SSMS when you run this? I am just curious which table it is failing on and then we could run that update statement for just that table. When the script hangs you could run a sp_who2 and see if there is a blocking process. You could also run SQL profiler to try and determine why it keeps hanging. This script should run in about 20 - 30 seconds.
Found out what the issue is. The Ice.ChgLog was the table it was getting hung up on. This is because it had over 32 million rows in it. So we purged any data in this table <2016 and shrunk it down to just 500,000 records, once I did that, Task 1515 completed successfully.
Now my next question is,does anyone know how we can purge the same records in our 905702a progress environment so that we don’t run into this same issue when we move our production database over?
In Upgrade Services - we found the same issue and have corrected that script internally - we had a customer with over 100M ChangeLog entries which is a good test to optimize our scripts but not the most useful business benefit. That is why we show the top tables in the ERP Analyser.
To Purge these records out of your ERP9 Database use the DB Purge and Summarize Menu Item, hope that helps.
I had a related but slightly different error when upgrading from 10.1.400.18 to 10.1.600.4. Same script, but an error relating to primary keys. @Edge did the person who wrote that script have a bad day?
Error Upgrade Process UpdateSchemaNameCols.sql Violation of PRIMARY KEY constraint ‘PK_ChgLog’. Cannot insert duplicate key in object ‘Ice.ChgLog’. The duplicate key value is (BVD, ACTRevision, Erp, ACTRevision, 10, 10~94, , 2015-11-13, 0, ).
UPDATE t1
SET [RelatedToSchemaName] = t2.TABLE_SCHEMA
FROM [Erp].[ARBalance] t1
INNER JOIN INFORMATION_SCHEMA.TABLES t2 ON t2.TABLE_NAME = t1.[RelatedToTable]
AND t2.TABLE_SCHEMA <> ‘dbo’
WHERE [RelatedToSchemaName] = ‘’
