Microsoft

The EnterpriseOne Microsoft category will include all posts and pages pertaining to Microsoft products in an EnterpriseOne installation.

Better Performance On Work with Payroll Transaction Ledger (P050999) With An Index On F0618

We have been getting complaints about the Work with Payroll Transaction Ledger (P050999) grid loading performance. So, I decided to take a look. What I found was that upon entering the application it does an automatic find on the grid using the following SQL statement with the current G/L date in the WHERE clause.

SELECT
YTDGL,YTMCU,YTSBLT,YTPAYM,YTPB,YTSBL,YTUAMT,YTPCUN,YTPRTR,YTDWK,YTSHRT,YTAN8,YTSHD,YTPPRT,YTPALF,YTBHRT,YTOBJ,YTGPA,YTSUB,YTCMTH,YTAUBP,
YTPHRW,YTPDBA,YTCO,YTAID,YTCRCD,YTCRDC
FROM PRODDTA.F0618
WHERE (YTDGL <= 115334 )
ORDER BY YTDWK ASC

As you can see, this SQL statement is returning every record in the F0618 that has a G/L Date less than or equal to the system’s current G/L Date. I don’t know about your installation but the one that I was working on had 17+ million records. Why the heck would it need to return all that?

That screen was taking 8+ minutes to present information to the user.

With the help of my coworker Geordy and Technet, I came up with an index on the F0618 that didn’t make the SQL statement finish any quicker, but it does present the user the first page of data extremely fast (LESS THAN 3 SECONDS!).

CREATE INDEX [F0618_CUSTOM]
ON [JDE_PRODUCTION].[PRODDTA].[F0618] ([YTDWK] ASC)
INCLUDE ([YTDGL],[YTAN8], [YTPALF], [YTPRTR], [YTCO], [YTMCU], [YTOBJ], [YTSUB], [YTSBL], [YTSBLT], [YTPHRW], [YTPCUN], [YTPPRT], [YTBHRT],
[YTSHRT], [YTSHD], [YTPAYM], [YTGPA], [YTPDBA], [YTPB], [YTUAMT], [YTAID], [YTCMTH], [YTCRCD], [YTCRDC], [YTAUBP])

You can see from the index that all I did was create an index over the field that the SQL was sorting on (YTDWK or Date of Work) and included the rest of the fields that were in the query.

There must be other little tricks with indexes in EnterpriseOne. Have you found any?

Restore SQL Server Database On A Schedule

Keep Calm and AutomateOne of our companies has a training environment in addition to the standard PS,DV, PY & PD environments. They have been working very hard refining their Procure-To-Process (P2P). Now that they know how they would like to handle this process, they need to train the users. That’s where the P2P Training (PTR) environment comes in.

We setup the environment and have had everything running smoothly for the last few months. However, last week we received a request to backup the data in that environment and then restore that backup copy every morning for the next 2 weeks.

It’s a really simple process, but kind of a pain that I had to manually do the restore.

Well, if you’ve been reading this blog for very long, you know that I can’t stand doing things more than once. A trained monkey, I am not. If it can be automated, I do it and it usually makes sense to do so. This was definitely one of those times.

What I ended up doing was making 2 files:

  1. A bat file to call the sql file
    echo off
    echo. >> restore.log
    echo %date% %time% >> restore.log
    sqlcmd -E -d JDE_PTR -i h:\restore.sql >> restore.log
    echo %date% %time% >> restore.log
    
    
  2. The sql file
    USE master
    GO
    
    ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    
    RESTORE DATABASE TestDB FROM DISK=N'H:\TestDB.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
    GO
    
    ALTER DATABASE TestDB SET MULTI_USER
    GO
    

Then, I just added a scheduled task that fired off the bat file every morning.

Note: When creating the scheduled task, you will need to fill in a value for the “Start in” option. I’m not sure why but it only works if you fill that in. I was using Windows Server 2008 R2 so your mileage may vary.

Scheduled Task Start In Option

Recover Lost Disk Space

We currently share development clients and so there can be many people that use each one. Several times some of those clients have filled their C: drives and I have been unable to find where it went. I checked user desktops, downloads, etc. However, I was unable to find anything.

Then, I ran across a simple command that can empty the recycle bins of every user on the PC and help you recover your lost disk space:

rd /s /q "C:\$Recycle.Bin"

Hopefully, writing this little tip here will help me remember and maybe help someone else that has lost disk space and doesn’t know where it went.

How To List The Column Names Of A Table On SQL Server

We all know that most database tables in EnterpriseOne have a large number of columns. In fact, they usually push the limits of the RDBMS.

Well sometimes, you need a list of those columns for things like INSERT statements.

Below is how you can get the column names from a table in a SQL Server database:

USE JDE_PY910
SELECT [name] AS [Column Name]
FROM syscolumns
WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'F98950')

You can use either type V (views) or U (tables).

Check Windows Service Status And Restart If Necessary

Lately we have been noticing an issue where the Print Spooler service stops for some reason and users are unable to print UBE results through EnterpriseOne. So far, we have been unable to narrow down a cause for the Spooler service to stop.

So, like any good CNC Admin, I put a band-aid on it. I found a bat file script that I could use to check the status of the service and then restart the service it it wasn’t running. I then setup a scheduled task to run every 5 minutes.

 
ECHO OFF
for /F "tokens=3 delims=: " %%H in ('sc query "Spooler" ^| findstr " STATE"') do (
ECHO STATE = %%H 
if /I "%%H" NEQ "RUNNING" (
 REM Put your code you want to execute here
 REM For example, the following line
 net start "Spooler"
 ECHO Spooler has been restarted
 )
)

Do you have any band-aids that you use to mitigate issues?