Sorry - mail must have filtered it.. I've sent the file via email just now. Let me know that you got them.
Mike
Sorry - mail must have filtered it.. I've sent the file via email just now. Let me know that you got them.
Mike
Robert,
We had exactly the same problem I believe. Same script although I'd have to check the line number to be sure. Did you try upgrading your Test database, or the Training Database to determine that it is specifically your Production DB? That's what we did and realized things were a bit weird when it worked on the others and not Production. The open call we had with Epicor read like this:
We discovered several issues we would like to get answers for in making this upgrade function correctly from the Schema Change tool. The first thing we discovered was that the 905_701DF script had a cursor in it that was not iterating correctly on our Epicor905 DB. We had to modify that cursor to be a static cursor and give it an explicit node starting point for it to loop upon in order to get it to properly process in our Epicor905 DB. However, we discovered this morning that same cursor would run fine on our EpicorTest905 instance as a dynamic cursor as is in the original 905_701DF script. While we do not have a good explanation as to why it would not work in our Epicor905 DB we don't expect anyone from your team to be able to come up with one either.
We were able to get the Schema Change tool to complete in our Epicor905 DB after making this
change to that single block of cursor code. But in getting this to work correctly we came upon another anomaly that we need explained to us. After confirming that our custom script would run in SQL Server Management Studio, we moved the file into the \epicor\Epicor905\32\Clientserver\db\SQLUnicode
directory. When we first ran the Schema Change tool the script did not complete.
We then renamed the 905_701DF file in the \epicor\Epicor905\32\Clientserver\db\SQL directory and ran the Schema Change tool again and the Schema Change tool then stated it could find the
905_701DF script file.
So after troubleshooting these issues for a few days we have really came down to just a few questions regarding these source script locations.
We were under the impression that since we have a Unicode DB installation that the Schema Change tool was supposed to use the script files from the \SQUnicode directory. Is this not this case for this upgrade?
Regardless of a standard or a Unicode database being installed the scripts ran by the Schema Change tool are being retrieved from the \epicor\Epicor905\32\Clientserver\db\SQL directory?So you can see that we had a few problems (Unicode vs Standard and where the scripts were pulling from) as well as the Cursor problem. We are upgrading from 606 to 702 as well, SQL 2008R2, but otherwise a similar configuration to yours.
Epicor did NOT come up with any decent answers.
If you wish, I can send you (via email) our 905_701DF script so you can see the changes we made. Shoot me and email: mike <AT> andersontully <DOT> com
Mike
Any chance you have tried running this on another server with SQL 2008 or 2008R2 instead of 2012?
I’s test that to make sure the SQL isn’t a problem since that changed from one to the next.
Also, I think while it will run in 2012, make sure the DB is still set to be running as a 10.0(2008) DB not an 11.0(2012)
You also may need to install the Native Client 10.0 on the SQL which is where I am assuming you are running the schema change from.
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of robert.beaghan@...
Sent: Wednesday, April 02, 2014 9:12 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Upgrading from 9.05.607b to 9.05.702a
We are in the process of upgrading from 9.05.607b to 9.05.702a (SQL edition). We're installing on new servers and then migrating our production database. Our new servers are as follows:
Database Server:
Appserver:
We can't get the Schema Changes to run to completion. We've tried running the Schema Change against a restored backup of our production database as well as a copy of one of our test databases. We've also tried, at the suggestion of Epicor Support, to run the Schema Change against the Epicor 9.05.607 demo database downloaded from EpicWeb. Each time we run it, regardless of the database, it hangs at the same statement -- line 744 of the file '.\Epicor905\32\Clientserver\db\SQL\905-701DF.sql' and is included below:
DECLARE @TableSchema sysname,
@TableName sysname,
@ConstraintName sysname,
@ColumnName sysname,
@str1 varchar(max),
@str2 varchar(max)
DECLARE Constraint_cursor CURSOR FOR
SELECT SCHEMA_NAME(a.schema_id),
a.name,
b.name,
c.name
FROM sys.objects a,
sys.default_constraints b,
sys.columns c,
sys.types d
WHERE a.name = OBJECT_NAME(b.parent_object_id)
AND OBJECT_NAME(b.parent_object_id) = OBJECT_NAME(c.object_id)
AND COL_NAME(b.parent_object_id, b.parent_column_id) = c.name
AND b.definition like '%?%'
AND c.user_type_id = d.user_type_id
AND d.name in ('char','varchar','nvarchar','nchar')
OPEN Constraint_cursor
FETCH NEXT FROM Constraint_cursor
INTO @TableSchema,
@TableName,
@ConstraintName,
@ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @str1 = 'ALTER TABLE ' + @TableSchema + '.' + @TableName + ' DROP CONSTRAINT ' + @ConstraintName
--PRINT @str1
EXEC (@str1)
FETCH NEXT FROM Constraint_cursor
INTO @TableSchema,
@TableName,
@ConstraintName,
@ColumnName
END
SELECT @str2 = 'ALTER TABLE ' + @TableSchema + '.' + @TableName + ' ADD CONSTRAINT ' + @ConstraintName + ' DEFAULT ' + '''' + '''' + ' FOR ' + @ColumnName
--PRINT @str2
EXEC (@str2)
CLOSE Constraint_cursor
DEALLOCATE Constraint_cursor
GO
If I try to run the SELECT statement (below) for which the cursor is defined, it executes successfully returning 25 rows fairly quickly.
SELECT SCHEMA_NAME(a.schema_id),
a.name,
b.name,
c.name
FROM sys.objects a,
sys.default_constraints b,
sys.columns c,
sys.types d
WHERE a.name = OBJECT_NAME(b.parent_object_id)
AND OBJECT_NAME(b.parent_object_id) = OBJECT_NAME(c.object_id)
AND COL_NAME(b.parent_object_id, b.parent_column_id) = c.name
AND b.definition like '%?%'
AND c.user_type_id = d.user_type_id
AND d.name in ('char','varchar','nvarchar','nchar')
However, if I step through the entire statement at the top of this message using a debugger, the initial FETCH statement before the WHILE loop never returns.
We've gone through a few suggestions from Epicor Support, i.e. verifying permissions all around, configuring allowed memory on the SQL Server. I've also connected via both Shared Memory and TCP protocols. Epicor Support currently has a (more recent) copy of our database that they are going to try to run the Schema Change against.
Does anyone have any ideas as to what the issue may be and how to resolve it? Are there issues with cursors in SQL 2012?
Thanks for any help that can be offered.
Bob Beaghan
Software Engineer
One thing to note in regards to an upgrade from 60x to 70x, the Epicor905\ClientServer Folder goes away in 700 or 701, I forget which exactly, but when doing an upgrade, you should actually clear all the contents of the Epicor905 when running the install, this will get the correct file locations setup in config files, specifically the ones that are used by the admin tools and schema changes.
During an “upgrade†the files are not updated with the correct locations, however with a “clean install†the correct .ini files are created. There are some other locations too, I just don’t remember them right off the top of my head.
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of robert.beaghan@...
Sent: Friday, April 04, 2014 3:43 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Upgrading from 9.05.607b to 9.05.702a
theevlbastrd/Mike: I tried emailing you off-line to get a copy of your script. Since I'm not sure if it made it through or not, I'd thought I go ahead and request it here as well.
Thanks in advance,
Bob Beaghan