Upgrading from 9.05.607b to 9.05.702a

Sorry - mail must have filtered it.. I've sent the file via email just now. Let me know that you got them.

Mike

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:
  • Physical server:  2 sockets x 6 cores = 12 CPUs, 32 GB RAM
  • Windows Server 2008 R2 Enterprise, 64-bit
  • SQL Server 2012, SP1, 64-bit, non-Unicode, upgraded in-place from fresh install of SQL Server 2008 R2, 64-bit
  • OpenEdge 10.2a, SP3, HF29, 32-bit

Appserver:
  • VMware virtual server:  2 sockets x 4 cores = 8 CPUs, 28 GB RAM
  • Windows Server 2008 R2 Standard
  • OpenEdge 10.2a, SP3, HF29, 64-bit
  • Epicor 9.05.702a

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

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:

  • Physical server:  2 sockets x 6 cores = 12 CPUs, 32 GB RAM
  • Windows Server 2008 R2 Enterprise, 64-bit
  • SQL Server 2012, SP1, 64-bit, non-Unicode, upgraded in-place from fresh install of SQL Server 2008 R2, 64-bit
  • OpenEdge 10.2a, SP3, HF29, 32-bit

 

Appserver:

  • VMware virtual server:  2 sockets x 4 cores = 8 CPUs, 28 GB RAM
  • Windows Server 2008 R2 Standard
  • OpenEdge 10.2a, SP3, HF29, 64-bit
  • Epicor 9.05.702a

 

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

 




We did try running the Schema Change against a test copy of our database as well as Epicor's demo database (9.05.607b).  All run attempts hung at the same place in the SQL code.

A few months ago, we successfully upgraded our development server.  However, that was a SQL Server 2008 instance that resided on the same machine as the appserver.

We tried both compatibility levels but I think we'll have to revisit that.  We'll also try using the older Native Client.

I'm still confused as to why the SELECT statement for which the cursor is defined runs fine on its own, but try it as part of the cursor definition and it doesn't work.

Thank you,
Bob Beaghan
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

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