SQL Statements

The EnterpriseOne SQL Statements category will include all posts and pages pertaining to SQL statements that can be used in the administration and configuration of EnterpriseOne.

How To Move SQL Server Data and Transaction Log Files

I’ve been migrating data from an IBM AS/400 to a SQL Server a lot lately. Within the next year or so we should have completed migrating 3 more instances. Then, we will have all 7 instances on SQL Server.

In preparing for the final data migration of one of the instances, I had to move the data and transaction log files. I found a great article for this on MSSQLTips.

Below are the basic steps.

  1. Use the following commands to get the names and location of the data and transaction log files
  2. Set the database to single user mode and detach it

  3. Move the files to their new location
  4. Re-attach the database with the files at their new location

EnterpriseOne UBE Runtime Performance Audit

Yesterday, I saw a post by Shannon Moir called “Nice UBE / Batch Performance Summary“. It was great! It provided some the information needed to really evaluate the runtimes of your UBEs.

Shannon is a wiz at SQL on an Oracle database. Unfortunately for me, we don’t use Oracle databases. So, I converted Shannon’s SQL so that I can use it on SQL Server.

Thanks again, Shannon!

ESU Cleanup

Since our installations of EnterpriseOne were installed and the data upgraded we have applied and installed thousands of ESUs. On 4 of the 7, we neglected to apply the ESUs to the Pristine pathcode (PS900/PS910). This made researching code changes a bit of a challenge.

So, the other day, I decided to rectify that and apply the many, many ESUs to Pristine. Because there were some very old ESUs and not all of them were registered with Change Assistant, it was a very time consuming. After I had finished, I noticed that there were several ESUs that had only been applied to PS & PD and others that had only been applied to PS, DV & PY. Rather than look through every ESU and risk missing some, I connected to E1LOCAL and wrote a SQL statement to get every ESU that had been applied to the system that was not in every pathcode.

Please see “Use Oracle SQL Developer To Modify The JDE LOCAL Database” for connection instructions.